automation for adding date?

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?

Find more posts tagged with

Formulas

Automations

Conditional Formatting

Accepted answers

All comments

Kelly Moore

Hey @Jared Ross

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

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

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

Debbie Sawyer

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.

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.

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?

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

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.