I cannot get if statement to work with linked or lookup fields

1

I am trying to understand how to check if a value is present in a column when that is either a link to a table (Room)

or a lookup (Box Room)

What I expect i

Other columns Room Box Room Formula Result
Lounge Lounge
Lounge Lounge
Lounge Lounge
Garage Garage

and each of the below is my trying to understand how it works but I cannot get it to work yet

fail means I could not get an answer in all for Formula Result cell

pass just means i could get all 4 cells to populate

Formula: if(count(field(‘Box Room’)) > 0, “hi”, “ho”)
Result: PASS

Other columns Room Box Room Formula Result
Lounge hi
Lounge hi
Lounge ho
Garage hi

Formula: if(count(field(‘Box Room’)) > 0, field(‘Box Room’), “ho”)
Result: FAIL

Other columns Room Box Room Formula Result
Lounge Lounge
Lounge Lounge
Lounge
Garage Garage

Formula: if(count(field(‘Box Room’)) > 0, count(field(‘Box Room’)), “ho”)
Result: PASS

Other columns Room Box Room Formula Result
Lounge 1
Lounge 1
Lounge ho
Garage 1

Formula: if(count(field(‘Box Room’)) > 0, totext(field(‘Box Room’)), “ho”)
Result: FAIL

Other columns Room Box Room Formula Result
Lounge Lounge
Lounge Lounge
Lounge
Garage Garage

Formula: if(count(field(‘Box Room’)) > 0, ‘hi’, field(‘Room’))
Result: FAIL

Other columns Room Box Room Formula Result
Lounge
Lounge
Lounge Lounge
Garage
2

hey @DavidS, the issue is that Link to table and Lookup fields are arrays.

count() works because it checks array length, but returning the field directly fails because formulas can’t return arrays.

You need to extract a value using first() or join().

3

Thanks, I got it to work using join, but it tells me first is not a function. BaseRow v2.0.6

For others that read this - what worked:

if(count(field(‘Box Room’)) > 0, join(field(‘Box Room’), ‘,’), join(field(‘Room’), ‘,’))

Are there any plans to make the editor multiline?

4

Glad to hear you found a solution, @DavidS.

We have some plans to improve the UI of the formula editor. I’ll check with the team to see whether this is a short-term or long-term plan, and I’ll let you know. :raised_hands: