Skip to main content

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.


Nope! Your formula would be:


IF(

   DATETIME_FORMAT({Updated}, "YYYY-MM-DD") = DATETIME_FORMAT(TODAY(), "YYYY-MM-DD"),

   "Update",

   SWITCH(

      {STATUS},

      "For Sale", " ",

      "On Hold", "Delete",

      "Sold", "Delete"

   )

)

Nope! Your formula would be:


IF(

   DATETIME_FORMAT({Updated}, "YYYY-MM-DD") = DATETIME_FORMAT(TODAY(), "YYYY-MM-DD"),

   "Update",

   SWITCH(

      {STATUS},

      "For Sale", " ",

      "On Hold", "Delete",

      "Sold", "Delete"

   )

)

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…

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…


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

      )

   )

)

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

      )

   )

)

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”, " ",

)

)

)

)


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”, " ",

)

)

)

)



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.

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.


Thanks! That did the trick! Sorry about my shiftiness. 😕

Reply


Terms & Conditions