What is your method for creating recurring tasks?

I have a Task List sheet which is pretty standard stuff. Task Name, Due Date, Assigned To.

But we create some tasks that are a recurrence, like, "Check on Person A Every Two Days."

Obviously, I can manually (using a formula to increment my dates =WORKDAY([Due Date]5, 2)) create a bunch of tasks out into the future, and then use some filtering to hide the far future items from active task lists. But I'd like to be more creative about this (and I'd like to be hands off and let my users run this thing).

I'd love to create a workflow that takes certain fields (Task Name and Assigned To) and copies them to a new record, and adds two days to Due Date and writes that date to the Due Date column in the new record. Is that possible? Because if I can do that, I can trigger the workflow when a user check the Done box.

I can't figure out how to do that ^ so I thought I could write the record to a holding sheet, add two days to the due date in the holding sheet, and then copy the fields back to the Task List. The problem is that I can't figure how to add two days to Due Date in the holding sheet and then copy that updated data back to the Task List. This is because Due Date writes to Due Date, and writes back to Due Date.

Does anyone have a solution to this?

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭

    This is driving me crazy. I feel like I am really close. What I am doing now:

    When DONE is checked on the TASK LIST, if that record has the RECURRING TASK checkbox checked, the row gets copied to a HOLDING sheet. The HOLDING sheet has a CREATED DATE system field. Then I have a TRANSFORM sheet which uses VLOOKUP to pull details for the MAX of CREATED DATE record from the HOLDING sheet. That means that whenever a new record gets written to the HOLDING sheet, the records in the TRANSFORM sheet "change." But this isn't a change that triggers my action, which is to copy this new row back to the TASK LIST. I can trigger that action by manually editing any random field in the TRANSFORM sheet, but I'm not about that - I need to trigger the action via this formula. Or come up with an alternative approach.

  • Hi @James Keuning

    Do you have multiple possible cadences for when these tasks would be recurring? For example, a dropdown list that indicates how often a task should pop up, so "Check on Person A Every Two Days" and "Check on Person A Every Ten Days", etc.

    If you have a column that identifies this per-row, we could set up recurring workflows based on what's selected in this "Recurring" column.

    1. The first thing I would do is have a Record a Date workflow set in this sheet to populate a "Last Completed" Date column in your sheet, triggered when "Done" is checked.
    2. Then we can use a Clear Cell workflow where when that Completed Date is 2 days in the past, it clears out the "Done" checkbox so that row is waiting to be completed again.
    3. Then your users could either work directly in this sheet, using a Filter to see what needs to be completed that day, or you could set up a Report for them so they're always viewing the filtered list.

    I would create multiple Clear Cell workflows with different conditions, depending on what is selected in the Recurrence column. For example, the "every 2 days" recurrence could use a workflow like this:

    Then the next recurrence would have a different number of days and a different condition.

    If you need to see a historical record, you could also have a Copy Row workflow to a tracking sheet when that "Done" box is checked.

    Cheers!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • James Keuning
    James Keuning ✭✭✭✭✭

    Thanks @Genevieve P., that is a creative solution. I have not used the x days after workflow, and that's a creative use! The only issue is that I need to log the completed tasks. I know I could use Smartsheet's Activity Log, but I prefer to have the info in the sheet.

  • What about having a second sheet for the "log"? Each time the "done" is checked you could send that row to the secondary log sheet and track progress there.

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • NikkiOno
    NikkiOno ✭✭✭✭

    @Genevieve P.

    I am also trying to create recurring tasks for our organization employees with automation workflow to request an update every .... (this changes for different tasks).

    These are the store managers and tasks assigned. I was able to set up the clear cell date workflow. Each store manager has the same tasks that I want them to check completed but how can I set up the workflow to request an update to where they only see the tasks listed to them without setting up a separate workflow for each person/task? I feel like there has to be a more efficient way.


  • Hi @NikkiOno

    When you're sending an update to "contacts in a cell", this means that each contact will only receive an update request for the rows that they are associated with!

    Meaning that you don't need to create the Condition where "Responsible = Contact" because that's already being filtered in the Action Block.

    See: Action blocks: Specify what kind of automation is triggered

    Let me know if that makes sense or if it would be helpful to see screen captures.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • NikkiOno
    NikkiOno ✭✭✭✭

    That saved me so much time! Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!