Recurring task with task results stored per row

kristyf

Good morning all

I have created a grid for 'preventative maintenance' program - whereby the grid shows the task, owned by, frequency (daily/weekly/monthly etc), date last completed (user enters this each time they complete the task) and date next due is calculated (from the date last completed). 

But - my gap is that i also need to store results at each task when its repeated - ie a Monthly task - check pressure settings on pipe - record setting - so July result is stored, the new date is calculated for task to be completed by, the automated workflow sends the reminder/alert, and the user completes the task again on a date in August, and needs to store the result.

So far, i can only see doing this with comments? but thats much harder to extract and report on, and doesnt provide the easy visual needs for history.

I am hoping there is a way (formula or whatever) that basically, you start with row 1 Parent/header task - and the first level row heirarchy task with its due dates.

When the task is done (by entering the task complete date), smartsheet creates a NEW ROW (indented) with an exact copy of the previous row task requirement,but new dates (calculated based on the criteria) and a blank comments field etc etc.  Each time the row has the task completed (by entering the date), on calculation of the new date, a new row with its 'complete by' field is created.  

its the automation here thats important - i dont want my infield users to have to manually create new rows for each task reminder date.  

thoughts?

Note - on infield users will be using CARD VIEW - with traffic light colouring for the tasks calculated by no of days til take needs to be completed

thanks

Kristy

https://app.smartsheet.com/sheets/4Wph73q87R3cgfQ3W2jmWch8q5Hh7qMxPmQ6XMf1

 

Andrée Starå

Hi Kristy,

I can think of a few ways to structure this.

1. Recurring alert with a link to a form, so a row is created for each task
2. Sheet structured with multiple instances of the same task with a pre-defined recurrence pattern or using formulas.
3. Automated with the help of a third-party service like Zapier that would copy the previous row but unfortunately, there isn't a way to automatically indent it.

Would any of those methods work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I was thinking either Andree's first suggestion, or...

 

Since the users are using card view, you could use a column with a hyperlink to the form, and another column that would extract the most recent completion date from the form entries to automatically calculate the next completion due date.

 

This way they can select the hyperlink to fill out the form early if they needed/wanted to complete the task before the due date.

.

Basically they would see the card view. The tasks would be sorted out by the color based on the next due date. The task name would be at the top of the card and the next due date would be just below that.

They could click on the task name which will take them to a form where they enter the date of completion and then click submit.

The card for that task will automatically be updated with the new due date and will move accordingly based on your color scheme.

.

Does this sound like it could work?

kristyf

Hey Paul and Andre

Thanks for your responses - i think all the solutions would work.

What i have managed so far, using RYG and check boxes, is a new row in a grid for each interval of the task (so 12 rows if its a monthly task for example) and then the card filter options to only shows those incomplete. 

i have this formula to calculate the NEXT DUE date (thanks to Andre) - but, i am stuck when it comes to a month value greater than 12 - ie a quarterly task last completed 15/10/19 should have a new task due date of 15/1/20 

=IF([Last Completed]@row = "", "", IF([Test Frequency]@row = "Daily", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row), DAY([Last Completed]@row) + 1), IF([Test Frequency]@row = "Weekly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row), DAY([Last Completed]@row) + 7), IF([Test Frequency]@row = "Monthly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 1, DAY([Last Completed]@row)), IF([Test Frequency]@row = "Quarterly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 3, DAY([Last Completed]@row)), IF([Test Frequency]@row = "Yearly", DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row), DAY([Last Completed]@row))))))))

 

can you help? i have tried IFERROR but its not working

 

hope you can help

thanks

 

Paul Newcome

Using the IFERROR is a step in the right direction.

.

IF([Test Frequency]@row = "Monthly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 1, DAY([Last Completed]@row))

to use the IFERROR:

IF([Test Frequency]@row = "Monthly", IFERROR(DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 1, DAY([Last Completed]@row)), DATE(YEAR([Last Completed]@row) + 1, 1, DAY([Last Completed]@row)))

.

Basically we tell the formula that if the frequency is monthly, add a month, but if there is an error (the month +1 is greater than 12) then we add one to the year instead and change the month number to 1.

.

We would use the same concept for quarterly, but there are two different ways to approach it. The first way is to assume that quarterly would always be the months of 3, 6, 9, and 12. If that's the case, we would set it up exactly as we did for Monthly, but instead of changing the month number to 1, we would change it to 3.

.

However...If it is quarterly and the last completed month was month 11, that won't work. So the second option would be used to cover any situation where the quarterly month numbers deviate from 3, 6, 9, and 12. I would actually suggest this option overall as it will also work for those tasks where the quarterly months do not deviate from the "norm".

.

To do this, we would use the same setup of an IFERROR to change what gets added where in the date. We will still add 1 to the year in the IFERROR portion, but instead of specifying a month, we would do "MONTH([Last Completed]@row) - 9". This means that a last completed in October would give a next due in January, November would give February, and December would give March. It would look like this...

.

IF([Test Frequency]@row = "Quarterly", IFERROR(DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 3, DAY([Last Completed]@row)), DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row - 9, DAY([Last Completed]@row)))

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

kristyf

Genius!! and very well explained!! I feel smarter already

thank you!!!

i have saved this one to my templates as i am sure i am going to need it moving forward

 

cheers

K

Paul Newcome

Happy to help.

kristyf

Hey Paul

For some reason now, i am getting a 'INVALID DATA' error for Quarterly selections - current task finished 16/7/19, next task now due by 16/10/19 and the following hits error (should be 16/1/20).

I also need to flush this formula out to cover 'weekly' IF ERROR because currently, it is adding day +7 but if the prior transaction date is 25th of the month, then this doesnt work.

Can you help me build it please so i can finish it off?

thanks so much in ancitipation :)

