Frequency Automation

wilbuchanan14

Hello,

I am not sure if this is possible, but I was wondering if anyone has an automation idea to create the following scenario:

We have temporary contractors that see a client, and then will see them for a frequency for a certain amount of time. We have a form when the client is scheduled for their first service, they enter the frequency in the format of 2w4, 1w1, which reads 2 times a week for 4 weeks, then 1 time a week for 1 week. Is there a formula/automation to create child rows with the corresponding dates, such as if 2 times a week, they will be plotted on the Tuesday and Thursdays for 4 weeks, then on Wed for the 5th week at 1 times a week?

I hope that makes sense. Would really greatly appreciate any help.

Jeff Reisman

@wilbuchanan14

Unfortunately you can't use automation to create child rows. However, there are some other options that may work.

You can use formulas to calculate dates in various columns on the same row. This can be practical depending on how many potential follow up visits there might be.

Alternatively, you could use copy row automation to create rows other sheets. For example, if the frequency is 2w4, automation could copy your row data into 8 other sheets which contain extra columns to calculate those follow up visit dates.

What's the maximum number of potential service visits that might need to be scheduled?

wilbuchanan14

Most of the time, no more than 10 weeks. The child row isn’t a deal breaker. But the added rows with automated calculation date would be a huge time saver. The scenario would be for the following example.

Contractor scheduled to see Client for an assessment on 9/12. I already have an automation that notifies the contractor and the client about the upcoming assessment via a form when one cell changes to “scheduled”. It then moves that entire row to another sheet. On 9/9, I have an automation that sends an update frequencies request to the contract. They would put in 2w4 a/o 9/12.

Automation and Formula:

When the cell is updated to 2w4 a/o 9/12, then 8 rows are created below it. With dates filled in as 9/12, 9/15, 9/20, 9/22, 9/27, 9/29, 10/4, 10/6.

That would be the most ideal way. Thank you for your help and thank you in advance.

Andrée Starå

Hi @wilbuchanan14

I hope you're well and safe!

I'm developing something similar for a client at the moment, and it might be possible to modify it to work for your use case. At the moment, it's structured to create a row automatically for each day in a range, and next, I'm going to work on doing the same, but with parent/children rows.

Do you have anything already created?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!