# Using Standardized Syntax

Standardized Syntax Mode enables you to create formulas using a standardized language rather than a specific syntax for a specific database engine. Formulas created in Standardize Syntax Mode work with any database engine supported by the Managed Connect and Live Connect features.

For general information on using the Formula Builder interface, see Overview of Formula Builder.

## Create a Formula Using Standardized Syntax Mode

- Open the Formula Builder interface and select
**Standardized Syntax**.

- Enter a name in the
**Formula Name**box. - To add a function to the formula, type it directly in the editor. A suggestion box displays with columns and functions that fit your text. You may also use the following method:
- Under
**Insert Functions**, select the function that you want to insert. The description box displays helpful information about the function. - Click
**Add to Formula**. The formula displays in the Formula box.

- Under
- To add a column to the formula, type it directly in the editor. A suggestion box displays with columns and functions that fit your text. You may also use the following method:
- Insert the cursor in the desired location in the formula.
- Under
**Insert Columns**, click on the column that you want to insert. The column displays in the Formula box.

- Continue to add functions and columns until your formula is complete. Standardized Syntax Mode provides automatic error detection features. The editor continuously evaluates formulas as you type, and it immediately notifies you of syntax errors as well as incorrect usage of data types. Hover over a highlighted error to display additional details.

- Before you can use the formula, you should test it with sample data. If your formula uses correct syntax, you may display the
**Testing**tab to test your formula. For more information, see the following section, “Testing a Formula in Standardized Syntax.” - Click
**Save**to save the formula. In Standardized Syntax Mode, the**Save**button is displayed on the Formula tab.

## Testing a Formula in Standardized Syntax

- In Standardized Syntax mode, create your formula and click
**Testing**. If the formula contains any syntax errors or incorrect usage of data types, the Testing tab is not available.

The Input box lists all the columns in the formula in JSON format, and it assigns values from the dataset to those columns.

In the

**Input**box, review the values provided and modify as needed. To obtain a new set of values from the dataset, click**Randomize Data**.**Note**: You can evaluate more than one set of values at a time by editing the JSON and adding another set in the columns.Click

**Test Formula**. The output displays in the Output box. If an error occurs during formula evaluation, click**See Console Output**to display a detailed error response from the database engine.Click

**Save**. The formula is ready for use. For more information, see "Using Formulas" in Overview of Formula Builder.

## Syntax Reference

This section is a reference for proper syntax of formulas in Standardized Syntax mode.

### Field/Column Syntax

Insert columns by using the **Insert Columns** dropdown, by entering the column name, or by selecting them from the Column/Function suggestion box. Columns display as pills inside the formula.

### Function Syntax

Function names must be in uppercase and must be followed by a bracketed argument list. For example:

`ABS(25)`

### Comments Syntax

You can add single-line comments to the formula that should start with `//`

. For example:

`// This is a comment`

### Operator Syntax

The following table lists the available operators. Note that normal operator precedence applies.

Symbol | Operation |
---|---|

* | Multiplication of two numbers. |

/ | Division of two numbers. |

+ | Addition of two numbers. |

- | Subtraction of two numbers, or negation of a number. |

= | Test equality of two values. |

> | Test if the first number is greater than the second number. |

< | Test if the first number is less than the second number. |

>= | Test if the first number is greater than or equal to the second number. |

<= | Test if the first number is less than or equal to the second number. |

<> | Test inequality of two values. |

( ) | Evaluate the bracketed expression before applying operators to it. |

#### Supported values

Data Types supported:

**Numbers**: including decimals and negative numbers.**Strings**: any sequence of characters between two quotes.**Date time values**: strings with any of these formats: "MM/DD/YYYY" or "MM/DD/YYYY HH:MM:SS".**Boolean**: true or false values.

## Supported Functions

Standardized Syntax mode supports the following functions:

Function name | Type | Description |
---|---|---|

ABS | Numeric | Returns the absolute value of a number. |

