Help

Re: For Sale, Sold, and Donated Formula

Solved
Jump to Solution
1162 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Alba_Machado2
7 - App Architect
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

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

See Solution in Thread

12 Replies 12

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?

Alba_Machado2
7 - App Architect
7 - App Architect

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

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.

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.

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.

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?

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

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.

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

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. Heart

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.

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!