Skip to main content
Solved

Auto-updating event field

E

I have a table where users submit events that happen on a weekly and monthly basis. For example: a yoga class every Saturday at 9am or a group run the first Sunday of every month. I don’t want to add a ton of records of that same event but would like the original date field (Event Date) to be updated so that it populates the next coming date.

So process would be:

  1. Event is submitted
  2. Event Date is populated with first class date ex: 5/20/20
  3. After the first class date passes, it is updated to the next coming date: 5/27/20

Is there a formula that’ll auto-update that Event Date field? Or at least spit out that new date in a new formula field?

Best answer by Kamille_Parks11

If things happen on a varied reoccurring basis, you may consider a Single Select field called {Frequency} with options for weekly and monthly, then you could have a Formula field with a formula like this:

IF(IS_AFTER({First Event Date},TODAY()), {First Event Date}, SWITCH(Frequency, "weekly", DATEADD({First Event Date}, DATETIME_DIFF(TODAY(), {First Event Date}, "weeks")+1, "weeks"), "monthly", DATEADD({First Event Date}, DATETIME_DIFF(TODAY(), {First Event Date}, "months")+1, "months")))

That formula will check if the first time the event is supposed to happen comes after the current date. If it is, the formula field will display the date entered in the form. Otherwise, the formula uses a SWITCH() function to determine whether to add weeks or months to the first event date.

View original
Did this topic help you find an answer to your question?

Kamille_Parks11
Forum|alt.badge.img+25

If things happen on a varied reoccurring basis, you may consider a Single Select field called {Frequency} with options for weekly and monthly, then you could have a Formula field with a formula like this:

IF(IS_AFTER({First Event Date},TODAY()), {First Event Date}, SWITCH(Frequency, "weekly", DATEADD({First Event Date}, DATETIME_DIFF(TODAY(), {First Event Date}, "weeks")+1, "weeks"), "monthly", DATEADD({First Event Date}, DATETIME_DIFF(TODAY(), {First Event Date}, "months")+1, "months")))

That formula will check if the first time the event is supposed to happen comes after the current date. If it is, the formula field will display the date entered in the form. Otherwise, the formula uses a SWITCH() function to determine whether to add weeks or months to the first event date.

ScottWorld
E
2 people like this
  • ScottWorld
    ScottWorld
  • E
    Erendira_Garcia
Justin_Barrett
Forum|alt.badge.img+20

Welcome to the community, @Erendira_Garcia! :grinning_face_with_big_eyes: To answer your question re: whether or not a formula could update the date, unfortunately the answer is no. Formula fields can only update themselves based on the data in other fields.

While the formula that @Kamille_Parks proposed would work for your “Saturday at 9am” situation, it doesn’t work for the “first Sunday of every month” option because adding a month to a given date will keep it on the same numerical day; e.g. May 3rd (the first Sunday) becomes June 3rd (the first Wednesday).

In my own planning base, I’ve created a system that lets me choose a variety of intervals:

  • every 1-15 days/weeks/months/years
  • specific repeating days of the week (e.g. Monday and Friday)
  • specific Nth weekday (e.g. first Sunday, third Thursday, etc.)

This is all controlled using one multi-select field I call {Frequency}. That’s the “easy” part. The rest is contained in 14 formula fields1 that carefully interpret the contents of {Frequency}, finally spitting out a date in {Next Due} based on the current date in {Due}. Once a day (at 3am, I think), an Integromat scenario runs and copies the date from {Next Due} into {Due} for any record where {Due} has just passed, so that each record only progresses to its next option when it’s scheduled to do so. I set the current due date and frequency for each record that needs such a schedule, and the rest is automatic.

Anyway, I share all that just to let you know that it’s possible to create a system that supports multiple types of intervals between events, but it’s not a quick setup. If you want more info, message me. I do plan on sharing more about this system at some point—very likely on my YouTube channel—but it’s much more complex than anything currently in my production schedule, making it harder to determine when/where it will fit.

Notes

  1. Why 14 formula fields? I like to break down complex formulas into smaller parts where possible. If I’m running the same calculation in multiple places, I’ll turn it into its own formula field, then reference that field where needed instead of rewriting the same code several times, similar to using variables in scripting/programming. Technically I could use fewer than 14 formulas if I didn’t split things out like this, but IMO it would make the setup more difficult to manage. With more formulas, things are more compartmentalized.
ScottWorld
Kamille_Parks11
2 people like this
  • ScottWorld
    ScottWorld
  • Kamille_Parks11
    Kamille_Parks11
E

Thanks so much @Kamille_Parks and @Justin_Barrett these answers were just what I was looking for!
Kamille, your code worked perfectly! I was having such a hard time wrapping my brain around it so I truly appreciate your help. Maybe Airtable will base future recurring fields off that? :smirk:

Justin, you were right that it wouldn’t really work month-wise (definitely works for when you need the same date each month like paying a bill, but not when you need it to be based on day of the month). I appreciate you sharing your base break-down too, it might be easier to use an integration app to update the monthly dates rather than remember to do it myself.

Thank. you both!

Justin_Barrett
Kamille_Parks11
2 people like this
  • Justin_Barrett
    Justin_Barrett
  • Kamille_Parks11
    Kamille_Parks11

Reply


Most helpful members this week

Terms & Conditions