📔 Formula Fields Content Library - what are you looking for?

Examples of Formula Fields 📍 You are here

Troubleshooting when building Formula Fields

🔽 Single-model Formulas

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. The formula below shows the difference in days between the Customer Since (system field) and Go-Live (custom field).

DAYS_DIFF(<<customerFrom>>, <<custom.Go-Live>>)

LENGTH: Get the length (as a number) of an array property (this will count the number of responses selected). The formula below is counting the number of products acquired.

LENGTH(<<custom.Products Acquired>>)

IF: Evaluates a conditional expression and returns true or false depending on the result of the conditional expression. For example, in the formula below the custom scoring is based on the company health score (system field, name: "h"):

If health is higher than 6, the score is 1
If health is 5, the score is 2
If health is lower than 4, the score is 3

IF(<<h>> > 6, 1, IF(<<h>> <4, 3, IF(<<h>> == 5,2,0)))

INCLUDES: Checks if a string or an array reference contains a substring or element and returns true or false (By default is case insensitive). The formula below checks the system "name" field to see if it contains the word "Toyota".

INCLUDES(<<name>>, Toyota)

INCLUDES_ALL: Checks if a string or an array reference contains ALL the elements listed in the array. Returns true or false. The formula below is checking that the system "name" field contains "Toyota" and "industries".

INCLUDES_ALL(<<name>>, ["Toyota", "industries"])

INCLUDES_SOME: Checks if a string or an array reference contains **SOME** elements listed in the second array. Returns true or false. The formula below checks to see if the system name contains "Toyota" or "industries".

INCLUDES_SOME(<<name>>, ["Toyota", "industries"])

🔄 Cross-model Formulas

Currently, only possible to build on the Company object.

Date of the last meaningful conversation, e.g. QBR.

FIND(Conversation.date & {"filters": [{"op": "equal to", "field":{"id":"type"}, "value": "QBR"}], "sort": {"date": -1}, "limit": 1})

Find and display up to five featured end-user names ordered by their last name.

FIND(EndUser.name & {"filters": [{"op": "equal to", "field": {"id": "featured"}, "value": true}], "sort": {"lastName": -1}, "limit": 5})

The total number of Open Prio1 Tickets (source: Zendesk, Freshdesk, Jira Service Desk, ...).

COUNT(Conversation & {"filters": [{"op": "equal to", "field": {"id": "type"}, "value": "Ticket"}, {"op": "equal to", "field": {"id": "custom.Priority"}, "value": "P1"}, {"op": "equal to", "field": {"id": "custom.Status"}, "value": "Open"}]})

Total time spent on interactions of a specific type with customers in the last 90 days (custom field on Conversations).

SUM(Conversation.custom.Time Spent & {"filters": [{"op": "after (days ago)", "field": {"id": "date"}, "value": 90}] })

Revenue import: display the License Parent Ids for each Product on the company level, the user can then use this to import licenses and have them linked to the correct row (blue bars).

FIND(License._id & {"filters": [{"op": "equal to", "field": {"id": "product"}, "value": "Licenses MRR"}], "sort": {"fromDate": 1}, "limit": 1})

Display a License field on Company level:

FIND(License.custom.Plan (deal) & {"filters": [{"op": "any of", "field": {"id": "custom.Plan (deal)"}, "value": ["Business", "Enterprise"]}] })

Display a License picklist text field on Company level:

FIND(License.custom.Stage & {"filters": [{"op": "any of", "field": {"id":"custom.Stage"}, "value": ["Closed Won", "In Negotiation", "Proposal Presented", "Abandon", "Active / Prospecting", "Closed Lost"]}], "limit": 1})

Display a License number field on Company level:

FIND(License.custom.Number of RF Users & {"filters": [{"op": "has value", "field": {"id": "custom.Number of RF Users"}, "value": true}], "limit": 1})

Display a License date field on Company level:

FIND(License.renewalDate & {"filters": [{"op": "equal to", "field": {"id": "renewalStatus"}, "value": "renewed"}], "sort": {"renewalDate": -1}, "limit": 1})

Show the number of an object on the Company - eg the Number of Assets

COUNT(Asset)

Multiple IF Statements linked together to show a revenue split of 20% of License value

IF(<<product>> == Product A, <<value>>* 0.2, 
IF(<<product>> == Product B, <<value>>* 0.2, 
IF(<<product>> == Product C, <<value>>* 0.2, 
IF(<<product>> == Product D, <<value>>* 0.2, 
IF(<<product>> == Product E, <<value>>* 0.2, 
IF(<<product>> == Product F, <<value>>* 0.2, 
IF(<<product>> == Product G, <<value>>* 0.2, 
IF(<<product>> == Product H, <<value>>* 0.2, 0))))))))

Identify tickets matching multiple different statuses:

COUNT(Conversation & {"filters": [{"op": "equal to", "field": {"id": "type"}, "value": "ticket"}, {"op": "any of", "field": {"id": "status"}, "value": ["open", "another status"]}]})

Identify tickets not matching a status:

COUNT(Conversation & {"filters": [{"op": "equal to", "field": {"id": "type"}, "value": "ticket"}, {"op": "none of", "field": {"id": "status"}, "value": "closed"}]})

👑 Pro tip: Showing a Calculated metric in a Company custom field (this allows you to use a calculated metric as a merge tag in an email)

To get the Calculated Metric ID you need to navigate to the metric that you want to use Data Module > Metrics > Select your metric

Replace the word calculated to usage and append the brackets around the text:

BEFORE:
calculated.6464b31a3063d1228f2ee0ce
AFTER:
<<usage.6464b31a3063d1228f2ee0ce>>