# For Sale, Sold, and Donated Formula

Topic Labels: Formulas
Solved
1945 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",