=IF([Last Completed]@row = "", "", IF([Test Frequency]@row = "Daily", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row), DAY([Last Completed]@row) + 1), IF([Test Frequency]@row = "Weekly", DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row), DAY([Last Completed]@row) + 7), IF([Test Frequency]@row = "Monthly", IFERROR(DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 1, DAY([Last Completed]@row)), DATE(YEAR([Last Completed]@row) + 1, 1, DAY([Last Completed]@row))), IF([Test Frequency]@row = "Quarterly", IFERROR(DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 3, DAY([Last Completed]@row)), DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row - 9, DAY([Last Completed]@row)), IF([Test Frequency]@row = "Yearly", DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row), DAY([Last Completed]@row))))))))))

 

OMG - this is soooo confusing!! 

Would really appreciate more help

thanks

Kristy

 

Paul Newcome

I'll take a closer look here in a bit, but here's my initial observation:

 

Your weekly issue is actually easy enough to fix. You will also want to implement this into your "daily" because you have it set up the same way which will lead to the same issue eventually.

.

When you are adding days to a date, it is much more effective to use

DATE(yyyy, mm, dd) + n

as opposed to 

DATE(yyyy, mm, dd + n)

.

There will always be 7 days in a week, so using +7 will always return the same day of next week no matter what that date is.

Same thing with daily. You don't want the 32nd of the month. You want tomorrow regardless of the date.

.

So here is my suggestion for IF's 2 and 3...

 

...............IF([Test Frequency]@row = "Daily", [Last Completed]@row + 1, IF([Test Frequency]@row = "Weekly", [Last Completed]@row + 7, .................

.

The reason we use the drawn out IFERROR process when adding months and quarters is because you aren't adding a set number of days. Depending on the month and year, there are four different possibilities for how many days are in the specified month. This in turn plays into how many days are in a quarter.

 

Since we can't specify (we can but it's horribly painful) exactly how many days to add if we want the same date except one month later, we just add 1 to the month with in turn leads to the IFFEROR to account for going into a new year.

.

So there's my initial look that takes care of the weekly issue you knew you had and the daily issue you didn't know about yet. Haha. 

.

I'll post again when I've had a chance to look at the quarterly issue. 

Paul Newcome

... ... ... ... IF([Test Frequency]@row = "Quarterly",

IFERROR(

DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 3, DAY([Last Completed]@row)),

DATE(YEAR([Last Completed]@row) + 1, MONTH([Last Completed]@row - 9, DAY([Last Completed]@row)), ...

.

So I broke it down to really take a look at the nuts and bolts of things and believe I may have found the issue (the bold portion above will be explained at the end, but that's where the issue is).

 

Here is the breakdown simplified...

 

IFERROR(open

     DATE(open

          YEAR(open close),

          MONTH(open close),

          DAY(open close)

     ) close DATE

,

     DATE(open

          YEAR(open close),

          MONTH(open close),

          DAY(open close)

     ) close DATE

.

The IFERROR hasn't been closed. If you look closely at the Monthly portion of the formula, you will see the additional closing parenthesis before moving on to the Quarterly IF that is missing from before moving on the the Yearly IF.

 

... ... ... IF([Test Frequency]@row = "Monthly",

IFERROR(

DATE(YEAR([Last Completed]@row), MONTH([Last Completed]@row) + 1, DAY([Last Completed]@row)),

DATE(YEAR([Last Completed]@row) + 1, 1, DAY([Last Completed]@row))), ... ...

.

Does that all make sense?

kristyf

I am absolutely loving your detailled explanations! Thank you, Thank you, Thank you! I appreciate the time it must take to respond like this, it honestly helps sooooo much!!

Its all working now - and yes, i hadnt considered i would end up with a 'weekly' problem, but in my testing yesterday, it occurred - you were one step ahead!

 

Massive thanks

K

 

Paul Newcome

Happy to help! 

 

I prefer to be as detailed as I can be without going too overboard with it.

 

As far as being one step ahead goes... I deal with dates in formulas A LOT. I have learned all kinds of crazy quirks, tips, and tricks with those things. I've hit PLENTY of walls when building out solutions, so I figure if someone can learn from my experience then I may as well share. I know how frustrating it can get. Haha