automation for adding date?

Jared Ross
Jared Ross ✭✭
in Formulas and Functions

So I'm fully aware of the automation to add a date when a cell is changed, but it doesn't seem to be working for me. Hoping someone might point out where I went wrong.

I have a grid of teams and members where I keep track of which members are complete, I'm trying to track when a 70% or more of the team is "done". I've made another grid which lists the teams, and have a formula to pull in what percentage of the team has finished. I've made a column of checkboxes that automatically check if 70% or more of the team members are done. I set an automation that inputs a date in another column if that checkbox changes to checked, but so far, I'm having no luck with it activating. If I force run the automation, the entire date column populates regardless of the checkbox column. Where am I going wrong?

Tags:
· Share on FacebookShare on Twitter

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Jared Ross

    Is it possible for you to share a screenshot of your setup for your Record Date automation?

    · Share on FacebookShare on Twitter
  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    In the Trigger area - are you selecting "When a row is changed" or are you using something else?

    In the Trigger area - are you select Run "When Triggered" i.e. run straight away.

    Like Kelly said, it would be useful to see a screen shot of the workflow that isn't working and we might be able to point out ideas of where it could be enhanced.

    Kind regards

    Debbie

    · Share on FacebookShare on Twitter
  • Jared Ross
    Jared Ross ✭✭

    Heres a couple grabs of my grid and the automation.

    Screen Shot 2022-12-15 at 11.37.24 AM.png
    Screen Shot 2022-12-15 at 11.36.51 AM.png
    Screen Shot 2022-12-15 at 12.20.55 PM.png


    · Share on FacebookShare on Twitter
  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 12/15/22

    Hi - the column formula in the team completion threshold cell - does it contain any cross sheet references at all?


    • This is help text from a Smartsheet article: "To prevent infinite approval loops, cells that contain cross-sheet formulas or cell links will not trigger automation that changes the sheet (e.g., Move row, Copy row, Lock row, etc.). To work around this, consider using time-based or recurring automated workflows"

    If you do have a cross sheet reference in your column formula - that would explain why the automation is failing.

    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Jared Ross

    Automations run when the sheet is saved. Is it possible you were watching for the automation to happen without any saving of the sheet? Is your autosave on? Also, as a safe guard against overwriting existing dates, consider adding a Condition when [AWS Completion] is not a date.

    · Share on FacebookShare on Twitter
  • Jared Ross
    Jared Ross ✭✭

    No, the "team completion threshold" column is referencing the "percentages done" column. The "percentages done" column does however reference another sheet. Could this be the issue?

    · Share on FacebookShare on Twitter
  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Yes, I believe it does effect the automation sadly.

    I have just run some tests on some sheets at my end:

    I set up a % complete column which is cell linked to another sheet.

    I set up a checkbox column to tick when the linked % complete = 100%

    I setup a automatic date workflow to capture the date that the box ticked.

    Technically the box ticking isn't linked to another sheet, but the conditions of the box being ticked is. (Same scenario as you).

    I also set another automation date workflow to capture the date that the status changed. (into a different date column). (to ensure that I am waiting long enough for the 1st scenario date to show up).


    I open the linked sheet and updated % complete to 100%. Saved and closed the linked sheet.

    Opened the main sheet with the automations in and saw the refresh occur which updated % complete then checked the box. The sheet saved automatically so at that point I changed the status manually too and saved again.

    A few moments later the manual changed trigger captured the date. The other date automation failed to work - due to the condition of the trigger being linked to a cell that is linked.


    Phew! I hope this makes sense and clarifies why your automation isn't working.

    Kind regards

    Debbie

    · Share on FacebookShare on Twitter
  • Jared Ross
    Jared Ross ✭✭
    https://community.smartsheet.com/discussion/comment/354555#Comment_354555

    I think that was it. I was saving the automation and forgot to save the sheet. Devil in the details. DOH!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!