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

Troubleshooting when building Formula Fields 📍 You are here

⚙️ Symptoms and troubleshooting process

When running into problems with building or updating your Formula Fields it's good to have a clear and structured way of approaching the troubleshooting process. This article will provide some tools and methods to systematically work through your Formula Field and identify the issue.

In most cases, you will experience one of three symptoms: no data, wrong data, or some text-based version of your formula. The root causes below overlap a bit, depending on your formula.

👑 Pro-tip on troubleshooting process!

If you have a longer formula, start with the easiest step and add in increments to see "where it breaks". See an example of a build-up below from simple to complex - stop at each step and see if it returns a value you're expecting (or any value at all). While the example below could seem very daunting and complex, it's more about seeing the process than understanding the formula itself.

Below we have an example of a Formula Field that does not work as intended. At first, when looking at a longer formula it's easy to get overwhelmed. However, by breaking up the formula into smaller pieces we can systematically analyze each step and understand the issue.

FIND(License.custom.Owner & { “filters”: [{ “op”: “more than”, “field”: {“id”: “value”: 10}, { “op”: “less than”, “field”: {“id”: “Value”}, “value”: 100 }], “sort”: {“lastName”: -1}, “limit” : 2 })

Step 1: Always start simple, does it work to just cross-reference our custom field from the License model? It seems to output a value, let's move on.

FIND(License.custom.Owner)

Step 2: Let's add a filter and see if that impacts our result, does anything break? While adding the filter make sure to check if the syntax is correct, sometimes typing errors can be easier to spot when incrementally re-building our formula fields

FIND(License.custom.Owner & {“filters”: [{ “op”: “more than”, “field”: {“id”: “value”}, “value”: 10 }] })

Step 3: As everything still seems to work let's add the final piece to our formula, this time it would appear as though the Formula Field stopped returning an output. So the issue is connected to the final part of the formula we added.

FIND(License.custom.Owner & { “filters”: [{ “op”: “more than”, “field”: {“id”: “value”}, “value”: 10 }, {“op”: “less than”, “field”: {“id”: “Value”}, “value”: 100 }], “sort”: {“lastName”: -1}, “limit” : 2})

Step 4: Let's look at the final piece more closely, remember that our syntax is case-sensitive. Now we can spot a typing error in the last filter. Currently, it displays "id":"Value" where the spelling, as a matter of fact, should be "value". This syntax error is the reason our Formula Field is not functioning properly. After fixing that it will return the correct value.

{“op”: “less than”, “field”: {“id”: “Value”}, “value”: 100 }]

📣 Quick Tip: If you struggle to remember the correct syntax for an operation or model for Formula Fields you can go back to double-check old formulas or look through the examples posted in the helpdesk.

📖 Things to check before troubleshooting

Some mistakes are common and easily amendable, always make sure that you check for the following before troubleshooting further.

1. Did you check the underlying data?

Often, it's that the data is "incorrect". So make sure that you double-check that what you are trying to reference:

Exists
Makes sense (spot-check a few companies)

2. Have you waited a few minutes / re-loaded the page?

Since Formula Fields are dynamic and continuously updating sometimes the update takes time to trigger throughout the platform especially with large applications. However, this should only take a couple of minutes at most, anything more is likely then due to another issue.

3. Have you tested going into the formula field, and simply pressing "update"?

Sometimes the Formula Field needs to be re-loaded and as previously mentioned allowed to work for a few minutes. Despite it not being a quick fix each time the action takes seconds and a couple potentially save hours of troubleshooting.

🔍 Potential root causes

1. Did you try to paste a formula from somewhere else and forgot to change a specific variable?

It's often smart and time-saving to copy entire or parts of formulas and then modify them to the specific case. However, always make sure that you read through the Formula Field after modifications to ensure that all references made are in fact related to this particular Formula Field.

2. Are you trying to use cross-model operators on the same-object fields?

In simple model operations (i.e., same-object) you should use operators like + for addition, - for subtraction, * for multiplication, and / for division. This works like your school math. Remember to use ( ) it properly. You can not use things like SUM or AVG here.

This formula does not work as we are using SUM(), a cross-model operator in a same-object field.

SUM(<<nps>>,<<csmScore>>) / 2

Instead, we have to re-write it like this using the same-object operator (+).

(<<nps>> + <<csmScore>>) / 2

3. Are you trying to use same-object references for cross-model fields?

Note that cross-model operations can only be done from the company object to other objects, not the other way around or between other objects. If you are trying to reference another object from the company (e.g., end-user from the company), then you always need to start the formula with a cross-model operator. See the different "Cross-model operators" in this article.

The incorrect approach to referencing a cross-object field:

<<License.value>> or <<License.custom.Owner>>

The correct approach, remember to use SUM, MIN, etc if you want to return a numeric value and FIND when returning a non-numeric value:

SUM(License.value) or FIND(License.custom.Owner)

4. Are you missing any parentheses?

For example, are you missing a }) by the end of the formula? Or are you missing one in the early parts of a filter? Make sure that each ({[ has a matching ]}) by the end.

