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.
- 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
@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)
@IF(%%tfa_XXX%%="Send me more information","1","0")
Formatting
Title-case format a respondent's name in Salesforce
@PROPER(%%tfa_XXX%%)
Add line breaks to a set of values being populated into a Salesforce field
@CONCATENATE(%%tfa_XXX%%,@CHAR(10),%%tfa_YYY%%)
Add a copy of the response data to a Salesforce Text field
%%RESPONSE_TEXT%%
Add a copy of the response data to a Salesforce Rich Text field
@CONCATENATE("<pre>",%%RESPONSE_TEXT%%,"</pre>")Dates and Times
- 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
@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
@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)
@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
@LOCALTODAY()The output will be in the following format: 09/05/2017
Set yesterday's date on a Salesforce DateTime field
@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)
@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.
@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 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"))