Hello,
I see the following as a serious shortcoming of Baserow: boolean functions do not work well with fields as an argument.
Example: a (formula) field is supposed to take the data from field A (link-to-table) if this field in not empty, otherwise take the data from field B (look-up).
The function “when_empty” does not work, only works with simple text fields…
Same type of problem encountered with “isblank”, “if” etc.: no way to make a Boolean test on data in field C, to take data from field A if TRUE otherwise take data from a field B (FALSE).
I did not succeed to find a work-around because if a field (lookup, link-to-table) is empty, the formula using that field as an argument returns nothing, void.
Anyway, to me the function when_empty(field(‘A’),field(‘B’)) should work, but it does not…
Same for if(field(‘C’)=0,field(‘A’),field(‘B’))
I want more than simply if(field(‘C’)=0,‘it is zero’,‘it is not zero’)
Hope there is a good solution and/or things will evolve.
Thanks in advance,
Gerard
Hello Gerard,
A link-to-table field and a look-up fields can contain multiple items which makes them arrays or lists. The function isblank() on the other hand expects a simple value like text, number, date,… This explains why it works on single text fields and not on link-to-table, lookup or rollup fields.
So, you first need to convert the list to a text. This can be done with the join() function. The result is a string that is empty when there are no elements in the link-to-table or lookup field. This will respond correctly on the isblank function.
A small (useless) demo.
I check if the field Organization (link-to-table field) has a value. If this is true, I show the content of the field Name. Otherwise, I show the content of the field Note. The formula is:
if(isblank(join(field('Organization'),'')),field('Notes'),field('Name'))
Hope this solves your problem.
Regards
Frederik
Hello Frederik,
Thank you for your prompt and clear reaction, much appreciated!
I now see what to do to get what I want in Baserow.
I just tested, works perfectly!
My problem is solved.
Suggestion: add this explanation to the user doc / formulas; it would have saved me time (tried many things, nothing worked; using the “join” function to convert to text was the key!)
Many thanks again,
Kind regards,
Gerard
PS: sent a reply by e-mail too, but got not through.
@PROXIP-Gerard, thank you for bringing this to our attention. We have added a troubleshooting guide: Formula field reference // Baserow.