Formulas for Common Uses 

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. 

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

• Line Breaks in Concatenates
• Send Leading Zeros to Salesforce

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

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