Nov 03, 2020 04:00 AM
Had a good formula going for the status of the books in our shop…
IF({Sales}, “ :green_circle: Sold”, “ :white_circle: For Sale”)
But then I realized, we also “ :orange_heart: Donate” and “ :beer: Trash” books (we only trash them if they get damaged, say, in a flood, and the Microsoft garbage emoji is garbage, so beer it is!).
Like the {Sales} field, the {Donations} field is a linked one, only, unlike {Sales}, in {Donations} it’s not enough to say EMPTY=For Sale and NOT EMPTY=Sold because there are three options in the Donations table {Beneficiary} single select field: Charity, Thrift, and Trash.
What’s the formula for this? Do I need to create lookup fields first?
Solved! Go to Solution.
Nov 03, 2020 09:11 AM
Ask you’ve been asking for a lot of formulas, I highly recommend learning how nested formulas work.
In the meantime, here’s an untested formula, simply because I find this situation interesting and you are always polite:
IF(
{Sales},
"Sold",
IF(
{Recipient Lookup} & "",
IF(
{Recipient Lookup} & "" = "TRASH",
"Trash",
"Donate"
),
"For Sale"
),
)
Here’s another untested formula that is a bit longer, but might be easier to understand
IF({Sales}, "Sold")
&
IF(
AND(
{Sales} = BLANK(),
{Donations} = BLANK()
),
"For Sale"
)
&
IF(
{Recipient Lookup},
IF(
{Recipient Lookup} & "" = "TRASH",
"Trash",
"Donated"
)
)
Nov 03, 2020 07:57 AM
Yes, you will probably need a lookup field.
Can you post a screen capture after you get the lookup field with sample values for all of the combinations you want?
Nov 03, 2020 08:52 AM
Ooh, good idea. Here’s what I got. Still using that first formula that only deals with sales. :slightly_smiling_face:
Nov 03, 2020 08:59 AM
In your other recent topics you have plenty examples of how to use a nested IF(), which is what you need now to incorporate “ :orange_heart: Donate” in your {Status}
field.
Nov 03, 2020 09:01 AM
Thanks! I’ll look up some YouTube tutorials on the nested IF() because it still puzzles me and I didn’t even realize that’s what was needed here.
Nov 03, 2020 09:06 AM
Standard IF() statement:
IF(
the thing you're testing,
what to do if the test comes back "true",
what to do if the test comes back "false"
)
so:
IF(
{Name} = "Kamille",
"Your name is Kamille",
"You are not Kamille"
)
A nested IF() replaces one (or both) of the what to do..."
bits with another IF() statement. You’re testing if A is true, then do X, if it isn’t true then test if B is true, and if B comes back true do Y, and if B comes back false do Z.
Nov 03, 2020 09:09 AM
That’s a super clear explanation and I’ll be sure to screenshot it for my notes. But what if you’re testing two different fields? In this case, the {Sales} link field and the {Recipient Lookup} field?
Nov 03, 2020 09:11 AM
Ask you’ve been asking for a lot of formulas, I highly recommend learning how nested formulas work.
In the meantime, here’s an untested formula, simply because I find this situation interesting and you are always polite:
IF(
{Sales},
"Sold",
IF(
{Recipient Lookup} & "",
IF(
{Recipient Lookup} & "" = "TRASH",
"Trash",
"Donate"
),
"For Sale"
),
)
Here’s another untested formula that is a bit longer, but might be easier to understand
IF({Sales}, "Sold")
&
IF(
AND(
{Sales} = BLANK(),
{Donations} = BLANK()
),
"For Sale"
)
&
IF(
{Recipient Lookup},
IF(
{Recipient Lookup} & "" = "TRASH",
"Trash",
"Donated"
)
)
Nov 03, 2020 09:13 AM
Doesn’t matter. SWITCH()
only compares one field the whole way through, IF()
doesn’t care.
I purposely didn’t write out the formula for you so that you’d try to learn to do it yourself, but look at @kuovonne’s reply and study it, a lot if you have to.
Nov 03, 2020 09:18 AM
I also hesitated about posting my formula for the same reason, but then I decided that the logic for this particular situation might be a bit difficult for someone new to coding.
Here’s a stepping stone based on @Kamille_Parks’s example:
IF(
{Name} = "Kamille",
"Your name is Kamille",
IF(
{Location} = "USA",
"You are not Kamille, but you live in the USA",
"You are not Kamille, and you do not live in the USA."
)
)
Nov 03, 2020 09:19 AM
Still having a hard time wrapping my head around this one, but it worked. I’ll come back to your examples here and keep reviewing docs and YouTube video’s like
Gareth Pronovost’s Understanding IF and SWITCH Formulas in Airtable. Thanks again to you both.
Nov 03, 2020 09:29 AM
You’re welcome!
Here are some thoughts to keep in mind as you learn:
You need to know the vocabulary and grammar of the functions in your formula. The vocabulary is the exact words IF
, SWITCH
, etc. The grammar is where you can and cannot put spaces, where you need quotes, where you need commas, etc.) This info is available in the formula field reference.
You need to understand nesting to combine multiple functions in a complex formula. This comes with practice, and it requires that you understand the vocabulary and grammar of the functions you want. The best tool for this is writing in a text editor with the multi-line, indented style demonstrated in this thread.
Understanding the logic that you want to implement is also key. This can be the most difficult aspect of programming to learn, because any give coding problem can have multiple possible solutions, and sometimes errors do not show up immediately. In general, start with simple situations and gradually build up the formula.
I am considering producing some training materials for teaching how to write complex formulas, but don’t have much time right now.
Nov 03, 2020 10:30 AM
I hope you do produce those videos when you have time. Maybe consider Skillshare. It was the first place I checked for Airtable tutorials and they had nada there. Gareth is cool on YouTube but different learners benefit from different teachers and teaching styles (I’m a former middle school language arts teacher.) I’ll be keeping an eye out for your lessons!