Skip to main content
How to build Formula Fields
Christian Dreyer avatar
Written by Christian Dreyer
Updated over 2 months ago

๐Ÿ“” Formula Fields Content Library - what are you looking for?

How to build Formula Fields ๐Ÿ“ You are here


๐Ÿ›  Creating a Formula Field

Before diving into what we can include in our Formula Field we first have to understand how we can create them. A Formula Field is created in the same way as any custom field with the exception that the Formula box must be โœ… . Then a new input field will appear and this is where we will be building our Formula Fields.

๐Ÿ“ฃ Quick Tip: As always when creating a custom field we must include what type of data will be stored in that field and this will be especially important for Formula Fields. Since data within a Formula Field can be dynamically operated upon it's important to think through what data type your end-product will be.


๐Ÿ—’ Static Formulas

The simplest and rarely used form of Formula Fields is that of static formulas, below is the general form of a static formula in pseudo-code. However, the logic and syntax behind the static formula are the basics for building more advanced and dynamic Formula Fields.

// General form of a static formula `[NUMBER OR STRING] [MATH OPERATOR] [NUMBER OR STRING]`

The static formula could be used to display the result of a mathematical operation or the concatenation of string. e.g.

// Mathematical operation `((5 + 1) / 20) * 30
// String `great + company`


๐Ÿ” Structural Rules

In order to use Formula Fields, you must be aware of some rules relating to how we reference and handle different properties and models.

  • The evaluation of the formula can only be applied to properties of the same type, if properties of different types are referenced the result will be null or zero. This means that you for example cannot logically add together a date and a string, which also intuitively makes sense.

  • Cross model operations can only be used for the Company model, meaning that it is only possible to reference related models of the company model. It is not possible to create a formula field with cross related operation for the Licenses for example.

๐Ÿ“ The models that can be used for the cross operations for companies are:

  • EndUser

  • Company

  • Opportunity

  • Churn

  • User

  • Invoice

  • License

  • Sale

  • Conversation

  • Task

  • Workflow

  • Issue

  • Asset

  • Project

  • NPS


๐Ÿ”ฝ Simple model reference

Above you could see examples of a completely static formula, however, the real value of Formula Fields come from dynamic referencing. Below we can see the syntax for how we can incorporate properties from a model to create a simple model reference.

The general form of the simple reference:

*// General form of a simple reference property* `<<[PATH TO PROPERTY]>>`

In order for Formula Fields to read and reference properties from the same model, it is required to wrap the path to the property with less than (<<) and greater than (>>) signs. e.g.

For the next examples, our formula field will be operating in the licence model and referring to the properties of "value" and "mrr". Note that we have selected "number" as our data type for this Formula Field.

