For calculations, Ultorg incorporates a simple, spreadsheet-like formula language. The formula editor provides reference highlighting, auto-completion, and online documentation.

As a running example, we will use the perspective below, which lists customers, orders, and line items. There are three formulas: one to compute the billed amount for each line item, one to find the total for each order, and one to find the average order amount for each customer.

A formula in Ultorg always returns a primitive value, such as a number or text string. It is thus similar to a formula in Microsoft Excel, or a scalar column expression in SQL.

Entering a Formula

An Ultorg formula takes the form of a calculated field, which you can insert anywhere in the current perspective. To insert a formula, open the context menu on an adjacent field, and click Insert Formula Before or Insert Formula After.

In our example, we start by inserting a formula that will hold the amount per line item:

The new field is indicated with the formula icon () in the table header. In the field selector, a calculated field icon () is shown.

To set the formula, move the cursor to any cell in its table column, and press the Equals key (=). A list of available functions appears:

The list of available functions opens automatically when you type, or when Ctrl+Space is pressed. You can press Enter to auto-complete the selected function name.

If you start entering a function, a tooltip will show its required input parameters. You can click the tooltip to re-open the full documentation popup.

Formulas can be edited either from the in-cell formula editor (seen above) or the formula bar.

For this example, we can use regular arithmetic symbols (plus, minus, etc.) to calculate the net amount for each line item. First we click the “Unit Price” field to insert a reference to it:

Ultorg formulas are similar to spreadsheet formulas, but reference entire columns of values (fields) rather than specific ranges of cells.

Continuing the example, we type asterisk (*) for multiplication, and insert another reference, to “Qty”. We also multiply by (1 - [Discount]) to subtract any stated discount.

The color coding and bracketed field names show which fields are being referenced. When Enter is pressed to accept the formula, the overall result can be seen:

In Ultorg, you define the formula once, and its result is computed for every row in the subquery where the formula was inserted. This is in contrast with spreadsheets, where formulas must dragged down and copied for every cell. By defining formulas in terms of fields rather than cells, Ultorg perspectives remain refreshable even as the number of rows changes.

Optionally, we can move the cell cursor to the grey column label in the table header, and give the calculated field a name. Here, we type the name “Amount”:

You can change field names freely for display purposes. Existing formulas continue to work even if referenced fields are renamed. This is true even when names are not unique.

Entering a Reference

Formulas may contain references to any other fields in the same perspective. During formula editing, there are several ways to enter a reference:

• You can click the field to be referenced, anywhere in its table column (as mentioned).
• You can press Up or Down to move the cursor out of the open formula editor, and then use the arrow keys to move to the desired field.
• You can type the name of the field, omitting spaces and punctuation, e.g. “unitprice”. Ultorg will tidy up the formula, with full field names, after you finish editing it.

Aggregate Functions

Continuing the example, we would like to find the total amount for each order. To do this, we add a second formula, at the Orders level, and reference the existing Amount formula from within the sum function:

The line items from each order add up to that order's total.

This formula could equivalently be added with the shortcut Sum action in the context menu.

The sum function is a so-called aggregate function. Aggregate functions compute their result from multiple input rows, through references to fields in a deeper, or adjacent, subquery.

Other aggregate functions include count, max, and avg (average). The aggregate functions appear at the beginning of the function list here and in the formula editing dropdown.

We can modify the formula to include freight costs as well. The freight costs are stated per order, rather than per line item, so we add them outside the parentheses of the sum function.

Using Aggregate Results

Combinations of aggregates are permitted; you can create sums of sums, averages of sums, etc. Sorting and filtering also works normally on calculated fields, including those that contain aggregate functions.

For example, we can add a third formula to find the average order size for each customer, and then sort customers descending by average order size:

Hiding and Showing Input Rows

After adding an aggregate function, you will see not only its result, but also the input rows that contributed to it. To see only the results, you can hide the subquery that retrieves input rows.

For example, we can hide the “Orders” subquery and just show the list of customers with their average order amounts. To do this, right-click Orders and click Hide.

We can also enable horizontal bars () on the average order amounts to better visualize the amounts. The result is shown below.

The hidden subquery can be made visible again from the field selector, or by invoking Unhide Referenced on the calculated field. The hidden state icon () is shown in the table header for any formula that is referencing a currently-hidden field.

Filtering Input Rows

