Formulas for Common Uses 


In this Article
Related Articles

Note: In the following formulas, change the tfa_ fields to match your form's own field aliases.  Additionally, field aliases from repeatable fields and sections cannot be used within FormAssembly formulas.

Skip If Formulas

Skip an object creation if a field is empty

@IF(%%tfa_XXX%%="","TRUE","FALSE")
 

Skip an object creation if either of two fields are empty

@IF(@OR(%%tfa_XXX%%="",%%tfa_YYY%%=""),"TRUE","FALSE")

Blank Values

If you would like to have a formula check to see if a value is blank, and send a value based on that, you can use a formula like this:

  @IF(%%tfa_1%%,%%tfa_1%%,%%tfa_2%%)

This formula says, "If there is a value in tfa_1, send that value. Otherwise, send the value in tfa_2


Checkboxes

Check a box in Salesforce only if both "I agree to be contacted" and "I would like daily emails" checkboxes are checked


Set the field's mapping selector to Formula.
@IF(@AND(%%tfa_XXX%%="I agree to be contacted",
%%tfa_YYY%%="I would like daily emails"),"1","0")
 

Skip an object creation if a checkbox labeled "Don't create Contact" is checked

@IF(%%tfa_XXX%%,"TRUE","FALSE")
 

Skip an object creation if a checkbox labeled "Create Contact" is unchecked

@IF(%%tfa_XXX%%="Create Contact","FALSE","TRUE")
 

Check a box in Salesforce if a certain option is selected from a multi-select drop-down list


Set the field's mapping selector to Formula.
@IF(@CONTAINS("Send me more information",%%tfa_XXX%%),"1","0")

Formatting

Title-case format a respondent's name in Salesforce


Set the field's mapping selector to Formula.
@PROPER(%%tfa_XXX%%)
 

Add line breaks to a set of values being populated into a Salesforce field


Set the field's mapping selector to Formula.
@CONCATENATE(%%tfa_XXX%%,@CHAR(10),%%tfa_YYY%%)
 

Add a copy of the response data to a Salesforce text field


Set the field's mapping selector to Formula.
%%RESPONSE_TEXT%%
 

Add a copy of the response data to a Salesforce rich text field


Set the field's mapping selector to Formula.
@CONCATENATE("<pre>",%%RESPONSE_TEXT%%,"</pre>")

Dates and Times

Set today's date and time on a Salesforce Date field


Set the field's mapping selector to Formula.
@YMDTODAY("")

Output will include time and be in the following format: 2017-09-05-04:00 

Set today's date and time on a Salesforce Date field (alternative method)


Set the field's mapping selector to Formula.
@LOCALNOW("")

Output will include time and be in the following format: 09/05/2017 17:17

Set today's date on a Salesforce Date field


Set the field's mapping selector to Formula.
@LOCALTODAY("")

Output will be in the following format: 09/05/2017

Set yesterday's date on a Salesforce DateTime field


Set the field's mapping selector to Formula.
@YMDNOW("-1 day")
  Output will include the time and be in the following format: 2017-09-05T17:16:30-04:00

Set the date on a Salesforce Date field to a year from today's date

@YMDTODAY("+1 year","Y-m-d")
Note: FormAssembly formulas honor the same date format parameters as PHP. More information can be found here.

Attachments

Give a user's file upload attachment a custom Salesforce file name while retaining the extension


Set the Attachment object's FileName field to Formula.
@CONCATENATE("Profile_Photo.",@MID(%%tfa_upload%%,@COMPUTE(@FIND(".",%%tfa_upload%%,1)+1),40))

Dynamic Email Notifications


You can set up your form to send conditional email notifications. It will send to a different email addresses based on an answer in your form. 
 

FAQ

How do I insert line breaks between parts of my concatenation formula?


Rich Text fields accept HTML5. Instead of concatenations with @CHAR(10), use <br> for line breaks.

Dear person, <br /> %%tfa_13%% <br /> <br /> Hello Hello <br /> Hello becomes:
Dear person, 

Great team work! 

Hello Hello
Hello

When using line breaks in a concatenate formula, you can use @CHAR(10).

@CONCATENATE("Dear ", %%tfa_1%%, ",", @CHAR(10), "Great team work!") becomes:
Dear Bob,
Great team work!
 

How do I keep leading zeros on a formula when sending to Salesforce?

 
@CONCATENATE(@REPT("0",5-@LEN(%%tfa_1%%)),%%tfa_1%%)
Note: FormAssembly's formula engine can only handle a maximum integer/number length of 19 digits. This means if you are trying to prefill or use a formula with a number that is longer than 19 digits, it will be cut off. There is no limit for letters, only for numbers.

How do I format a currency field to always add a decimal with two places?

If you are using email notifications or the autoresponder, you may see that currency amounts are automaticaly stripped of 0's if they are after a decimal. If you would like those 0's to remain, you can use the following formula:

@IF(@CONTAINS(".",%%tfa_1%%),@IF(@COMPUTE(@LEN(%%tfa_1%%)-@SEARCH(".",%%tfa_1%%))=2,%%tfa_1%%,@CONCATENATE(%%tfa_1%%,"0")),@CONCATENATE(%%tfa_1%%,".00"))
Terms of Service · Privacy Policy