📣. Quick tip: is to actually count them to make sure it's correct since there will sometimes be quite a few.

This is incorrect - see the missing "} )" by the end of the formula:

SUM(License.value & { "filters": [{ "op": "equal to", "field": {"id": "featured"}, "value": true }]

This is incorrect - see the missing " [ " early in the filter*:

SUM(License.value & { "filters": { "op": "equal to", "field": {"id": "featured"}, "value": true }})

This is the correct way to do it:

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

5. Tricky but common one - are you addressing the property with the wrong formula?

This will vary a bit depending on where in the formula you are. Check the general rules below against your case to see if it's the same logic.

If simple-model reference (i.e., same object, e.g., company formula field to company field)
- If system field, then write <<[field]>>, e.g., <<nps>> or <<h>> (health)
- If custom field, then write <<custom.[field]>>, e.g., <<custom.Company tier>>
If cross-model reference (i.e., from company to another object's field)
- If you are referencing the value you want to bring over, i.e., first thing in formula
  - If system field, then write [Object].[field], e.g., License.value
  - If custom field, then write [Object].custom.[field], e.g., License.custom.Tier
- If you are referencing a field to filter on, i.e., in the middle of the formula, then you don't need to add the object name to the beginning of the reference - Planhat already knows what object we are "inside"
  - If system field, then write [field], e.g., value
  - If custom field, then write custom.[field], e.g., custom.Tier

🔍. Note the difference in standard "Relevance" field and custom "Decision maker"

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

6. Do you have the wrong spelling for the field or value you're addressing?

You need to have the exact right spelling of the field you are addressing! For example:

If it's Tier then you can't write tier
If it's renewalStatus you can't write Renewalstatus
If it's License category you can't write Licensecategory

Note here that your custom fields will always have the exact spelling you gave them (as you see in the UI), but some system fields have different names in the UI to the back-end. Examples:

Company: "Health score" is called h
Licenses: "Status" is called renewalStatus
Licenses: "Start date" is called fromDate and "End date" is called toDate

To check the name of a field, go into "Manage fields" for that object and look at the light grey naming beneath the larger bolded name. You can see some examples from above in the screenshot below, like renewalStatus and fromDate

7. Are you using citation marks at the wrong place?

Some references or values need citation marks, others don't. The rules:

Don't use citation marks (i.e., [reference]) if:
- The field you're referencing has a numeric value (e.g., 8)
  - Note: in rare cases, 8 could be stored as text if the field is of type text. Double check this if nothing else seems to work
- It's the first reference of your cross-model operation, i.e., the value you want to get
  - E.g., FIND(License.value) → License.value goes without citation marks
- It's a conditional value as part of an IF-statement
- Any mathematical or cross-object operators (e.g., FIND, MAX, or +)
- The field you're referencing is either true or false, so you're writing true or false
Use citation marks (i.e., "[reference]") if:
- It's anything you're writing inside a filter, sort, or limit formula except for the cases mentioned above
Use "inequality marks" (i.e., <<[reference]>>) if:
- You are referencing a field from the same object/model

Examples:

Incorrect:

// This is incorrect - see the missing "" on the "field" and "id" of the filter
`SUM(License.value & { "filters": [{ "op": "equal to", field: {id: 
"featured"}, "value": true }]`

// This is incorrect - see the "" on the first reference to License.value
`SUM("License.value" & { "filters": { "op": "equal to", "field": {"id": 
"featured"}, "value": "true" }})`

// This is incorrect - see the "" on the true value
`SUM(License.value & { "filters": { "op": "equal to", "field": {"id": 
"featured"}, "value": "true" }})`

// This is incorrect - see the "" on the same-model reference
`<<"mrr">>`

// This is incorrect - see the "" on the same-model reference
`SUM(License.value & { "filters": { "op": "equal to", "field": {"id": 
"featured"}, "value": "true" }})`

Correct:

// This is correct
`SUM(License.value & { "filters": [{ "op": "equal to", "field": {"id": 
"featured"}, "value": true }] })`

📍 Specific root causes

Are you using the wrong data type?

For example, are you using "text" as the "data type" when you're in fact trying to calculate a number? You can see this in the window of the formula field, above the formula, where it says "What type of data is it?". If this is incorrect, you will often see this in the data module:

undefined
FIND(License.value & filter {... i.e., your entire formula in text
3 * 2

1. Are you writing IF statements with incorrect logical operators?

What you should write
- For "equals", write ==
- For "not equals", write !=
- For "larger than", write > (and inverse for "smaller than")
- For "larger or equal", write >= (and inverse for "smaller than")
If you write an incorrect operator, then issues can seem arbitrary - e.g., if you write IF(<<renewalStatus>> = ongoing, yes, no) then all properties will be yes

2. Are you defining conditional values using citation marks?

If you are building "IF"-based statements, then you should write the conditional value to test against without citation marks, even if it's text. So for example:

*// These are incorrect* `IF(renewalStatus == "ongoing",good,bad)`

*// Correct approach - without citation marks* `IF(renewalStatus == ongoing,good,bad)`