Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 27, 2019 05:22 AM
I am trying to create a scoring system for this table. For example IF my Status is “Interested” then Field 18 equals 5. IF Status equals “Sent High Level” then Field 18 equals 2…IF Status equals “NO - PASS” then Field 18 equals 0.
Nov 27, 2019 07:41 AM
IF({Status}=“NO - PASS”,0,
IF({Status}=“Sent High Level”,2,
IF({Status}=“Interested”,5,"")))
However, this has an issue that I’ve not resolved yet. When the result is 0, the field shows as blank.
The following works, as far as displaying the 0 but I don’t know how that might impact anything you are doing with the field later.
IF({Status}=“NO - PASS”,“0”,
IF({Status}=“Sent High Level”,2,
IF({Status}=“Interested”,5,"")))
Nov 27, 2019 11:14 AM
I believe this is due to the fact that the final IF()
function ends in an empty string, while the first two return numeric values. The returned value across all nested IF()
functions should be consistent, otherwise Airtable might create unpredictable results (like this).
Because the final part of an IF()
function is optional (which lets Airtable insert the appropriate blank equivalent if the condition is false), that empty string can be removed, which fixes the problem:
IF({Status}="NO - PASS",0,
IF({Status}="Sent High Level",2,
IF({Status}="Interested",5)))
However, there’s still more that can be optimized here. Because the output is simply being switched based on the contents of {Status}
, the SWITCH()
function would be a slightly better option:
SWITCH({Status}, "NO - PASS", 0, "Sent High Level", 2, "Interested", 5)
Nov 27, 2019 02:44 PM
Thank you for all the help guys. much appreciated.
Nov 29, 2019 05:23 AM
Hi Justin,
I have tried both formulas and am getting the same result where only sent high level populates the cell.
Nov 29, 2019 05:31 AM
Check the details of your single-select options. My guess is that there might be an extra space either at the beginning or end of the text of those other two options. Airtable auto-trims that stuff for display purposes, but leaves it intact when passing to formulas, which would result in a failed match.
Nov 29, 2019 05:32 AM
Airtable is also case sensitive, so you’ll need to change “NO - PASS” in the formula to “No - Pass” for that one to match.
Nov 29, 2019 05:34 AM
Wow! Thanks for the quick reply! Damn you extra space :slightly_smiling_face: