For Sale, Sold, and Donated Formula

[Alba_Machado2]

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?

[kuovonne]

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]

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

[Kamille_Parks]

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.

[Alba_Machado2]

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.

[Kamille_Parks]

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.

[Alba_Machado2]

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?

[kuovonne · Accepted solution]

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

[Kamille_Parks]

Alba_Machado2:

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.

[kuovonne]

Kamille_Parks:

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