Seeing the inputs to an aggregate function is useful during query construction, as it allows you to visually confirm the correctness of your formulas, and make further changes as necessary. In manually coded SQL, by contrast, you would see the final output only.

If there are filters on the inputs to an aggregate function, then only the selected rows will contribute to its result.

In our example, we could use the field selector to show the Category field for each product, and then filter to include only Beverages:

The order totals change as a result, as they now only include line items for products in the Beverages category. In turn, we also get updated averages at the Customers level, and a different company is now ranked first.

Calculations on Input Rows

Our example showed the calculation of amounts per line item (“Amount”) and the sum of line items plus freight (“Total”) in two separate formulas. Equivalently, we could have included both calculations directly in the “Total” formula at the Orders level:

If you move the cell cursor while editing the formula, you can see which input rows will contribute to the total in each case:

A formulas can reference fields from intervening subqueries if desired. For example, we can do a conditional sum at the Customers level to calculate total discounts from orders in 2025:

The deepest referenced subquery, i.e. “Line Items”, determines the number of input rows. This technicality is relevant to functions such as sum, which are sensitive to duplicated values. References into single-row subqueries do not affect the number of input rows.

Group-By Fields

In Ultorg, the grouping of rows is determined by which subquery the aggregate formula is inserted in. In our example, the sum formula in the “Orders” subquery yields totals per order.

To group by a subset of fields, rather than by entire table rows, use the Custom Group action to create a new, custom subquery level. Then add the aggregate function there. For grand totals, you can invoke Insert Formula Before on the root subquery, or use the shortcut Sum action.

For subqueries associated with a data table, the table's primary key columns are automatically used for grouping, even when they are not selected for display.

At the SQL level, Ultorg computes the appropriate GROUP BY clause in each case.

Language Details

Ultorg's formula language is similar to that of Microsoft Excel, except that references are defined in terms of named fields rather of ranges of cells. The structure is as follows:

A formula may contain and combine any of the following parts:

• Functions: These take the form functionName(input1, input2, input3, ...), where each input can be any sub-formula.

  Ultorg has a standardized list of supported functions which are designed to work consistently across different data sources. For relational databases, each function is translated to the specific SQL dialect required by the data source.

• Operators: These are the arithmetic symbols + - * / (plus, minus, multiply, divide) and %% ** (modulo, power), as well as symbols for comparison (= <> < > <= >=, is, is not) and logic (and/or/not). They are documented alongside other functions.

  Operators have precedence according to the usual mathematical rules. You can use parentheses to override the order, e.g. =2+4*2 will yield 10 but =(2+4)*2 will yield 12.

• References: The name of a field in brackets, e.g. [Author Name].

  A reference is inserted automatically when you click a field during formula editing. If the perspective has multiple fields with the same name, or if the referenced field has no name, a disambiguating notation will be used, e.g. [Readings\Title] or [Courses@3].

• Constants: Numbers, text, and other constant values that are used with functions and operators. The format for the most common data types is shown below.

  Data Type	Example Constant
  Text	"Amalgamated Holdings Corporation"
  Number	123.45
  Time	{17:45}
  Date	{1995-12-31}
  Local Timestamp	{2023-12-31 17:45}
  Global Timestamp	{2023-12-31 17:45Z}
  Logical	true or false
  Null (special value)	null

  Date and time constants use the ISO 8601 format in curly braces. See the Data Types reference page for additional data types and the format of constants for each.

  Quoted text: String constants may contain backslash-escaped characters in the JSON style. For example, "Line 1\nLine 2" has a newline character in the middle.

Logical Conditions

Functions such as contains and startsWith, or comparisons such as [Status] = "InStock", return either true or false. These are known as logical conditions. Logical conditions can be used with functions such as if and countif, or returned directly from a formula.

You can combine logical conditions with the and, or, and not operators. For example:

[Price] < 50 and not contains([Product], "asbestos")

Formulas like these can be used to express arbitrary filter conditions. Though for the example above, this would not be needed. You could just filter directly on Price and Product instead.

Null Values

The special null value is used by many data sources to represent non-applicable or missing data. Null handling in Ultorg is similar to that in SQL. Ultorg functions also return null for undefined results, e.g. for division by zero.

The ifnull and zn functions can be used to replace a null value with a default value. In a logical condition, you can test for null values with is null. For example:

