Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Consignment Formula Question: Auto discounting a p...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

1
2574
9

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 28, 2022 09:07 PM

I track consignment items in Airtable. This is my consignment pricing structure:

Day 0-29: Initial List Price

Day 30-45: Initial List Price minus 15%

Day 45-90: Initial List Price minus 25%

Day 91: Expired

What formula will auto-discount the initial price by the above amounts based on the date when the item sells?

Can the entire cycle of auto-discounts be calculated in one column?

What formula can be used to mark an item “expired” on day 91? - could this formula also be included in the same column as the auto-discount column?

:slightly_smiling_face:

Thanks in advance for any help or insight.

Solved! Go to Solution.

Reply

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 29, 2022 10:42 PM

@Judith_Soto The formula as listed above isn’t formatted as preformatted text, so it contains styled quotes, and styled quotes won’t work in Airtable. If you copied and pasted without fixing the quotes, that will lead to an error. I’ll give you a variant in a bit that fixes that problem, but there are other problems.

In your logic, there are multiple conditions that all trigger on the state of the `{Sold Date}`

field. You have conditions that tie to the elapsed days, as well as conditions for Paid or Unpaid, but those latter two are actually tied to the two “sold” conditions farther up the list; i.e. you want to know the paid/unpaid status of sold items in addition to knowing if they’re sold. With the formula above, these conditions are separate, not connected (plus the last `{Sold Date}`

comparison looked for an empty field, not a filled field). The problem there is that Airtable latches onto the first condition that tests as true, working from the outside in. It won’t return multiple outputs from different levels of the nesting. Looking at the formula above, if the condition that returns “Sold” triggers, you won’t know the paid/unpaid status because that check isn’t done until the innermost level.

Another issue is the day counts that you listed. The two criteria that you indicated are “<90” and “>91”, but “<90” means 89 or lower, and “>91” means 92 or higher. That means that a difference of 90 or 91 days won’t trigger either condition. The second criteria should be “>=90” to ensure that nothing is missed.

Finally, a shortcut way of checking for an empty/filled field is to just use the field name itself. Comparing against an empty string or the `BLANK()`

function is largely unnecessary. (I say “largely” because there’s a small issue with numeric fields, but I won’t get into that here.)

This revised formula has all of these issues addressed:

```
IF(
DATETIME_DIFF(NOW(), {Date of listing}, "days") < 90,
IF({Sold date}, "Sold - " & IF({Payout date}, "Paid", "Unpaid"), "Active"),
IF(
DATETIME_DIFF(NOW(), {Date of listing}, "days") >= 90,
IF({Sold date}, "Sold Past Expiration - " & IF({Payout date}, "Paid", "Unpaid"), "Expired")
)
)
```

It might even be safe to assume that if the date difference isn’t less than 90, then it’s automatically >=90, so this even simpler formula might do the job:

```
IF(
DATETIME_DIFF(NOW(), {Date of listing}, "days") < 90,
IF({Sold date}, "Sold - " & IF({Payout date}, "Paid", "Unpaid"), "Active"),
IF({Sold date}, "Sold Past Expiration - " & IF({Payout date}, "Paid", "Unpaid"), "Expired")
)
```

Reply

9 Replies 9

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 29, 2022 01:42 AM

You will need a field that shows the initial listing date. This could be either a “Date created” field, which will auto-fill when the record is created, or a date field that you fill manually. Then your formula will use DATETIME_DIFF(NOW(), {Date of listing}, “days”) to give you how many days have passed since the initial listing. Then you wrap that same formula inside IF formulas, i.e.

IF(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<30, {Price}, IF(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<45, {Price}-{Price}/100 * 15, IF(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<90, {Price}-{Price}/100 * 25, “Expired”)))

This just says "If less than 30 days have passed since the listing, charge full price; otherwise if less than 45 days have passed, take of 15% of the price; otherwise if less than 90 days have passed, take off 25%; otherwise show “Expired”.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 29, 2022 09:15 AM

Thanks so much, @JonathanB. That worked like magic!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 29, 2022 10:27 AM

Wonderful! Glad to hear it :grinning_face_with_big_eyes:

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 29, 2022 10:48 AM

@JonathanB Can I ask for help with a related question?

I want to create a Status column that shows the status of the consignment item as “Active” (not sold), “Sold”, “Expired” (you gave me the formula above), “Paid”, or “Unpaid”

I have fields that I was using to calculate the values. I’m taking a stab at nesting IF-THEN formulas but running into issues.

