FormAssembly.com : Formulas

The Formula Engine

Introduction

The formula engine introduces smart processing to FormAssembly. Configuration options like email templates or "Thank-You" messages can be scripted and interact with submitted data.

The syntax is very similar to Excel or Salesforce formulas. Here's an example that could be used in an auto-responder email template:

Dear @IF(%%tfa_salutation%%="Mr.","Sir","Madam")

@IF(condition, statement_if_true, statement_if_false) is a function. It will be interpreted by the engine and replaced with the result of the evaluation.
%%tfa_salutation%% is the alias for the 'Salutation' field in the web form (here, a drop down menu with just "Mr." and "Ms.").

In plain English, this tests the submitted value for the 'Salutation' field. If it is "Mr.", the email will include "Dear Sir", otherwise it will be "Dear Madam".

Note: The formula engine deals with post-submission processing only. It does not modify the submitted data, and can not be used inside the form itself. For calculated fields within the form, see Form Calculations.

Form Field Aliases

You can reference any field in your form using the alias syntax, which is the internal field name surrounded by two percent signs (e.g. %%field_name%%).

The alias will be replaced with the actual value submitted with the form when the formula is evaluated.

Here's another example for the Auto-Responder template:

Dear %%tfa_salutation%% %%tfa_lastname%%,
	      ...

This will be translated for instance to "Dear Mr. Smith," or "Dear Ms. John,".

The field names vary from form to form. Pleaser refer to the inline help to find the correct aliases for your form.

Note: The formula engine also supports the {!form_field} notation used in Salesforce. {!form_field} and %%form_field%% are equivalent.

Functions

The engine supports most functions found in MS Excel. The function must be spelled in uppercase and start with the '@' character.

Here's a list of the most useful functions:

Logic
@IF	`@IF(condition,when_true,when_false)` Performs a logical test and returns either the second parameter (if true) or the third parameter (if false). Example: `@IF(%%field_a%%>5,"GOOD","NOT ENOUGH")`
@AND	`@AND(condition 1,condition 2)` Returns TRUE if both conditions are true, FALSE otherwise (logical AND). Example: `@IF((@AND(%%field_a%%>5,%%field_b%%>10)),"OK","NOT OK")`
@OR	`@OR(condition 1,condition 2)` Returns TRUE if at least one condition is true, FALSE if all conditions are false (logical OR). Example: `@IF((@OR(%%field_a%%>5,%%field_b%%>10)),when_true,when_false)`
@NOT	`@NOT(condition)` Returns TRUE if at the condition is false, FALSE otherwise (logical NOT). Example: `@IF((@NOT(%%field_a%%>5)),"OK","NOT OK")`
Arithmetic
@COMPUTE	`@COMPUTE(%%field_A%%+%%field_B%%)` Performs arithmetic calculations on form fields.
@MAX	`@MAX(number1,number2,...)` Returns the largest value from the numbers provided.
@MAX	`@MIN(number1,number2,...)` Returns the smallest value from the numbers provided.
@ROUND	`@ROUND(number,decimal_places)` Returns a number rounded to a specified number of decimal places.
String Operations
@CONCATENATE	`@CONCATENATE(string,string,...)` Joins 2 or more strings together. Example: `@CONCATENATE(%%tfa_firstname%%," ",%%tfa_lastname%%)`
@LEFT	`@LEFT(text, number_of_characters )` Extract a number of characters from a string, starting from the left. Example:`@LEFT(%%field_a%%,5)`
@RIGHT	`@RIGHT(text, number_of_characters )` Extract a number of characters from a string, starting from the right. Example: `@RIGHT(%%field_a%%,5)`
@MID	`@MID( text, start_position, number_of_characters )` Extract a number of characters starting at any position.
@FIND	`@FIND( text1, text2,start_position)` Returns the location of a substring in a string (case-sensitive). Example: `@RIGHT(%%tfa_email%%, @FIND('@',%%tfa_email%%,1)) (Returns the domain part of an email address).`
Date & Time
@LOCALNOW	`@LOCALNOW()` Returns the current date and time according to your language and time-zone settings.
@LOCALTODAY	`@LOCALTODAY()` Returns the current date according to your language and time-zone settings.
@NOW	`@NOW()` Returns the current date as a timestamp. This can be passed to other date functions to extract the day, month or year.
@YEAR	`@YEAR(date_value)` Returns a four-digit year given a valid date. Example: `@YEAR(@NOW) (Returns 2012).`

Comparison Operators

A comparison operator can be used to compare two values in a @IF statement. The result is a logical value, either TRUE or FALSE.

Operator	Name	Example
=	Equal to	`@IF(%%field_a%%="A",when_true,when_false)`
>	Greater than	`@IF(%%field_a%%>5,when_true,when_false)`
<	Less than	`@IF(%%field_a%%<5,when_true,when_false)`
>=	Greater than or equal to	`@IF(%%field_a%%>=5,when_true,when_false)`
<=	Lower than or equal to	`@IF(%%field_a%%<=5,when_true,when_false)`
<>	Not equal	`@IF(%%field_a%%<>5,when_true,when_false)`

Examples

How to redirect your visitor based on a field value

Go to "My Forms" and click on the form you need to configure. This opens the form properties panel on the right-hand side.
Click on the "Notifications" tab.
In the "redirect to this page" field, instead of typing in the web address (URL) of the page, enter a formula. The syntax should look like this:

@IF(%%field%%="value_1", "http://your.site.tld/page_1.html", @IF(%%field%%="value_2", "http://your.site.tld/page_2.html", @IF(%%field%%="value_3", "http://your.site.tld/page_3.html", "http://your.site.tld/default_page.html" )))

Here's the explanation for each part:

@IF	The logical function used to test the value of a field. Note that the @IF statements are nested to perform the equivalent of a if/else/otherwise logical construct.
%%field%%	The alias of the field you need to test. The actual name will be different for your form. To find the correct alias, click on the link at the bottom of the Notifications tab.
value_1	The expected value. If you are testing a multiple-choice field, you must enter the choice value. To find the choice values for an alias, click on the link at the bottom of the Notifications tab.
'http://your.site.tld/page_1.html'	This is the page where the user will be redirected if 'value_1' was selected.

Restrictions

Formulas are available in the Professional Plan only.

Building your Forms

Input Validation

Conditional Questions

Form Calculations

Customization and Branding Options

Interactive Tutorials

Web Form Design Tips

Managing your Forms

Publishing your Form

Configuring Optional Features

Submission Confirmation and Notifications

Dynamic Configuration with Formulas

Sharing Forms and Data

Workflows

Managing your Data

How to Export your Data

Troubleshooting

Publishing Issues

Form Submission Issues

Export Issues

Managing your Account

PayPal Subscription

Connectors Documentation

Salesforce Connector Documentation Index

PayPal Connector Documentation Index

HTTP POST Connector Documentation Index

Google Spreadsheet Connector