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.
Answers
-
What is the formula exactly?
-
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?
-
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.
-
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?
-
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?
-
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.
-
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.
-
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
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
