Setting Follow-up Dates depending on Stage Selected

[Will_Stokes]

I have a base that is used for our CRM/tracking manufacturing orders. We have fields for Stage and Next Follow-up date. I need to be able to set the follow-up date differently depending on what the stage is set to. Here’s the basic info. One other item is we only want these dates set to workdays, no weekend dates.

If Stage = Opportunity then set next follow-up date to today + 2 days
If Stage = Specified then set next follow-up date to today + 25 days
If Stage = Delayed then set next follow-up date to today + 180 days
If Stage = Bidding then set next follow-up date to today + 2 days

What is the best way to accomplish this? (btw, we are willing to pay to get this done :slightly_smiling_face: )

[Justin_Barrett]

Welcome to the community, @Will_Stokes! :grinning_face_with_big_eyes: This can be done via a formula. You didn’t mention the name of your source date field, so I’ll just use {Date} for this formula.

IF(
    Stage,
    WORKDAY(
        Date,
        SWITCH(
            Stage,
            "Specified", 25
            "Delayed", 180
            2
        )
    )
)

This formula will only create a follow-up date if a value exists for {Stage}, and the WORKDAY() function ignores weekends. The appropriate values are sent to that function for the “Specified” and “Delayed” options. If it’s not either of those, the fallback value is 2, which will apply for “Opportunity” and “Bidding.”

[Will_Stokes]

Justin,

Thank you so much. Another question when will this formula “fire”? We’d like it to run when the record is created and anytime the stage is changed. Is that possible?

[Will_Stokes]

Justin,

I tried to create a new field with this formula in it but it returns and error.

Just to let you know the Trigger Field is “Stage” and the field to be update is the “Next Follow-Up Date”

[Justin_Barrett]

Will_Stokes:

Another question when will this formula “fire”?

The formula will refresh any time that one of the formula components changes (in this case, the stage or the initial date; more on that later).

Will_Stokes:

We’d like it to run when the record is created and anytime the stage is changed. Is that possible?

Yes and no. Formulas always run when new records are made, and as I said above, any time that any of the variable elements in the formula change. That leads me to this next comment…

I just realized that I was a bit hasty in reading your original formula. You asked for the target date to be “today + X days”. In that case, a formula won’t work because “today” is constantly changing. In other words, that formula will keep pushing the date farther and farther out if you use the TODAY() function reference in place of Date. What you want for “today” is a static reference to the date the update occurs, which can’t be done via a formula. The only way to pull that off would be to trigger an automation that runs the calculation when the stage changes and inserts the target date into a static date field.

Will_Stokes:

I tried to create a new field with this formula in it but it returns and error.

You didn’t specify the details of the error, but if you used the exact formula that I wrote above, my gut says that it was an unknown field reference for {Date}. As I indicated earlier, that was meant as a placeholder for a field that I thought contained a starting date. If you don’t have a field matching that name, Airtable will give you an error.

I noticed that you sent a PM to discuss this further, so I’ll continue our conversation there.

[Zach_B]

I actually am trying to set up this exact scenario lol

When a stage is selected via a single select, it would give a date for the next time to follow up

Any chance someone has a copy of the script used to solve Will’s problem? (Willing to pay if necessary)

[Will_Stokes]

Zach, actually I was able to work with Justin (the community leader who commented above) and pay him to do exactly what we needed. You should be able to contact him and get it done. He was great to work with and very affordable.

[Zach_B]

I managed to figure it out haha, just modified some formulas and applicated it to my use case

But thanks!