Skip to main content
Solved

Using last modified time to populate Amazon's Update Delete field

  • October 20, 2020
  • 6 replies
  • 39 views
A
Forum|alt.badge.img+14

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.

Best answer by Kamille_Parks11

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.

6 replies

Kamille_Parks11
Forum|alt.badge.img+27

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"
   )
)
A
Forum|alt.badge.img+14
  • Alba_Machado2
  • Author
  • Known Participant
  • 54 replies
  • October 20, 2020

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…

Kamille_Parks11
Forum|alt.badge.img+27

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",
      )
   )
)
A
Forum|alt.badge.img+14
  • Alba_Machado2
  • Author
  • Known Participant
  • 54 replies
  • October 20, 2020

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

Kamille_Parks11
Forum|alt.badge.img+27
  • Kamille_Parks11
  • Brainy
  • 2679 replies
  • Answer
  • October 20, 2020

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.

A
Forum|alt.badge.img+14
  • Alba_Machado2
  • Author
  • Known Participant
  • 54 replies
  • October 20, 2020

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. :confused:


Terms & ConditionsAccessibility statement