Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# For Sale, Sold, and Donated Formula

Topic Labels: Formulas
Solved
496 12
cancel
Showing results for
Did you mean:
7 - App Architect

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?

1 Solution

Accepted Solutions
18 - Pluto

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"
)
)

``````
12 Replies 12
18 - Pluto

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?

7 - App Architect

Ooh, good idea. Here’s what I got. Still using that first formula that only deals with sales. :slightly_smiling_face:

16 - Uranus

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.

7 - App Architect

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.

16 - Uranus

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",
"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.

7 - App Architect

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?

18 - Pluto

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"
)
)

``````
16 - Uranus

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.

18 - Pluto

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",
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."
)
)
``````
7 - App Architect

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.

18 - Pluto

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.

7 - App Architect

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!