Using lookup value in if statement

[Chad_Carpenter]

I have multiple tables in my base. Following is what I’m attempting to do for a formula type field named {Amount Owed}:

IF({I Owe} > 100, {I Owe} / {percent}, {I Owe})

• {I Owe} is in the table Med Bills
• {percent} resides in Med Bills but is a lookup to another table named Bills which in turn looks up to another table named Discount which is formatted as a percent field

The IF statement above works and displays {I Owe} if it is less than 100, but when the logic really needs to work of > 100, I get #ERROR! result in {Amount Owed}.

My question, can this work as described, I mean using a lookup value in my formula logic?

[Chad_Carpenter]

UPDATE

The field {Amount} is formatted as a decimal but when referenced as a lookup field in another table, it is read as text so math cannot be done on it. Is that by design? If so, how do I get around that?

[W_Vann_Hall]

Lookups and Rollups cause Airtable to do some interesting things. Most of the time, you’ll find yourself dealing with values cast to a comma-delimited string — but sometimes you’ll discover you’ve been handed access to the array itself, which can result in quite unexpected results when using the FIND() command.

You may have discovered this already, but if not (and for those who follow in our wake), the VALUE() function is used to convert a string-based representation of a number to the number itself, for use in calculations:

VALUE('235.17') + 3 will return 238.17 rather than #ERROR.

Hidden in (or perhaps missing from) documentation is the reciprocal formula; that is, the one for converting a number into a string: Simply concatenate the value with an empty string, like so:

LEN(417) returns #ERROR, while LEN(417&"") returns 3.

[Chad_Carpenter]

Using

VALUE()

Did the trick, thank you. I don’t see that documented anywhere, either VALUE() or cast, is that in the documentation so I can read more about it?

[Christoff]

The VALUE() function is documented in Formula field reference: Numeric operations and functions.

[W_Vann_Hall]

… and if, when, and how Lookup and Rollup modify or convert arrays is documented, albeit grudgingly, throughout the help pages, primarily in Guide to formula, lookup, count, and rollup fields and Rollup field reference. I say ‘grudgingly’ because, now that I’ve learned how such fields and functions tend to behave, I can understand what is meant by

if there are multiple linked records, the lookup will concatenate the cell values and separate them with a comma

and how that differs from

[j]oin all the values into a single comma-separated string.

Even so, that still doesn’t explain what this difference means to FIND() or how one should adjust formulas accordingly.

[BTW, I suspect @Kasra, et al., wince every time I misuse the word ‘cast,’ which in computer science has a very specific meaning. What I mean by it is Airtable’s automatic conversion of an array into a single string with commas (’,’) between individual entries — roughly the equivalent of a call to ARRAYJOIN({Array},","). There appear to be subtle differences in how Airtable treats rollups, lookups, and access-by-reference to rollups, lookups, and linked records. (To say nothing of what happens once you started nesting or chaining such abstract data types — to misuse yet another CS term!) I’m confident there is a coherent logic behind it, but I’ve yet to suss it out.]

[Ken_A]

W_Vann_Hall:

Hidden in (or perhaps missing from) documentation is the reciprocal formula; that is, the one for converting a number into a string: Simply concatenate the value with an empty string, like so:

LEN(417) returns #ERROR, while LEN(417&"") returns 3.

I have no idea what that means, or why… but it solved my problem. Thank you!

This is basically my usage: IF({Attendance}}>=VALUE({Last Milestone}&"")+{Classes Required},“Reward”,"")