[Price] is null or [Price] > 500

See the documentation of individual functions and operators for details.

Date and Time Functions

Date, time, and timestamp values are represented with dedicated data types. Durations of time are represented as numbers, like in Excel.

You can perform calculations on date and time values using the plus and minus symbols. Durations returned by the minus symbol (diffDayFract) are displayed in a day/hour/minute format by default, configurable via the Show as Duration option in the Format popup.

The plus or minus symbols serve as shorthands for various date and time functions. The specific operation used depends on the data types of input values, as follows:

Usage	Description
date + time	Create a timestamp from a date and a time.
date +/- days	Add a number of whole calendar days to a date.
timestamp +/- days	Add elapsed time to a timestamp, in fractions of a day.
endDate - startDate	Get the number of whole calendar days between two dates.
endTime - startTime	Get elapsed time between times or timestamps, in fractions of a day.

For other date and time functions, see the Formula Functions reference.

Valid Field References

A formula can reference any field in the same perspective, including other formulas. Below are some additional examples of valid formulas.

Counting Rows

The count function may be used with a reference to a subquery as its input. This counts the number of rows in each group from said subquery:

Alternatively, the count function may be used with a primitive reference or sub-formula as its input, in which case the number of non-null values is counted. See the function documentation.

In all other cases, formulas reference primitive fields only, rather than entire subqueries.

Parallel Aggregates

A single subquery may contain multiple aggregate functions. These may reference either the same subquery or different descendant subqueries. For example, the formula below divides the number of meetings by the number of distinct instructors:

Outward References

A formula may reference fields in any of its ancestor subqueries. For example, we can compute a percentage by dividing a value by the sum of all values as computed in an outer formula:

In SQL, there is an analogous concept known as a “correlated” subquery. The SQL code that is generated by Ultorg is always in decorrelated form.

Inward References

Our aggregate function examples all involved an inward reference, i.e. a reference to a field in a deeper subquery than the formula itself.

Outside of aggregate functions, an inward reference is permitted from a Custom Group, or in cases where the referenced field is guaranteed to yield at most one value per row. When there are zero rows in the referenced subquery, the reference yields a null value.

For example, the formula below takes a customer name from the “Billing Accounts” table if available, or otherwise uses the first name and last name from the “Users” table:

The inward reference to “Name on Credit Card” is permitted because join constraints ensure that there is at most one billing account per user.

If the reference is not guaranteed to yield at most a single value, an error message is shown.

You can still extract unique values where they do occur, using the single aggregate function:

Lateral References

References may traverse subqueries in both the outward and inward direction.

Error Handling

During interactive query building, your perspective might occasionally contain invalid formulas, broken references, or other errors. Ultorg catches errors before they reach the SQL layer, with a user-friendly warning shown for just the specific field(s) involved. The rest of the query runs normally, so you can continue making changes while data remains visible.

Formula Editing Errors

If you enter a formula in the wrong format, Ultorg will show an error and offer a correction.

Some common errors, such as missing close parentheses, are corrected without a prompt. The word #huh! indicates a part of the formula that could not be parsed.

For operator symbols, Ultorg accepts alternative variants from common programming languages. For example, && is accepted in place of the keyword and. Many synonymous function names are also accepted, e.g. average instead of avg, or stddev instead of stdev. In each case, the preferred form is automatically substituted after you finish editing the formula.

Data Type Errors

The functions and operators available in Ultorg's formula language are each designed to work with specific combinations of data types. For example, the left function expects its first input to be of the Text data type, while the second input should be a Whole Number. If a function is used with the wrong data types, a warning message is shown on the formula's field:

In manually coded SQL, these kinds of errors would cause the entire query to fail. In a spreadsheet, a formula might simply return the wrong result, with errors going unnoticed.

See this reference page for a list of data types officially recognized in Ultorg. See the function reference for the data types expected by each function.

Other Formula Errors

Various other formula-related errors can occur, such as broken or circular references. These are handled gracefully and displayed in the same way as data type errors.

Query Execution Errors

Some errors can occur at the SQL execution stage, e.g. when a network connection is lost, or when accessing a table with insufficient permissions. In such cases, the error message from the data source will be shown, with the data from the most recent successful query still visible.

The Undo () and Redo () actions are useful during troubleshooting. For network errors, use Refresh () to reconnect.