Topic Labels: Formulas
Solved
1194 6
cancel
Showing results for
Did you mean:
7 - App Architect

Creating a table that will be split into different views for different online marketplaces, as they each require different fields. The Status field at Abebooks corresponds to the Update Delete field at Amazon, though, so I’m making the Update Delete a formula field that converts the language like this:

‘’'SWITCH(
{STATUS},
“For Sale”, " ",
“On Hold”, “Delete”,
“Sold”, “Delete”
)

My question is how can I add a statement that will look at the last modified time field I’ve named Updated and return the value “Update” only if that Updated field has today’s date? So that I can get only the updated today records into their own view to be downloaded as a CSV files I can use for the online marketplaces? I feel like this is a thorny one, but this community has surprised me on multiple occasions so maybe I’m just too much of a newb to recognize real Airtable thorniness.

1 Solution

Accepted Solutions
16 - Uranus

Okay if BOTH “sold” and “on hold” are highest order it would have been better to mention that. Assuming you don’t change the prompt again:

``````IF(
OR(
{Status} = "Sold",
{Status} = "On Hold"
),
"Delete",
IF(
DATETIME_FORMAT({Updated}, "YYYY-MM-DD") = DATETIME_FORMAT(TODAY(), "YYYY-MM-DD"),
"Update"
)
)
``````

Note: Your tweaked version doesn’t nest properly. A nested IF() will always look like this when simplified:
`IF(a, b, IF(d, e, IF(g, h, IF(j, k, IF(m, n, o)))))`
All end parenthesis go at the end.

6 Replies 6
16 - Uranus

``````IF(
DATETIME_FORMAT({Updated}, "YYYY-MM-DD") = DATETIME_FORMAT(TODAY(), "YYYY-MM-DD"),
"Update",
SWITCH(
{STATUS},
"For Sale", " ",
"On Hold", "Delete",
"Sold", "Delete"
)
)
``````
7 - App Architect

Thanks, Kamille! Looks like I didn’t think this one through, though. If the last modified field is today and I change the status to sold, then the Update Delete field should say “Sold” instead of “Update.” Sold should override “update.” So maybe a bit thornier than I initially made it out to be…

16 - Uranus

This still isn’t particularly difficult (for me), you just have to decide what status should supersede what, and then just write the formula in that order:

``````IF(
{Status} = "Sold",
"Sold",
IF(
DATETIME_FORMAT({Updated}, "YYYY-MM-DD") = DATETIME_FORMAT(TODAY(), "YYYY-MM-DD"),
"Update",
SWITCH(
{STATUS},
"For Sale", " ",
"On Hold", "Delete",
)
)
)
``````
7 - App Architect

I tried entering the formula just as you had it, but I got the dreaded “Invalid Formula.” Then I tried tweaking it to adjust the supersede order, but, of course, that didn’t help matters any. Here’s my tweaked version:

IF(
{Status} = “Sold”,
“Delete”,
IF(
{Status} = “On Hold”,
“Delete”,
)
IF(
DATETIME_FORMAT({Updated}, “YYYY-MM-DD”) = DATETIME_FORMAT(TODAY(), “YYYY-MM-DD”),
“Update”,
SWITCH(
{STATUS},
“For Sale”, " ",
)
)
)
)

16 - Uranus

Okay if BOTH “sold” and “on hold” are highest order it would have been better to mention that. Assuming you don’t change the prompt again:

``````IF(
OR(
{Status} = "Sold",
{Status} = "On Hold"
),
"Delete",
IF(
DATETIME_FORMAT({Updated}, "YYYY-MM-DD") = DATETIME_FORMAT(TODAY(), "YYYY-MM-DD"),
"Update"
)
)
``````

Note: Your tweaked version doesn’t nest properly. A nested IF() will always look like this when simplified:
`IF(a, b, IF(d, e, IF(g, h, IF(j, k, IF(m, n, o)))))`
All end parenthesis go at the end.

7 - App Architect

Thanks! That did the trick! Sorry about my shiftiness. :confused: