Issues with "Record a Date" Automation
Hi all,
Any idea why this automation isn't recording a date? We have a formula that's designed to return a value of "Reorder" whenever the inventory for that products dips below a certain threshold. The formula is working, but it's not being recognized as a trigger with the attached automation. Any advice would be much appreciated. Thanks!
Best Answer
-
Yes. References to another sheet will make it so that you need to use a time based trigger instead of an action based trigger. This cuts down on creating circular issues. You will need to set your trigger to be date based, set it as daily, then adjust the timing to be at the end of the day.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
What is the formula exactly?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks for the quick reply Paul! Here's the formula:
=IF(OR([Starting Quantity]@row = "", [Reorder Trigger]@row = ""), "", IF(([Quantity On-Hand]@row + [Quantity On Order]@row) < [Reorder Trigger]@row, "Reorder", ""))
-
What happens if you adjust your trigger to run when it changes to that specific status and remove the condition?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I can try and see if that helps, but I was trying to avoid that so I'd be able to run the automation manually without affecting the other rows on the sheet, since the "run now" function disregards the trigger but still obeys the conditions.
-
Then try leaving the condition in. Either way, it may help to adjust the trigger to be when it changes to that specific value instead fo any value.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ah ok, I'll give that a try and keep an eye on it. Thanks!
-
Sorry @Paul Newcome, still no luck on this. I changed the trigger to when "Reorder" shows up in that column instead of any value, and I left the condition the same, but the sheet still isn't recording a date. When I run the automation workflow manually it records a date right away. Any ideas?
-
Do any of the cells referenced in the formula populating that column contain cell links to other sheets or cross sheet references?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Nope, all the cell references in the are within the sheet itself!
-
And [Starting Quantity], [Reorder Trigger], etc…. None of them contain cell links or cross sheet references?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Actually, they do. While the formula only refers to columns on the same sheet, a few of those columns refer to other sheets through SUM and SUMIF formulas. Here's a breakdown:
[Starting Quantity] = No links/cell references
[Reorder Trigger] = Refers to 3 other columns on the same sheet:
- [Leadtime (Weeks)] = No links/cell references
- [Avg Shipped Qty/Month] = Cross-sheet cell reference (SUMIF formula)
- [Safety Stock] = Refers to [Avg Shipped Qty/Month] which has a cross-sheet cell reference[Quantity On-Hand] = Refers to 3 other columns on the same sheet:
- [Starting Quantity] = No links/cell references
- [Quantity Shipped] = Contains 2 cross-sheet cell references (SUM and SUMIF formulas)
- [Inventory Received] = Cross-sheet cell reference (SUM formula)[Quantity On Order] = No links/cell references
Do you think this is affecting the automation's ability to run properly? At the moment, the trigger is set up to fire when rows are added or changed, whenever the "Reorder Notification for Weekly Report (Formula)" column changes to a value of "Reorder". Does it matter whether the "Reorder" value appears due to a formula that includes a cross-sheet cell reference?
-
Yes. References to another sheet will make it so that you need to use a time based trigger instead of an action based trigger. This cuts down on creating circular issues. You will need to set your trigger to be date based, set it as daily, then adjust the timing to be at the end of the day.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks for clarifying, but if I change the trigger to run daily, would it record a new date in that column every day? The main reason I created the workflow was to track how many days it's been since the "reorder" value originally appeared, that way we can send time-based reminders if the warehouse manager hasn't reordered a product after a certain number of days. If we have the workflow running every day, I'm afraid that date would be overwritten every day.
-
You would include another condition to only output on rows where the date field is blank.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ah ok, makes sense! I'll give it a try and let you know how it goes. Much appreciated!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives