I am not sure if it is a formula or an automation :)

Question

I am trying to build a base for a company that delivers natural food for pets. The clients tells us the date they want their first delivery made, how often they want to receive it (every week, every 2 weeks, etc), the preferred day of the week they want to receive the deliveries (monday, tuesday, etc) and also the number of deliveries that are already paid for.
Based on that I would like the base to automatically fill the future delivery dates for each client record considering the informations we have. This will be importante so that I can set up a calendar view later that will allow me to know all the clients I have to make deliveries on any specific date.
Thanks in advance for any help and I am sorry if this is not the right place to ask.

AirOps · Accepted Answer

Hi @tsallis,

This is an interesting problem!

Typically went I solve more complex problems like this I break out the formula into tangible steps in different fields, which helps me keep track of solving the issue. I will share my process as I think it will be helpful. For reference this base is going off of assumption of today being March 16th 2023.

Below is a screenshot of my base

The first step is to quantify the preferred day of the week to a number that we can use in computations. The weekday( ) function in airtable tells us the day of the week from 0-6 (0 = sunday, and 6=saturday), and we will utilize this function to calculate the next deliveries.

I called this field "Preferred day of the week #" and used this formula to match the weekday( ) function in Airtable:

IF({preferred day of the week}="Sunday", 0,

IF({preferred day of the week} = "Monday", 1,

IF({preferred day of the week}= "Tuesday", 2,

IF({preferred day of the week}="Wednesday",3,

IF({preferred day of the week}="Thursday",4,

IF({preferred day of the week}= "Friday",5,6))))))

Next, I want to calculate the date of the second delivery as this delivery will be on the clients preferred date. I called this formula "2nd Delivery", and the formula to do this is as follows:

DATEADD(

IF(WEEKDAY({1st Delivery})={Preferred day of the week #},

DATEADD({1st Delivery}, {frequency (in weeks)},"weeks"),

IF(WEEKDAY({1st Delivery})>{Preferred day of the week #},

DATEADD({1st Delivery},(7+{Preferred day of the week #})-WEEKDAY({1st Delivery}),"days"),

IF(WEEKDAY({1st Delivery})<{Preferred day of the week #},

DATEADD({1st Delivery},7+({Preferred day of the week #}-WEEKDAY({1st Delivery})),"days")))),

{frequency (in weeks)}-1, "weeks")

Color Legend:

If the 1st delivery falls on the desired delivery date, we add 1 week to this date

If the 1st delivery falls later in the week than the preferred delivery date, find the next occurrence of the preferred day. (ie 1st delivery = 3/2/2023 (Thurs) and desired deliveries is Tuesdays, the result is 3/7/2023)

If the 1st delivery falls earlier in the week than the preferred delivery date, find the next occurrence of the preferred day the following week. (ie 1st delivery = 2/14/2023 (Tues) and desired deliveries is Thursdays, the result is 2/23/2023)

adjusts the date for the desired frequency of deliveries from customer

Now that we have the date of the 2nd delivery, we can add the final piece to our puzzle, which is called "Next Delivery". Which takes into account how many deliveries have been paid for, or more accurately described for these calculations, how many deliveries have been made. This formula is:

DATEADD({2nd delivery}, ({# of paid deliveries}-1)*{frequency (in weeks)}, "weeks")

Important: for this final piece to work, the "# of paid deliveries " field needs to remain up to date. You will likely want to use an automation to +1 to this value after each delivery has been made.

I hope this helps!

Hi Cherry, I am so happy to be in such a great community with people like you. It worked like a charm, thanks a lot for the perfect explanation! Just the final part about updating the paid deliveries field with the automation I could not do.
Also, since some clients pay for annual plans with weekly deliveries, it would be great to have some automation that fills all the future dates
Thanks again and if you ever came to Rio de Janeiro let me know and the coconut water will be on me 🙂

tsallis · Answer