Formulas for Common Uses 


In this Article
Related Articles

PLEASE NOTE:

1.  Formulas cannot be used in connector repeating sections.  Javascript calculations cannot be used in connectors.
2.  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.
3. These are post-processing formula examples, and will not work with Form Calculations

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")

Skip an object creation if a field contains a particular value

@IF(@CONTAINS("@company.com",%%tfa_ZZZ%%),"TRUE","FALSE")

Are you working with checkboxes in your skip-if formula? Be sure to review this doc for more details on using the checkbox choice alias in your formula for the best results.


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

Calculate an Age Using a Date Entered


Set the connector's field mapping selector to Formula.

@DATEDIF(@DATEVALUE(%%tfa_XXX%%),@TODAY(),"Y")

Output will be the year difference between the date entered and today's date.  Replace tfa_XXX with the alias for the date field in your form.

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")

Compare today's date with a specific date 

@IF(@DATEVALUE(@LOCALTODAY())>@DATEVALUE("01/15/2019"),"TRUE","FALSE")

The output will be "TRUE" if the date listed (01/15/2019) has passed. 

Note: FormAssembly formulas honor the same date format parameters as PHP. More information can be found here.
Note: When pre-filling a Time field type from Salesforce, times will often pre-fill in the military time format (08:00:00.000Z). As a workaround, we suggest using the Salesforce field alias (%%SFA_Date_Field__c%%) in a formula in the pre-fill connector to trim the first 5 characters. For example: @LEFT(%%SFA_Date_Field__c%%,5) will output 08:00.

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_XX%%,@COMPUTE(@FIND(".",%%tfa_XX%%,1)+1),40))

Dynamic Email Notifications


You can set up your form to send conditional email notifications. It will send to 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?

You will need to replace the 5 in the formula below with the desired length for the number of characters you would like to send. Also, this does not appear to work when sending to a Number field type in Salesforce as the API strips the 0s. It needs to be sent to a text field. 

@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