For Sale, Sold, and Donated Formula

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?

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

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.

IF(
   the thing you're testing,
   what to do if the test comes back "true",
   what to do if the test comes back "false"
)

IF(
   {Name} = "Kamille",
   "Your name is Kamille",
   "You are not Kamille"
)

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?

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

IF(
  {Sales},
  "Sold",
  IF(
    {Recipient Lookup} & "",
    IF(
      {Recipient Lookup} & "" = "TRASH",
      "Trash",
      "Donate"
    ),
    "For Sale"
  ),
)

IF({Sales}, "Sold")
& 
IF(
  AND(
    {Sales} = BLANK(),
    {Donations} = BLANK()
  ),
  "For Sale"
)
&
IF(
  {Recipient Lookup},
  IF(
    {Recipient Lookup} & "" = "TRASH",
    "Trash",
    "Donated"
  )
)

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!