How Formulas Work

When the Action of a Rule is set to Replace Values from Formula, you are presented with the ability to add Exports.

These exports will produce the values that you will use in your formula calculation. You are able to add exports for any value that you need that will be specific to the record. For instance, if you want to calculate the highest score among superscored ACT, SAT I, and SAT R, you would want the Superscored ACT, Superscored SAT I, and Superscored SAT R values as exports.

Select the Export button to add one or more exports for use in the formula.

The exports available for selection are any which are associated with or inherited by the Base selected when making the rule. For example, if the Prospects base was selected, the available exports would be the same when editing a Query on the Prospects base.

Users with the Slate Template Library permission will also see the Slate Template Library checkbox, making available exports directly from the Slate Template Library without adding a local copy of the export.

Use the Search to find the export(s) needed for the formula.

When exports are added, they appear below the Export button. The order does not matter, though it may be helpful to reorder them when conceptualizing a formula.

Edit the exports that have been added to include a name that has no spaces or special characters. For example, the export shown above as ACT Superscore - Verified would need to be changed to something like act to be used in a formula. Double click on each export to change the name.

In some cases, translation values may need to be used for one or more exports. For example, to use concordance tables to compare ACT, SAT I, and SAT R scores, the SAT I and SAT R scores may be translated to ACT using the following translation keys: SAT-ACT (or SAT2400-ACT) and SATR-ACT.

To use translation values, update the Export Value of the Export to use the appropriate Translation Export Value and select the key of the Translation Code being used.

This will take the SAT R score and use the concordance table to determine the score on the ACT scale.

Tip

The concordance tables to convert between SAT I (1600), SAT I (2400), ACT, and SAT R are all available to add from the Slate Template Library. See the High Score Calculations section below for more information.

Like the Query Builder functionality, additional configuration options include Format Type, Format Mask, and Null Value handling for the exports. 

Once the exports have been re-named and configured, the formula can be written.

Use the exports configured above by referencing them as variables. Use @ in front of the export name to pull the value into the formula. For example @act.

SQL knowledge is not required to create a formula, as many of the types of formulas that will be created are very straightforward.

For example, to multiply the ACT score by 5, the formula would be @act * 5.

To multiply the ACT score by 5, divide by 3 and then add 6, the formula would be (@act * 5)/3 + 6.

String values can be concatenated by using the + sign. For instance, to set the Application Header to the {{Round Name}} - {{Entry Term}}, the exports for the formula would be the Round Name and the Entry Term. The formula would look like this: @round + ' - ' + @term. See the String Values section below for more information.

To have a resulting value exactly match the output of a single configured export, the formula can be just the export variable: @satr.

Why is my formula concatenating when I'm trying to do a calculation?

If one of the values in a formula is a custom field, regardless of the Data Type of the field itself or the export configured in the rule, a try_convert statement must be used around the variable to convert the value to a real number and allow mathematical functions to occur with the value.

For example, when adding together Scholarship values to populate a Total Reward field, the formula will need to read:

try_convert(real,@scholarship1) + try_convert(real,@scholarship2) + ...

High Score Calculations - Example Field Rule

When performing high score calculations that require comparisons of different score types (e.g., SAT I, Redesigned SAT, and ACT), first add the appropriate Translation Codes (which contain the published concordance tables) from the Slate Template Library. These include:

* denotes that the concordance table existed previously within the dbo.convertScore function. When the key is added from the STL, this function will begin to use the Translation Table to perform the conversion instead.

Use the methods described above to add the exports needed for the calculation. Then add the formula to compute the maximum of these values:

(select max(x.[value]) from (values (@act), (@sat), (@satr)) x ([value]))

String Values - Example Application Header Rule

When combining string values, it is important to note that if any one of the values is null (blank), the end result of the concatenation will also be null. For any export that could potentially have missing values for any records, you can set a default value to use instead by setting a Null Value:

The formula can then look like this:

In this example, an application with the round: 2020 MBA and no entry term will be assigned the header: 2020 MBA - FALL, since the Null Value was configured to FALL. An application with the same round and the Spring entry term will be assigned the header: 2020 MBA - SPRING.

Alternatively, if there is not a value that you would want to replace it with - you simply want it to be treated as an empty string rather than a null, you can use this syntax in the formula:

isnull(@term, '') where '' are two single quotes with no spaces.

If you want an aspect of the formula to only be included if there is a value, you can include it within the isnull:

isnull(' - ' + @term, '')

In this example, an application with the round: 2020 MBA and no entry term will be assigned the header: 2020 MBA, since the entry term is null, no Null Value was configured, and the ' - ' + @term are being treated as an empty string when they are null. An application with the same round and the Spring entry term will be assigned the header: 2020 MBA - SPRING.