Introduction to Formula Fields

How to build Formula Fields 📍 You are here

Examples of Formula Fields

Troubleshooting when building Formula Fields

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.

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.

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

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 models that can be used for the cross operations for companies are:

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:

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.

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.

📣 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.

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:

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

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.

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

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.

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.

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: ==, >, <, >=, <=, !=, &&, ||, !.

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

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.

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.

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>>.

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.

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:

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:

📣 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.

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_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 subtract four days from that.

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

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

📌 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.

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.

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.

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.

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.

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"].

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):

📌 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.

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:

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

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

The supported filter operations are:

📌 Important to note:

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

Sort the result array using the defined property and order.

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

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.

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.

Example limiting the number of results to 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.