When you're importing a residuals report into the CRM, you can add calculated columns to the existing columns being imported.

A calculated column automatically generates new data by transforming the original report data using Excel-like functions.

Here is a list of the supported functions:

• IF
• SEARCH
• CODE
• LEN
• LEFT
• RIGHT
• MID
• CONCATENATE
• TRIM

Note: The IF and SEARCH functions include support for the logical AND (&) and OR (|) operators.

Note: Explanations of the functions together with basic usage examples are provided towards the end of this article.

The calculated columns enabled you to provide for many of your custom import needs.

For example, you may want to adjust your split percentages or the revenue information, or re-format an ID column so that the imported ID string complies with the CRM's ID format.

If the calculated columns feature were not available, then all such adjustments would have to be made in the CSV file before the import is initiated.

The calculated columns can be mapped to CRM fields in the same way as the normal CSV or Excel columns.

Adding a Calculated Column

To add a new calculated column on the residuals reports mapping page, click the Add button in the Calculations row:

In the popup which appears, enter the new Column Name (in this example it's 'Volume'):

Next, select the columns from your CSV or Excel file that will be used in your formula (i.e. Equation):

As you select the columns they will be added below the Column(s) dropdown:

After you've selected the columns that you need for your formula, enter the formula in the Equation field and click Add.

In the example below, we're evaluating the "Commission Code" column from the imported file.

If the "Commission Code" is set to '2', then the new "Volume" column will show the "Quantity Billed" value from the original file (otherwise it shows zero):

Tip: Clicking on any variable automatically inserts the variable’s code in the Equation field.

Once your calculated column is saved it will be displayed in the Calculations row. If you need to delete it, click on the X icon in the top right corner of the formula widget:

The calculated column now appears in the list of mappable columns together will all of the original report columns.

To map a calculated column to a CRM field simply drag and drop the column to the appropriate field:

Creating Formulas

The functions built into the CRM allow you to create formulas in the same way as you would create them in MS Excel, and you can also nest multiple functions.

Unlike MS Excel though, when you add a new formula in the CRM you should not add the "=" character at the beginning of the formula.

The calculated column formulas typically take an input value and transform it into a new output value. The input values are fed into the formula using variables. Each column in the CSV or Excel file you're importing represents a single variable.

To add a variable to your formula enclose it in curly braces like this: {column_name}

Note that the variable names are case-sensitive, and the function names must be capitalized.

Here is a table with the descriptions of the available functions and their syntax:

In this table, we provide a few basic usage examples using sample columns/variables: