Issues with "Record a Date" Automation

jordan.navarro23

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!

Paul Newcome

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.

Paul Newcome

What is the formula exactly?

jordan.navarro23

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", ""))

Paul Newcome

What happens if you adjust your trigger to run when it changes to that specific status and remove the condition?

jordan.navarro23

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.

Paul Newcome

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.

jordan.navarro23

Ah ok, I'll give that a try and keep an eye on it. Thanks!

jordan.navarro23

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?

Paul Newcome

Do any of the cells referenced in the formula populating that column contain cell links to other sheets or cross sheet references?

jordan.navarro23

https://community.smartsheet.com/discussion/comment/442946#Comment_442946

Nope, all the cell references in the are within the sheet itself!

Paul Newcome

And [Starting Quantity], [Reorder Trigger], etc…. None of them contain cell links or cross sheet references?

jordan.navarro23

https://community.smartsheet.com/discussion/comment/443161#Comment_443161

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?

Paul Newcome

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.

jordan.navarro23

https://community.smartsheet.com/discussion/comment/443225#Comment_443225

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.

Paul Newcome

You would include another condition to only output on rows where the date field is blank.

jordan.navarro23

https://community.smartsheet.com/discussion/comment/443280#Comment_443280

Ah ok, makes sense! I'll give it a try and let you know how it goes. Much appreciated!