Formulas for Common Uses 


In this Article
Related Articles

In the following formulas, change the tfa_XXX fields to match your form's field aliases. These are post-processing formula examples, and will not work with Form Calculations.

Note:
  • Formulas cannot be used in connector repeating sections. 
  • Field aliases from repeatable fields and sections cannot be used within FormAssembly formulas.
  • Javascript calculations cannot be used in connectors.

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 is empty

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

Skip an object creation if a field contains a particular value (in this case, the value is @company.com)

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

Are you working with checkboxes in your skip-if formula? Review this article for more details on using the checkbox choice alias in formulas.


Blank Values

Check to see if a value is not blank, if it is blank then send a different value rather than the blank value:

  @IF(%%tfa_XXX%%,%%tfa_XXX%%,%%tfa_YYY%%)

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


Checkboxes

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

Note: 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 (in this case, we are looking for "Send me more information" to be selected)

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

Formatting

Title-case format a respondent's name in Salesforce

Note: 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

Note: 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

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

Add a copy of the response data to a Salesforce Rich Text field

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

Dates and Times

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

Calculate age using a date entered, where the field alias is the date field in your form

Note: Set the connector's field mapping selector to Formula.
@DATEDIF(@DATEVALUE(%%tfa_XXX%%),@TODAY(),"Y")

The output will be the year difference between the date entered and today's date. 

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

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

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

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

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

Set today's date on a Salesforce Date field

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

The output will be in the following format:  09/05/2017

Set yesterday's date on a Salesforce DateTime field

Note: Set the field's mapping selector to Formula.
@YMDNOW("-1 day")

 The 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 (in this case, we are seeing if today's date is past the date 01/15/2019) 

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

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


Attachments

Give a user's file upload attachment a custom Salesforce File Name while retaining the extension (in this case, the custom file name is Profile_Photo)

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

Dynamic Email Notifications

To send an email to different email addresses based on an answer on your form, set up conditional email notifications.  


Additional Resources


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.

  • Example Code:
  • Dear person, <br /> %%tfa_13%% <br /> <br /> Hello Hello <br /> Hello 
  • Output Result:
  • Dear person, 
    
    Great team work! 
    
    Hello Hello
    Hello

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

  • Example Code:
  • @CONCATENATE("Dear ", %%tfa_1%%, ",", @CHAR(10), "Great team work!")  
  • Output Result:
  • Dear Bob,
    Great team work! 

How do I preserve leading zeros in a formula when sending them to Salesforce?

To preserve leading zeros the value must be sent as a Text Field. If sending as a Salesforce Number field type, the Salesforce API will strip out leading zeros.

Note: Replace the '5' in the formula below with the desired length for the number of characters you would like to send.
@CONCATENATE(@REPT(0,5-@LEN(%%tfa_1%%)),%%tfa_1%%)
Note: FormAssembly's formula engine can handle a maximum integer/number length of 19 digits. If you are trying to prefill or use a formula with a number that is longer than 19 digits, it will be cut off. 
This is only for numbers - not text.

How do I format a Currency Field to always add a decimal with two places?

If you are using email notifications or the auto-responder, you may see that currency amounts are automatically stripped of trailing zeros if they are after a decimal. If you would like those zeros to remain, use the following formula:

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