Please fill in the questionnaire below.
Help Questionnaire
Describe the problem
Describe, step by step, how to reproduce the error or problem you are encountering.
Create TableA and TableB.
Create a linked field LinkedField in TableA linking to TableB.
Create a record in TableB.
Create several records in TableA, some that has LinkedField set to link to the record in TableB, some that have not set it (ie, the LinkedField is empty, no relations).
Create a formula field in TableA, MyFormula
Set MyFormula to all kinds of variants for how to check if LinkedField has any values and then do something based on it, with the ultimate intent being to look up LinkedField if LinkedField is set and use a default otherwise
Here are some examples of variants that fail:
- if(equal(count(field(‘LinkedField’)), 0), ‘Nothing’, field(‘LinkedField’))
- when_empty(field(‘LinkedField’), ‘Something’)
- if(is_null(field(‘LinkedField’)), ‘Null’, ‘Something’)
- Creating intermediate other fields with e.g. t(totext(field(‘LinkedField’()) and then doing is_null or when_empty against that field.
What DOES work is
- if(equal(count(field(‘LinkedField’)), 0), ‘Nothing’, ‘Something’)
but it does not give me access to the values of LinkedField when LinkedField is set.
Qualified guesses at what is wrong and how to fix it
Overall, what all of this looks like is that there is eager evaluation in if() and when I do field(‘LinkedField’) and LinkedField is empty it generates an error that invalidates the expression. This error is not visible in the UI; it looks just like a blank string.
Typically, for an expression language like this, you need if() to be lazy - ie, it cannot evaluate the arguments until it’s evaluated the condition, and then it has to evaluate just the appropriate branch. Alternatively, you need to treat error as a first class value and provide functions like is_error (and a workable display that shows it) rather than having errors abort the evaluation of everything.
