Using Formulas in Queries

Query Formula Export Parts can be used to perform mathematical calculations, combine multiple fields into one export column, write if/then statements, and otherwise manipulate data in the Query Builder. 

Query formulas are a powerful tool for SIS exports and data integration in general, making it possible to export your data in exactly the format and layout required by external systems.

Adding a Formula
  1. Navigate to the Edit Query view of the query, and click Formula on the query part palette.
  2. In the Edit Part window:
    • Give the formula part a Name. Like other export parts in a query, this becomes the column header in the data export.
    • Add Export: For each variable that will be used in the formula, the corresponding export must be added in this section. For example, if the formula calculates a value based on a person's GPA and highest overall max ACT score, you'll want to add an export for GPA, and one for the appropriate ACT score. 

      Configurable exports cannot be used within Formula export parts.
  3. Edit each newly added export:
    • Change the name of the export to ensure that it does not contain any spaces or special characters (except underscore). Names must be unique within the formula.
    • Verify that the format type for the export is compatible for the type of formula you are creating (Int for integers, Real for real numbers, String for words/character strings, etc.). Performing a mathematical operation on a combination of numbers and strings, as an example, may yield unexpected results.  

      The export value, translation code, or a custom null value can also be used, as the formula requires.
  4. Formula: Enter the formula, and add @ in front of the export name to reference it in the formula. SQL knowledge is not required to create a formula, as many types of formulas are very straightforward.
    •  Calculations:
      • To multiply the ACT score by 5, the corresponding formula would be: @act * 5
      • If you want 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
      • To export a combination of a record's round and entry term as Round Name - Entry Term, add an export that contains the round name and an export that contains the entry term. The formula would look like this @round + ' - ' + @term.
Formula Examples
Example 1 - Combine Multiple Fields into a Single Export Part

Entry Term and Student Type are stored in two different fields in Slate, but your SIS requires them to be in the same field or column, pipe delimited.

Formula: @entry_term + ' | ' + @student_type

Results:18 Fall | Freshmen

Example 2 - Coalesce Multiple Fields

Your SIS can only store one phone number per person. If the person record has a mobile phone number, this should be exported; if not, daytime phone should be exported, and if the record has neither mobile nor daytime phone, evening phone should be exported.

For this, using a coalesce function will return the first non-null value from the list of exports in parentheses.

Formula: coalesce(@mobile, @daytime, @evening)

Example 3 - Calculate: 4-Year Scholarship Value

You need to export not only an applicant's awarded scholarship amount, but also the four-year value of the scholarship. Rather than store the total value in its own field, a formula export can be used to calculate the total value of the scholarship over a given number of years.

Formula: @scholarship_amount*4

Example 4 - Calculation: Seats Available at Event

You'd like to know how many spaces remain for an event. This query is built using the Forms/Events query base, which returns one row per event (with the export column returning the number of seats still available per event).

 Formula: @limit - @registrants

Example 5 - Case Statement

An export to the SIS needs to send out a record's application status, but if the record has already been assigned a decision, their most recent confirmed decision should be sent out instead.

The case statement in the example below will return Decision Confirmed Name (i.e. the most recent confirmed decision) if the application has a status of "Decided." Otherwise, it will return Application Status.

 Formula: (case when (@app_status = 'Decided') then @decision_name else @app_status end)

Was this article helpful?
21 out of 41 found this helpful



Please sign in to leave a comment.