AND | Logical | Evaluates different logical tests and returns true if all logical tests are true and false if at least one of the logical tests returns false. |

CONCATENATE | String/Text | Joins two or more strings into one string. |

DATEADD | Date/Time | Adds a specific date part value to a given date. Date part allows: "Y" years, "M" months, "D" days, “H” hours, “MI” minutes and “S” seconds. |

DATESUBTRACT | Date/Time | Subtracts a specific date part value to a given date. Date part allows: "Y" years, "M" months, "D" days, “H” hours, “MI” minutes and “S” seconds. |

DATEDIF | Date/Time | Calculates the difference between two dates based on the date part requested. Date part allows: "Y" the number of complete years in the period, "M" the number of complete months in the period, "D" the number of days in the period. |

DAY | Date/Time | Returns the day of a date. The day is given as an integer ranging from 1 to 31. |

DAYOFWEEK | Date/Time | Returns a number or string representative of the weekday for a given date. |

EVEN | Numeric | Returns a number rounded to the next greatest even integer. |

EXP | Numeric | Returns Euler’s number e raised to the power of a number. |

HOUR | Date/Time | Returns the hour of a datetime value as an integer ranging from 0 to 23. |

IF | Logical | Performs a logical test and returns a value based on the result. You need to set a value in case the test result is true and one if it is false. |

IFS | Logical | Allows different logical tests and returns the values set for the first condition equal to true. |

INCLUDE | String/Text | Returns true if a string contains a specified sequence of characters. This function is case sensitive. |

ISNULL | Logical | Replaces null values with the specified replacement value. |

LEFT | String/Text | Returns the first character or characters in a text string, based on the number of characters you specify. |

LENGTH | String/Text | Returns the length of a string. |

LOG | Numeric | Returns the logarithm of a number to the base you specify. |

LOWER | String/Text | Converts all the characters in a string to lowercase. |

MAX | Numeric | Returns the greater of two or more numeric or datetime values. |

MID | String/Text | Returns a specific number of characters from a text string. The resulting substring begins at the specified beginIndex and extends, t the right, the number of characters specified on numChars. |

MILLISECOND | Date/Time | Returns the millisecond of a datetime value as an integer ranging from 0 to 999. |

MIN | Numeric | Returns the smaller of two or more numeric or datetime values. |

MINUTE | Date/Time | Returns the minute of a datetime value. The minute is given as an integer ranging from 0 to 59. |

MONTH | Date/Time | Returns the month of a date. The month is given as an integer ranging from 1 (January) to 12 (December). |

ODD | Numeric | Returns a number rounded to the next greatest odd integer. |

OR | Logical | Evaluates different logical tests and returns false if all logical tests are false and true if at least one of the logical tests returns true. |

POWER | Numeric | Returns the value of the first argument raised to the power of the second argument. |

PROPER | String/Text | Capitalizes the first letter in a string and any other letters in the text placed next to any character other than a letter or number. Converts the rest of the letters to lowercase. |

RANDOM | Numeric | Returns a positive decimal number between 0 (inclusive) and 1 (exclusive). |

REPLACE | String/Text | Looks for a specific sequence of characters inside a string and replaces it with a different sequence of characters. This function is case sensitive. |

RIGHT | String/Text | Returns the last character or characters in a text string, based on the number of characters you specify. |

ROUND | Numeric | Rounds a number to a specified number of digits. |

ROUNDDOWN | Numeric | Rounds a number down to a specified number of digits. |

ROUNDUP | Numeric | Rounds a number up to a specified number of digits. |

SECOND | Date/Time | Returns the second of a datetime value. The second is given as an integer ranging from 0 to 59. |

SQRT | Numeric | Returns the positive square root of a value. |

TEXT | String/Text | Transforms a number into a string. |

TRIM | String/Text | Removes all leading and trailing spaces from text. |

UPPER | String/Text | Converts all the characters in a string to uppercase. |

YEAR | Date/Time | Returns the year of a date. The year is given as an integer ranging from 1900 to 9999. |