Help with finding an error in table

Meera_Malhotra · ‎Jul 29, 2021

Hi all!

I have information in one tab on the database which is called “B12 Recipe Claim”. This value is an IF statement, where if the B12 content (specified elsewhere in the database) is above a certain value its “ :white_check_mark: High B12”

So now, I want to, in another tab, add a score for “ :white_check_mark: High B12” for a specific attribute (i.e. “calm”). I’ve set up these scores elsewhere in the database.

So, the formula should now be: IF({B12 Recipe Claim}=“ :white_check_mark: High B12”,{Calm B12 Score Lookup}, 0). I’ve used this formula for similar functions elsewhere in the table and it works fine. However I’ve noticed this in the table:

Basically, when the value is true, it doesn’t pull the fact that the value should be 0.2, it’s just blank or returns the other value (0). It does the same for scores relating to other things that also depend on the “ :white_check_mark: High B12”. I’ve tried making sure its not an issue with pulling in the score (so the lookup that’s to the left of the formula), and that I’ve got the spacing between the characters correct for the “value”. I can’t understand why it’s not pulling it across correctly.

Can anyone help? This happens a couple times for other “ :white_check_mark: High X” and I’ve possibly made the same mistake elsewhere so it would be helpful to fix! Please do let me know if I need to provide any other information

Dominik_Bosnjak · ‎Jul 29, 2021

Add

& "" //the rest of your formula

to the nil-returning field and enjoy that wonderful feeling of discovering yet another JavaScript quirk. Don’t worry, there’s plenty more syntactic excitement from where that came from. :grinning_face_with_sweat:

Edit:
and if that doesn’t work like my brain right now, then try encapsulating the 0.2 formula with VALUE().

In all seriousness, if you’re interested in the why and how, follow the duck. Because what you’re having is a typing issue, I’m just too inept to re-read your comment right now and confirm.

kuovonne · ‎Jul 29, 2021

The issue stems from the fact that the fields are lookup fields. Lookup fields cause more problems in Airtable formulas than any other field type. This is because lookup fields sometimes act as numbers/text/dates, and sometimes act as arrays (lists). I recommend using rollup fields instead of lookup fields whenever possible.

Dominik’s trick of tacking & "" to then end of your field name turns the field value into a text string, which is the simplest method of making the value into a text string.

Justin_Barrett · ‎Aug 04, 2021

@Meera_Malhotra Here’s a table that I built to help clarify what’s going on with lookup fields when pulling data from specific types of target fields: