### The FormAssembly Formula Engine and Editor

For Professional, Premier, and Enterprise users in FormAssembly, you have the option to dynamically customize items like email templates, thank you messages, and display notifications using our FormAssembly formula engine. Additionally, you can use formulas within many of our connectors to to achieve more advanced customization.

**Note**: The formula engine deals with post-submission processing only. It does not modify the submitted data, and cannot be used inside the form itself. Additionally, formulas do not work with repeating sections. For Enterprise users, formula usage may be restricted on a role-by-role basis.

**For calculated fields within the form, see Form Calculations.**

### Video Tutorials

#### Introduction to Formulas & Aliases

Learn how to talk to FormAssembly behind the scenes with aliases and create formulas for smart form processing. We'll show you how to get started with aliases and formulas in email notifications, thank you messages, and more.

#### Using the Formula Editor

Get more in-depth with specific formulas and learn about a variety of examples for smart processing within your thank you messages, redirect links, connectors, and more.

### Formula Structure

- Field Aliases
The syntax of our formulas is very similar to Excel formulas, but there are some slight differences. In general, each formula consists of a field alias, a function, and a comparison operator, all of which are outlined below.

To make the process of formula building easier we have created a Formula Editor, which allows you to design your formulas in a more simple, dropdown environment, rather than having to write them from scratch.

### Field Aliases

When creating a formula, you can reference any field in your form using the**alias**syntax, which is an 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 an example of a formula you could use in an acknowledgement message:Dear %%tfa_salutation%% %%tfa_lastname%%, ...

Once evaluated, this formula would be translated to Dear Mr. Smith, or Dear Ms. John depending on how the user had filled out their form.

#### Alias List

Field aliases vary from form to form. To see the list of aliases for your form, open the**Notifications**tab of your form settings. At the bottom of this tab, click the**Show the list of available aliases for this form**link. You can also view the available aliases from within the Formula Editor (explained below).

The alias list is also accessible from other places in FormAssembly where formulas and aliases can be used, such under the display tab or within many of our connectors.

**Note**: The formula engine also supports the {!form_field} notation used in Salesforce.

{!form_field} and %%form_field%% are equivalent.

#### Alias List Test Form

Now that you are more familiar with field aliases and formulas, you might enjoy trying our Formula Editor & Alias List Test Form. This form will let you see how different field types function in regards to field aliases, and how you can use these aliases to build formulas. Give it a try!

### Common 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 commonly used functions:#### Logic

##### @IF

@IF(

Performs a logical test and returns either the second parameter (*condition*,*when_true*,*when_false*)*if true*) or the third parameter (*if false*).

Example:@IF(%%field_a%%>5,"GOOD","NOT ENOUGH")

##### @AND

@AND(

Returns TRUE if both conditions are true, FALSE otherwise (logical AND).*condition 1*,*condition 2*)

Example:@IF(@AND(%%field_a%%>5,%%field_b%%>10),"OK","NOT OK")

##### @OR

@OR(

Returns TRUE if at least one condition is true, FALSE if all conditions are false (logical OR).*condition 1*,*condition 2*)

Example:@IF(@OR(%%field_a%%>5,%%field_b%%>10),when_true,when_false)

##### @NOT

@NOT(

Returns TRUE if at the condition is false, FALSE otherwise (logical NOT).*condition*)

Example:@IF(@NOT(%%field_a%%>5),"OK","NOT OK")

#### Arithmetic

##### @COMPUTE

@COMPUTE(%%field_A%%+%%field_B%%)

Performs arithmetic calculations on form fields.##### @MAX

@MAX(

Returns the largest value from the numbers provided.*number1*,*number2*,...)##### @MIN

@MIN(

Returns the smallest value from the numbers provided.*number1*,*number2*,...)##### @ROUND

@ROUND(

Returns a number rounded to a specified number of decimal places.*number*,*decimal_places*)#### String Operations

##### @CONCATENATE

@CONCATENATE(

Joins 2 or more strings together.*string*,*string*,...)