This is the logic:

If the DateDIFF is <90 days AND the “Sold Date” is blank, the value is “Active”

If the DATEDIFF is <90 AND the “Sold Date” is not blank, the value is “Sold”

If the DATEDIFF is >91 days AND the “Sold Date” is blank, the value is “Expired”

If the DATEDIFF is >91 days AND the “Sold Date” is not blank, the value is “Sold Past Expiration” (this happens sometimes)

If the “Sold Date” is not blank AND the “Payout Date” is blank the value is “Unpaid” otherwise “Paid”

Thanks again!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 29, 2022 01:09 PM

You need to use IF(AND(A, B), “Value1”), IF(AND(C, D), “Value2”, …)) etc i.e.

IF(AND(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<90, {Sold date}=""), “Active”, IF(AND(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<90, {Sold date}!=""), “Sold”, IF(AND(DATETIME_DIFF(NOW(), {Date of listing}, “days”)>91, {Sold date}="", “Expired”, IF(AND(DATETIME_DIFF(NOW(), {Date of listing}, “days”)>91, {Sold date}!="", “Sold Past Expiration”, IF(AND({Sold date}="", {Payout date}="", “Unpaid”, “Paid”))))))))

Nested “IF” formulas can get tricky if you try to do them all at once. I usually lose track of the number of brackets I’ve opened that I need to close at the end! Try doing one part of the formula at once, closing it off, then adding the next IF statement just inside the closed bracket, closing that off, and continuing like that. Each time you put in part of the formula, check that the formula field in Airtable works, and proceed from there :slightly_smiling_face: Close the same number of brackets as you’ve opened. Sometimes I have to go through and count them to make sure it all balances!

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 29, 2022 04:41 PM

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 29, 2022 10:42 PM

@Judith_Soto The formula as listed above isn’t formatted as preformatted text, so it contains styled quotes, and styled quotes won’t work in Airtable. If you copied and pasted without fixing the quotes, that will lead to an error. I’ll give you a variant in a bit that fixes that problem, but there are other problems.

In your logic, there are multiple conditions that all trigger on the state of the `{Sold Date}`

field. You have conditions that tie to the elapsed days, as well as conditions for Paid or Unpaid, but those latter two are actually tied to the two “sold” conditions farther up the list; i.e. you want to know the paid/unpaid status of sold items in addition to knowing if they’re sold. With the formula above, these conditions are separate, not connected (plus the last `{Sold Date}`

comparison looked for an empty field, not a filled field). The problem there is that Airtable latches onto the first condition that tests as true, working from the outside in. It won’t return multiple outputs from different levels of the nesting. Looking at the formula above, if the condition that returns “Sold” triggers, you won’t know the paid/unpaid status because that check isn’t done until the innermost level.

Another issue is the day counts that you listed. The two criteria that you indicated are “<90” and “>91”, but “<90” means 89 or lower, and “>91” means 92 or higher. That means that a difference of 90 or 91 days won’t trigger either condition. The second criteria should be “>=90” to ensure that nothing is missed.

Finally, a shortcut way of checking for an empty/filled field is to just use the field name itself. Comparing against an empty string or the `BLANK()`

function is largely unnecessary. (I say “largely” because there’s a small issue with numeric fields, but I won’t get into that here.)

This revised formula has all of these issues addressed:

```
IF(
DATETIME_DIFF(NOW(), {Date of listing}, "days") < 90,
IF({Sold date}, "Sold - " & IF({Payout date}, "Paid", "Unpaid"), "Active"),
IF(
DATETIME_DIFF(NOW(), {Date of listing}, "days") >= 90,
IF({Sold date}, "Sold Past Expiration - " & IF({Payout date}, "Paid", "Unpaid"), "Expired")
)
)
```

It might even be safe to assume that if the date difference isn’t less than 90, then it’s automatically >=90, so this even simpler formula might do the job:

```
IF(
DATETIME_DIFF(NOW(), {Date of listing}, "days") < 90,
IF({Sold date}, "Sold - " & IF({Payout date}, "Paid", "Unpaid"), "Active"),
IF({Sold date}, "Sold Past Expiration - " & IF({Payout date}, "Paid", "Unpaid"), "Expired")
)
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 30, 2022 12:25 AM

Thanks Justin. You’re right of course. :slightly_smiling_face:

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 30, 2022 09:53 AM

Thanks @Justin_Barrett it worked!