Cumulus field values can be determined by formulas that are authored as part of a field’s properties. A formula can be as simple as the concatenation of two field values into a single string, or as complex as if-then decision making.
Formulas can be assigned to record and category fields. Values for existing records and categories are calculated (or recalculated) when the record or category’s metadata is saved after an edit. Formulas are executed during cataloging operations for newly created asset records and categories.
Field formulas offer many significant benefits:
Metadata display versatility
Used in conjunction with “display only” metadata fields you add to your catalogs, field formulas enable you to provide users with data displays formatted in more meaningful ways.
Example: A video’s Duration field might contain the value “25.566.” Even when displayed using the field’s label, the user sees “Duration: 25.566,” which could be more clear. A simple field formula can round that value and display it with adjacent strings that tell a more complete story: “Length: 26 seconds.”
Another usage example would be to combine the values of the fields Horizontal Pixels, Vertical Pixels and the appropriate resolution field to provide a single line of commonly used information:
“2400 x 1200 @ 300 DPI”
This single formula, applied to the properties of a string field you add to your catalog, saves you a full 5 lines of vertical space in your thumbnail display. The added string field ensures that each field’s original value remains intact.
Calculations
Field values can be calculated to provide useful, searchable metadata values that would not otherwise exist in your catalogs without manual data entry. Example: say you have a field for Cost Per Use. Another field, Times Used, tracks how many times you’ve used the particular asset. A third field, Total Cost of Use, uses a simple multiplication formula to offer a sum of the total costs associated with the asset.
Workflow monitoring and guidance
Field formulas can combine the values of several status fields into one resulting field that can be used for Live Filtering, or as a trigger for Scheduler actions, or by the person in charge to decide on further actions.
Metadata Write-back Flexibility
The flexibility of the Cumulus architecture makes it possible (and easy) to use metadata taxonomies and field types that cannot be written back to assets because of the “string only” limitation of most metadata standards. You can circumvent this limitation in many cases by using formulas in conjunction with Cumulus’s Field Linking. By adding special “metadata write back” fields to your catalogs, you can add formulas that concatenate the contents of several fields (or fields with data types incompatible with write-back operations, such as string lists) and “field link” them for write-back to your assets.
Generate printer-friendly reports
Setting up formula-filled fields that you use on print templates offers you a great way to output metadata in formats suitable for review by others. Formulas enable you to combine metadata fields that would otherwise be “report hostile,” such as string lists, Booleans, and labels and ratings, into strings that fit nicely onto printed pages. Formulas also enable you to manipulate metadata that you intend to export from Cumulus for use in other systems.
Syntax Overview
A field formula is an expression that returns a single value, which can be of type number, real, boolean, string, date, date only or time only. The constant value null can also be returned to represent an empty field value.
When applying operators on two values of different types, one or both values are converted into a compatible type. Example: (“Cumulus ” + 7.5) results in the string “Cumulus 7.5” because 7.5 is converted to a string before the + operator is applied.
If an error occurs, the field’s value is set according to settings you determine while authoring the formula. (For details see “Multiplying the Values of Two Catalog Fields”.) If no error occurs, the resulting value is automatically converted to match the field’s type.
The arithmetic operators available are a subset of those available in programming languages such as C, Java, or JavaScript.
Function Reference
The Cumulus formula editor includes an in-line help reference that describes each function and how it can be used (see “In-line Help” ).
Additionally, a field formula syntax overview is provided as HTML page. This document assembles the most recent information on all functions, including the formal syntax. For a detailed description see Field Formula Syntax.
Formula Examples
The following formula examples rely on specific catalog fields. If using these formulas in your catalogs, make sure you substitute the same field names with fields from your own catalogs.
Adding Text Labels, Formatting Output and Rounding Numeric Values
This formula takes the real value in the catalog’s Duration field, rounds it to a single decimal place, and surrounds the resulting value with text strings.
The "%.1f" portion of this formula will be familiar to those who have worked with the printf function available in many programming languages. Cumulus supports the following printf formatting options:
Data Type
Supported
Example
String
s
%-20.20s
Integer
d, I, o, u, x, X
%d
Real
e, E, f, g, G
%5.2f
In addition, you may use the new line escape character "\n" within a string to force a line break where needed:
This formula multiples the values of the Horizontal Pixel and Vertical Pixel fields, divides that value by 1,048,576 in order to get the number of megapixels in an image, and appends that value with a text string that describes the result.
This formula compares values found in the Horizontal Pixel and Vertical Pixel fields. If the Horizontal Pixel value is larger, the image is determined to be “Landscape.” If smaller, it’s “Portrait.” If the two values are the same, the image is “Square.”
The Field Formula Editor
The field formula editor can be found on the Field Values tab of the Field Properties windows for record fields as well as for category fields. To open the field formula editor, select Use Formula based field values from the Field Value Mode drop-down list.
NOTE: Fields using formula based field values cannot be edited by users!
The Formula area is where you define your formulas.
Take advantage of the drop-down lists provided by the Fields and Functions buttons to save time and help you avoid syntax errors due to simple typos.
By default, the value of a formula field is newly calculated every time the record has changed, i.e. every time any field value has been changed. This not only can be very time consuming, but is completely unnecessary in may cases. Therefore, you can restrict the re-calculation of a formula field in such a way that it is only performed if specific fields or sub-table entries of a field have changed.
Use the Add button to select the field(s) on which you want the update of the formula field to depend. The Auto Detect button supports you by searching for all fields contained in the formula itself and adding them to the list.
NOTE: For technical reasons, the auto detect function can not be perfectly reliable. Make sure to check the results of the auto detection and correct them manually, if necessary.
Such dependencies of formula fields not only can speed up your system. Moreover, It makes it easy to determine the order in which fields are calculated. This is helpful e.g. if work flows are to be defined within Cumulus
Finally, choose your preferred option for error handling. An error occurs when for whatever reason your formula cannot be executed. Errors can include syntax issues in the formula itself, or problems evaluating field data used in the formula.
When you click OK the Field Properties window is closed, and your formula is saved and activated.
In-line Help
An in-line function and syntax reference is built into the formula editor. This feature provides a handy reference that will save you time and help you avoid syntax errors.
Three types of in-line help are available:
• Syntax reference – See an example of the syntax used with a function.
• Usage reference – See information about the purpose of a function, the value it returns, and a usage example.
• Autocompletion – See a menu list of functions whose names match characters you’ve typed.
To activate in-line help:
1. Click to place your cursor within the function’s name in the formula editing field.
2. Type CTRL p (syntax reference) or CTRL q (usage reference). The usage reference help window that appears may be resized as needed.
Both windows disappear when another key is pressed or the mouse is clicked in the Formula editing field itself. (Hint: Escape, Shift, CTRL or ALT are good keys to use to dismiss the window because they don’t introduce any new characters into the Formula field.)
3. For function name autocomplete assistance, type the first few characters of a function’s name and press CTRL-Spacebar to see a list of functions whose names match the characters you’ve typed.