Trying to avoid a circular argument problem

Alpha Chucky

I have an automation that looks for today's date under [Next Run Date]. If its today, the automation does its thing. Once the Run is complete, I now need the Last Run Date to be today's date so it pushes the [Next Run Date] into a future date.

The [Next Run Date] is determined by adding the date under [Last Run Date] and [Interval Days]. So it looks like this

See image attached

*[Next Run Date] is set to WORKDAYS. that's why the dates are not exactly 7 or 14 days apart.

How can I get the Last Run Days to update to the date that appears under [Next Run Date]

Does anyone have any creative solutions?
Spoiler alert Chatgpt just gave up trying. LOL!

thanks!!!

Michelle Choate 2

Can you use "Record a Date" to do this? So when the automation you currently have set runs, it checks a box or whatever, and then whenever that box is checked then it records a date in the "Last Run Date" column. Then, 24 hours after last run date, the checkbox gets cleared through a "Clear a Cell" automation or similar?

Alpha Chucky

Winna, Winna, Chicken Dinna! That worked. ….So I'll spell it out so maybe someone else can gain.

Start with building two helper columns "Run Completed" which is a Checkbox field and "24 Hours Have Past". In the 24hrs have past column populate with =IF(TODAY() > [Next Run Date]@row, "Yes", "No").

In the Trigger Date column, populate with =IF([Next Run Date]@row = TODAY(), "Run", "")
Trigger: When rows are added or changed ⇒ When Tigger Date changes to Run
Condition: When Trigger Date is equal to Run
Action: Copy rows
2nd Action: Change cell value in: Run Completed to Checked


Setup this new automation: "Record a Date"
Trigger: When the "Run Completed" column is equal to checked.

• Action: Use the "Record a Date" Record a date in [Last Run Date].
  (this does the actual update in the Record a Date column)
  
  Last Automation:

• Trigger: When rows are added or changed. When "24 Hours Have Past changes to "Yes".
• Action: Change cell value in Run Completed to Unchecked.
  
  Thanks Michelle (Again) :-)

Michelle Choate 2

Can you use "Record a Date" to do this? So when the automation you currently have set runs, it checks a box or whatever, and then whenever that box is checked then it records a date in the "Last Run Date" column. Then, 24 hours after last run date, the checkbox gets cleared through a "Clear a Cell" automation or similar?

Alpha Chucky

Hey friend! Let me give it a try.

Alpha Chucky

Winna, Winna, Chicken Dinna! That worked. ….So I'll spell it out so maybe someone else can gain.

Start with building two helper columns "Run Completed" which is a Checkbox field and "24 Hours Have Past". In the 24hrs have past column populate with =IF(TODAY() > [Next Run Date]@row, "Yes", "No").

In the Trigger Date column, populate with =IF([Next Run Date]@row = TODAY(), "Run", "")
Trigger: When rows are added or changed ⇒ When Tigger Date changes to Run
Condition: When Trigger Date is equal to Run
Action: Copy rows
2nd Action: Change cell value in: Run Completed to Checked


Setup this new automation: "Record a Date"
Trigger: When the "Run Completed" column is equal to checked.

• Action: Use the "Record a Date" Record a date in [Last Run Date].
  (this does the actual update in the Record a Date column)
  
  Last Automation:

• Trigger: When rows are added or changed. When "24 Hours Have Past changes to "Yes".
• Action: Change cell value in Run Completed to Unchecked.
  
  Thanks Michelle (Again) :-)