Example:@CONCATENATE(%%tfa_firstname%%," ",%%tfa_lastname%%)

##### @LEFT

@LEFT(

Extracts a number of characters from a string, starting from the left.*text*,*number_of_characters*)

Example:@LEFT(%%field_a%%,5)

##### @RIGHT

@RIGHT(

Extracts a number of characters from a string, starting from the right.*text*,*number_of_characters*)

Example:@RIGHT(%%field_a%%,5)

##### @MID

@MID(

Extracts a number of characters starting at any position.*text*,*start_position*,*number_of_characters*)##### @FIND

@FIND(

Returns the location of a substring in a string (case-sensitive). Returns #VALUE! if string not found.*text1*,*text2*,*start_position*)

Example:@MID(%%tfa_email%%,@COMPUTE(@FIND("@",%%tfa_email%%)+1), @COMPUTE(@FIND(".",%%tfa_email%%)-@FIND("@",%%tfa_email%%)-1) (Returns the domain part of an email address).

##### @CONTAINS

@CONTAINS(

Returns true of text is inside of field. Otherwise, returns false.*text*,*field*)##### @ADDSLASHES

`@ADDSLASHES( %%tfa_XX%% )`

Returns the string with backslashes before single quote ('), double quote ("), backslash (\) and NULL characters.

Example: embedding a field alias in a piece of JavaScript code:<script> var name = "@ADDSLASHES(%%tfa_1%%)"; </script> </pre>

#### Date & Time

##### @LOCALNOW

@LOCALNOW()

Returns the current date

*and*time according to your language and time-zone settings.Output will include time and be in the following format: 09/05/2017 17:17

##### @LOCALTODAY

@LOCALTODAY()

Returns the current date according to your language and time-zone settings.

Output will be in the following format: 09/05/2017

##### @NOW

@NOW()

Returns the current date as a timestamp. This can be passed to other date functions to extract the day, month or year.

Output will be int he following format: 42984.682152778

##### @YEAR

@YEAR(

Returns a four-digit year given a valid date.*date_value*)

Example:@YEAR(@NOW())

##### @TODAY

@TODAY()

Returns the serial number for today's date. For example: 42984

Can be used in a lookup to return all records with today's date in the specific field you are conducting the lookup on (for example, "Last Modified Date").

**Note:**FormAssembly formulas honor the same date format parameters as PHP. More information can be found here.#### URL Manipulation

##### @URLENCODE

@URLENCODE(

Returns the RFC3986-encoded version of the string passed in.*query_string_param*)

Example:http://www.tfaforms.com/123?tfa_Name=@URLENCODE("Mike Johnson")

will output:http://www.tfaforms.com/123?tfa_Name=Mike%20Johnson

##### @URLDECODE

@URLDECODE(

Decodes RFC3986-encoded version of the string passed in.*query_string_param*)

Example:http://www.tfaforms.com/123?tfa_Name=@URLDECODE("Mike%20Johnson")

will output:http://www.tfaforms.com/123?tfa_Name=Mike Johnson

##### @SUBSTITUTE

@SUBSTITUTE(

Replaces all occurrences of match with match_replacement in base_string.*base_string*,*match*,*match_replacement*)

Example:@SUBSTITUTE("One Two Three Four","Four","Five")

will output:One Two Three Five

Error Testing##### @ISERR

@ISERR(

*insert formula for error testing*)Returns the value TRUE if the formula's value is any error value except #NA; otherwise, it returns FALSE.

All of these error testing formulas can be useful to add on the Thank You page of your form so that you can see the output value when the form is submitted.

##### @ISERROR

`@ISERROR(insert formula for error testing)`

Returns the value TRUE if the formula's value is any error value such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!; otherwise, it will return FALSE.

##### @ISNA

`@ISNA(insert formula for error testing)`

Returns the logical value TRUE if the value is #N/A, otherwise, it will return FALSE.

##### @NA

`@NA(insert formula for error testing)`

Returns the error value #N/A, which means "no value is available" if there is no value available for the formula being tested.

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

**Note:**FormAssembly's formula engine can only handle a maximum integer/number length of 19 digits. This means if you are trying to prefill or use a formula with a number that is longer than 19 digits, it will be cut off. There is no limit for letters, only for numbers.

### The Formula Editor

As mentioned earlier, we have created a Formula Editor to make the process of formula creation easier on your end. This editor can be found on the Display tab, the Notifications tab, and within many of our connectors. To use the Formula Editor, look for fields, aliases, or f-value dropdown menus. Additionally, the Formula Editor can be found by clicking thenext to the redirect link or from within the connectors (where formulas are applicable).*f*icon

### Using the Formula Editor

The FormAssembly Formula Editor will automatically be populated with the values and aliases from your form. To find a field alias from your form, simply click on the**fields**€ dropdown menu, and you will see your available options. If you hold your mouse over an option, a pop-up will appear telling you all of the available values for that particular field.

To insert one of our generic aliases (those that are available on all forms, regardless of what fields you have), click on the**Aliases**dropdown menu. Here again, if you hover over a particular option, a pop-up will appear, telling you more information.

Finally, to find functions to use in your form, you can click on the f-value dropdown menu for all of the formula building logic options. You can build incredibly complex formulas within our formula editor, and to get a better idea of what is possible, you can explore the Functions section below for a complete explanation of each specific function. You can also find specific examples of formulas at the bottom of this article.

Additional examples can be found on our Formulas for Common Usage page.

### Syntax Highlighter and Checker

In the Formula Editor, you will find that certain functions and aliases will be highlighted in different colors. Additionally, if you create a formula with an error in it, a "SyntaxError" will pop up at the bottom of the window. These two tools are designed to help you build formulas more efficiently.

#### The Syntax Highlighter

In order to help you visually distinguish the functions from the aliases in your formula, the syntax highlighter will automatically color-code the elements. Depending on the element, you can expect the following colors:

If your element is red, it might be due to an incorrectly capitalized function or a misspelled alias. You should check for SyntaxErrors at the bottom of the Formula Editor to help resolve any issues.

#### The Syntax Checker

At the bottom of the Formula Editor, you will find that the syntax checker pops up with various errors while you are creating your formulas. The syntax checker is designed to:

- check your alias names to make sure that they are correctly formatted,
- check your function names for correct spelling and capitalization,
- check the number of function arguments, and
- check for cases where your formula may result in a nonsensical return.

Use the suggestions of the syntax checker and the sample formulas below as a starting point to build your own formulas.

### Examples

#### How to Redirect Your Visitor Based on a Field Value

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. |
---|---|

## %%tfa_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. |

## "Page 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 "Page 1" was selected. |

#### Test Checkbox Values

Checkbox fields are handled a bit differently, as each choice must be checked separately.

Assuming the choice aliases are tfa_blue and tfa_red, you can use this formula:

@IF(%%tfa_blue%%="Blue", "You've selected blue", ""); @IF(%%tfa_red%%="Red", "You've selected red", "");

#### Does Not Contain a Value

If you would like to write a formula where a condition would return true if a certain value is **not** contained, it would look like this:

@IF(@NOT(@CONTAINS("text value")),do this, otherwise do this)

To get more complicated with this example, if you would like to search for multiple text values, you could add in the @OR or @AND operator, like this:

@IF(@NOT(@OR(@CONTAINS("text value"),@CONTAINS("other text value))),do this, otherwise do this)

#### Auto-Responder Email Template

Finally, 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 the value is Mr., the email will include Dear Sir. Otherwise, the email will include Dear Madam.

- 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(%%tfa_field%%="Page 1", "http://your.site.tld/page_1.html",@IF(%%tfa_field%%="Page 2", "http://your.site.tld/page_2.html", @IF(%%tfa_field%%="Page 3", "http://your.site.tld/page_3.html", "http://your.site.tld/default_page.html" )))