Hi Abby. Totally doable. You will need an automation, also. First, you need a formula field that calculates the next due date. It could look something like below.
IF({Frequency}!='3-monthly',
DATEADD({Order Date},
SWITCH({Frequency},
'4-weekly',4,
'6-weekly',6,
'8-weekly',8),
'weeks'),
DATEADD({Order Date}, 3 ,'months')
)
There would be other ways to accomplish this, but you get the idea. It’s a little more work at first, but you probably want to have another field that keeps the original order date (maybe a simple created on field if the orders come in from a form or other automation).
The second part to getting this to work is the automation. Depending on your workflow (when you no longer mind if the {Order Date} field gets updated} you have an automation that looks at your {Next Due Date} field and triggers when it is “today”. The action is simply replacing {Order Date} with {Next Due Date}.
Anyway, this is just off the top of my head. Good luck!
Hi Abby. Totally doable. You will need an automation, also. First, you need a formula field that calculates the next due date. It could look something like below.
IF({Frequency}!='3-monthly',
DATEADD({Order Date},
SWITCH({Frequency},
'4-weekly',4,
'6-weekly',6,
'8-weekly',8),
'weeks'),
DATEADD({Order Date}, 3 ,'months')
)
There would be other ways to accomplish this, but you get the idea. It’s a little more work at first, but you probably want to have another field that keeps the original order date (maybe a simple created on field if the orders come in from a form or other automation).
The second part to getting this to work is the automation. Depending on your workflow (when you no longer mind if the {Order Date} field gets updated} you have an automation that looks at your {Next Due Date} field and triggers when it is “today”. The action is simply replacing {Order Date} with {Next Due Date}.
Anyway, this is just off the top of my head. Good luck!
I. DIDNT. KNOW. you could switch in a dateadd. why didn’t I think of that
I. DIDNT. KNOW. you could switch in a dateadd. why didn’t I think of that
Function nesting can occur anywhere. It’s most often done by nesting IF() functions inside each other, but it’s easy to forget that others can be nested as well. As long as you know that the output of a given function matches the input requirements of another, creative nesting can be used to more efficiently solve lots of problems.
Hi Abby. Totally doable. You will need an automation, also. First, you need a formula field that calculates the next due date. It could look something like below.
IF({Frequency}!='3-monthly',
DATEADD({Order Date},
SWITCH({Frequency},
'4-weekly',4,
'6-weekly',6,
'8-weekly',8),
'weeks'),
DATEADD({Order Date}, 3 ,'months')
)
There would be other ways to accomplish this, but you get the idea. It’s a little more work at first, but you probably want to have another field that keeps the original order date (maybe a simple created on field if the orders come in from a form or other automation).
The second part to getting this to work is the automation. Depending on your workflow (when you no longer mind if the {Order Date} field gets updated} you have an automation that looks at your {Next Due Date} field and triggers when it is “today”. The action is simply replacing {Order Date} with {Next Due Date}.
Anyway, this is just off the top of my head. Good luck!
Thank you so much for you help, that saved me so much time with the formula! Really appreciate it.
Sorry to call on more advice, but the only trouble I’m coming into is when trying to set-up the automation. It won’t let me select the Next Due Date as a field to replace the Order Date with. Because it’s a formula it’s not selectable at this step. Any more advice as to how I could get around this?
Thanks again!
Function nesting can occur anywhere. It’s most often done by nesting IF() functions inside each other, but it’s easy to forget that others can be nested as well. As long as you know that the output of a given function matches the input requirements of another, creative nesting can be used to more efficiently solve lots of problems.
i should say it never occurred to me to use it like that ive been writing nested ifs nested in a bigger switch formula. never thought to just switch those too haha
Thank you so much for you help, that saved me so much time with the formula! Really appreciate it.
Sorry to call on more advice, but the only trouble I’m coming into is when trying to set-up the automation. It won’t let me select the Next Due Date as a field to replace the Order Date with. Because it’s a formula it’s not selectable at this step. Any more advice as to how I could get around this?
Thanks again!
Hi Abby. Hmm…I just tried it in my scratch table automation and I could select my formula field. Do you have your formula field formatted in a similar manner to your date field? It shouldn’t matter if you’ve been able to format it as a date or date/time.
I think I may have sorted out the issue. Thank you so much for all your help!
@Abby_Solomon Glad to know that you got the answer you were seeking! If you would, please mark the appropriate comment above as the solution to your question (if one of those helped guide you to a solution). This helps others who may be searching with similar questions. Thanks!
