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