In the above example, the properties wrapped inside the signs are direct properties from the License model (look at the field "What is this data related to?", but it is possible to reference nested properties. For example, custom fields and their corresponding properties will live under ".custom" meaning we have to invoke that each time we want to reference that custom field.

A reminder that by custom fields we mean fields that are not automatically created but rather has been added manually to display custom data. Below is an example of how we can refer to a custom field.

<<value>> + <<custom.newValue>>

The above example will return the addition of the numerical value of "value" and "custom.newValue" and save that in a Formula Field under the licence module. Meaning that if you were to enter the Data Module you could add a column that displays the corresponding "value" + "custom.newValue" for each of your licences.

Another simple example is that of multiplication. Let's say that each December we actively track a specific data point for that month and want to use that to understand how it looks on a yearly basis. Then we would have to multiply our monthly value by 12 to annualized this, and preferably we would like to have this field change dynamically. This is easily done with a Formula Field as you can see below.

<<custom.dataPoint>> * 12

๐Ÿ“ฃ Quick Tip: Having your Formula Field in the data module on another browser tab while creating/editing is a great way to see if your formula is working as intended. Remember though that apart from saving the changes to your Formula Field. you have to refresh the page in order to see any updates made.


๐Ÿ”„ Cross model references

As previously mentioned to cross model reference means that you might want to have a field on the company level referencing a certain datapoint from another object. For example, you would like to display the value of licences on the company level.

The way to reference cross model property is through defined operations where it is possible to apply filters to select all or only some properties from the cross-referenced model (check the operation section for further information). e.g.

The general form of the cross model reference in pseudo-code:

*// General form of a cross reference operation* `[OPERATION]([MODEL].[PROPERTY PATH] & [OPTIONS])`

Below is a simple example of how we in the company model are referencing the property (value) from the licence model.

SUM(License.value)

The above example is a simple addition of all the values for the related License model, with SUM() intuitively being short for a summary (as in summarizing the license data up to the company level)

๐Ÿ“Œ Keep in mind that since we are not referencing from within the licence model but rather from the company model the (<<) and (>>) signs are not required to read the data. However, notice that is required to reference the model first capitalized (License) and then, using dot notation, reference the path to properties to which the operation is going to be applied (value).

It is also possible to reference nested properties using the same dot notation. In the example below the only difference is that we are referring to a custom field (newValue) which then needs to be referenced with ".custom" accordingly.

SUM(License.custom.newValue)

It is possible to mix referenced properties, operations and constants in the formula. e.g.

(SUM(License.value) * MAX(License.mrr)) / (1000 + <<mrrTotal>>)

In the above example, we can see that we are referring to data both from the licence model and thus having to use "Licence." as well as from within the company model itself having to apply (<<) and (>>). In addition, we have the static integer of 1000 included as well-meaning that we are mixing three different ways of referencing properties and across different models.


โš™๏ธ Single model operations

The supported Single model operations are:

LENGTH: Get the length (as number) of an array property.

The referenced property should be the type Number in order to apply the operation. The example below returns the length of the custom property of "multipicklist" as an integer.

LENGTH(<<custom.multipicklist>>)

IF: Evaluates a conditional expression and returns the truthy or the falsy section depending on the result of the conditional expression.

The available conditional operators are: ==, >, <, >=, <=, !=, &&, ||, !.

Equal To

==

Not Equal To

!=

Not

!

And

&&

Or

||

Greater Than

>

Less Than

<

Greater Than or Equal To

>=

Less Than or Equal To

<=

The general form and logic of the IF() operator, works very similarly to the syntax for most programming languages.

IF([CONDITIONAL EXPRESSION], [TRUTHY VALUE OR REFERENCE], [FALSY VALUE OR REFERENCE])`

Below is an example of the IF operator which checks if the mRR total for a company is greater than 1000 and returns 10 if it's or 20 if it's not.

IF(<<mrrTotal>> > 1000, 10, 20)

Another example of how the IF() operator could be used is simply to compare and see if a value is equal to 5000 returning the "property" if it's or "name" if it isn't.

IF(<<custom.value>> == 5000, <<custom.Property>>, <<name>>)

INCLUDES: Checks if a string reference contains a substring and returns true or false

This is by default case insensitive. However, this operator accepts a third boolean parameter which enables case sensitive matching.

In the example below we can see that our Formula Field is determining whether or not the substring "Toyota" is included in the <<name>>.

INCLUDES(<<name>>, Toyota)
INCLUDES(<<name>>, toyota, true)

Notice that the second time we add the condition of true to the end of the formula thus making the logic case sensitive as opposed to the first time in which it does not take that into consideration.

IS_EMPTY: Checks if the reference value exists, if it is an object or an array checks if it's empty returning true or false.

// Checks if custom.value is empty `IS_EMPTY(<<custom.value>>)`
// Checks if a system value is empty `IS_EMPTY(<<value>>)`

Conditional nesting

It is possible to do combinations of the IF operator with the boolean operator INCLUDES, IS_EMPTY and also it's possible to nest IF operators inside another IF operator

๐Ÿ“Œ Examples:

// Checks if the company name has the string "My Company" `IF(INCLUDES(<<name>>, My Company), <<mrrTotal>>, <<nrrTotal>>)`
// Checks if the custom.multipick is empty, if it's not then checks if the mrrTotal is equal to 500, if it's empty just returns 100 `IF(IS_EMPTY(<<custom.multipick>>), IF(<<mrrTotal>> == 500, 10, 5), 100)`


โฐ Date Formulas

In general date formulas expect two arguments that could be (depending on the operation) a reference to a date, another operation returning a date, another operation returning an integer, a math operation which result is an integer or just a simple integer.

The general form of the date formulas:

[OPERATION]([DATE PROPERTY PATH / OPERATION RETURNING A DATE], [DATE PROPERTY PATH / INTEGER / OPERATION RETURNING A DATE OR INTEGER]

๐Ÿ“ฃ Quick Tip: There is a special key to refer to "today" within the date formulas if you use @today as a param on any of the date operations, this key will be replaced with today's date (dynamically) and used to calculate the result.

Supported date operations are:

DAYS_DIFF: This operation expects two dates reference as argument and returns the difference (in days as an integer) between the first param and the second param.

For example the difference in days as an integer for today and when someone was last active on our platform.

DAYS_DIFF(@today, <<lastActive>>)

DATE_ADD_DAYS: This operation expects one date reference and a numeric param (integer) as an argument and returns the supplied date plus the number of days passed in as a second param.

For example, we can take the date that someone was last active on our platform and add four days to that.

DATE_ADD_DAYS(<<lastActive>>, 4)

DATE_SUBTRACT_DAYS: This operation expects one date reference and a numeric param (integer) as an argument and returns the supplied date minus the number of days passed in as a second param.

For example, we can take the date that someone was last active on our platform and add four days to that.

DATE_SUBTRACT_DAYS(<<lastActive>>, 4)

DATES_MAXOF: This operation expects two date references and returns the max date between the two.

DATES_MAXOF(<<lastActive>>, <<lastTouch>>)

DATES_MINOF: This operation expects two date references and returns the min date between the two.

DATES_MINOF(<<lastActive>>, <<lastTouch>>)

๐Ÿ“Œ Example of the use case for date formulas:

The use cases for date formulas vary but are broad and can be utilized in many ways to reference dates important to your business. One important thing could be to get the actual date of when we expect an onboarding to start or finish. Let's say that we have internal guidelines that within 90 days of the signed contract the customer should have begun the onboarding.

By using date formulas we can dynamically reference these data points and create an output which then can be used across modules in e.g. Tasks or Activities.

With the above Formula Field, we then automatically have a field that returns the date for which we have to have started our onboarding in order to reach a deadline. Notice that we are using DATE_ADD_DAYS() to add our 90 days and that we have set our data type to "date". This field can then be displayed and used in various modules for planning, tracking or statistics.


๐Ÿงฎ Cross model operations

In order to use the cross model operations, we utilize a type of operation called filters which allows us to segment and select applicable data. In the next block, we will go into filters more in detail so for now just focus on the supported cross model operators mentioned below.

SUM: Simple addition of all the properties selected that matched the filtering criteria.

The filter criteria is not required, if the options object is not defined or the filter property is missing the operation will sum up all the existing properties.

The example below sums up all licences which hold a value that is greater than 10 but less than 100.

SUM(License.value & { "filters": [{ "op": "more than", "field": {"id": "value"}, "value": 10 }, { "op": "less than", "field": {"id": "value"}, "value": 100 }] })

MAX: Get the maximum value of a specified property.

The example below gets the max value of the EndUser's relevance that are featured, users.

MAX(EndUser.relevance & { "filters": [{ "op": "equal to", "field": {"id": "featured"}, "value": true }] })

MIN: Get the minimum value of a specified property.

The example below gets the min value of the EndUser's relevance that are featured, users.

MIN(EndUser.relevance & { "filters": [{ "op": "equal to", "field": {"id": "featured"}, "value": true }] })

AVERAGE: Get the average value of a specified property.

For example, the Formula Field below gives the average value of the License's mrr that have a value greater than 100.

AVERAGE(License.mrr & { "filters": [{ "op": "more than", "field": {"id": "value"}, "value": 100 }] })

COUNT: Get the count of all entries for the specified model that matched the defined filter criteria.

This operation does not get applied to a specified property but to all entries into the related model. For example, the Formula Field below counts conversations which type is included in the array ["email", "note", "reminder", "QBR"].

COUNT(Conversation & { "filters": [{ "op": "any of", "field": {"id": "type"}, "value": ["email", "note", "reminder", "QBR"] }] })

FIND: This operation will return an array of all selected properties that matched the filter criteria.

Since the returned value of a FIND operation is an array this operation has other options as part of the filters (check the options section for further details):

  • Sort: Sort the results using the referenced property and order.

  • Limit: Limit the results to the specified limit number.

๐Ÿ“Œ This operation does not get applied to a specific property but to all entries into the related model. The example below finds two EndUsers names which experience is greater than 80, sorted alphabetically descending (-1) using their last name.

FIND(EndUser.name & { "filters":[ { "op": "more than", "field": {"id": "experience"}, "value": 80 }] , "sort": {"lastName": -1}, "limit": 2})


๐Ÿ” Option Object (operations)

Using option objects is a key part of effectively building and customising Formula Fields, most often through the use of filters. The options object containing the filters, sort and limit properties to be applied to the cross model operation.

General form of the option object:

{"filters": [..., {...}], "sort": {...}, "limit": [Number]}

Filters

It's an array of objects in which each object define a filter operation, field and value. e.g.

{"filters": [{"op":"[OPERATION NAME]","field":{"id":"[PROPERTY NAME]"}, "value" : [VALUE]}]}

The filters option can be applied to any of the exiting operations (SUM, MAX, MIN, AVERAGE, COUNT, FIND).

The supported filter operations are:

  • "equal to"

  • "not equal to"

  • "more than"

  • "less than"

  • "has no value"

  • "has value"

  • "any of"

  • "none of"

  • "equal to (date)"

  • "not equal to (date)"

  • "equal to (days ago)"

  • "before (date)"

  • "after (date)"

  • "before (days ago)"

  • "after (days ago)"

๐Ÿ“Œ Important to note:

  • The filters that should be applied to properties that contain arrays or multipicklist are any of and/or none of.

  • additional details about after and before days/date ago operations behaviour can be looked into here.

๐Ÿ“Œ Below is an example that filter the properties that have a "more" greater than 10 and a "value" less than 100.

{ "filters": [{ "op": "more than", "field": {"id": "mrr"}, "value": 10 }, { "op": "less than", "field": {"id": "value"}, "value": 100 }]}

  • "op": "more than" dictates what operation our filter will be based on, in this case, we want to filter our data based on the magnitude of a numerical value and thus use the "more than" operator.

  • "field": { "id": "mrr" } states what data we will apply our operator to, in this case, we are looking at the "mrr" field.

  • "value" : 10 is simply the numeric value with which our operator will compare our field value. Note that the 10 is not inside quotation marks as it is a numeric value and not a string.

Sort

Sort the result array using the defined property and order.

The sort option can only be applied to the FIND operation.

{"sort": {"[PROPERTY]": [ORDER]}}

The property could be any property from the reference model and the order could be 1 for ascending order or -1 for descending order as shown in the example below.

{"sort": {"lastName": -1}}

Limit

Limit the results to the defined number, if there are fewer results than the number specified the limit does not get applied.

The limit option can only be applied to the FIND operation.

{"limit": [NUMBER OF RESULTS]}

Example limiting the number of results to 3.

{"limit": 3}

๐Ÿ“Œ Example of how to utilize option operators.

In this example, we are using the cross model operator FIND() to display the date of the last meaningful contact in this case for the QBR (Quarterly Business Review). As we are cross-referencing the conversations model we have included "Conversation." as an appendix to our field reference as a way for our Formula Field to understand that we are referring to data from the Conversation model despite being within the company model.

In this example, our filter is using the operator "equal to" since we want to select the date field that displays QBR. Meaning that our Formula Field will take the date value from the conversation type that matches the value QBR.

Since FIND() returns an array of all selected properties which matches our filter we need to sort the data so it only returns a single value, the last point of contact. This is done by using the sort operator.

  • "sort": {"date": -1}, "limit": 1 means that we are sorting the array in descending order (-1) and that we are limiting our return to only include one value which is controlled by the numeric value connected to the "limit" operator.

Did this answer your question?