Find Records Automation Not Recognizing Field as a Date

Topic Labels: Automations
849 5
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

I have records with a %Completed field. I previously created an automation that updates a progress status field based on milestone dates applied to that record. This automation is triggered when the %Completed field is updated and it has been working wonderfully. There are 9 separate conditions with three progress status options. The first condition is shown in the screenshot below. The other 8 are just below out of frame.

Screenshot 2023-08-23 at 8.26.53 AM.png

  1. If today is between Milestone 1 and Milestone 2 dates and %Completed is > 25% = On Track
  2. If between M1 and M2 and % >= 12 and < 25 = Behind Schedule
  3. If between M1 and M2 and % < 12 = At Risk.
  4. If between M2 and M3 and % >= 50 = On Track
  5. If between M2 and M3 and % >= 35 and < 50 = Behind Schedule
  6. If between M2 and M3 and % < 35 =At Risk
  7. If between M3 and Deadline and % >= 75 = On Track
  8. If between M3 and Deadline and % >= 60 and < 75 = Behind Schedule
  9. If between M3 and Deadline and % < 60 = At Risk

The %Completed is calculated based on tasks from another Table that are associated with those records. Due to the frequency of how often tasks are created/completed, the %Completed field updates often so this automation is basically running constantly. I glanced at my automation allotment (pro plan) and realized I will hit my 50,000 in a few more months if I don't fix this. This doesn't need to be done in real-time, so I wanted to recreate the automation using the "scheduled time" trigger to check all records once a day which from what I understand will only count as 1 automation run each day regardless of if or how many records the automation ends up updating.

I added the Scheduled Time trigger and then a Find Records block that locates all of the records that could potentially need their progress status updated (weeds out completed and upcoming records). When I try to create the conditions for the criteria listed above, I'm not able to use the "one or before" or "after" type options I use in my current solution.

Screenshot 2023-08-23 at 8.27.15 AM.png

The Repeating Groups option is more appropriate here, but I can only use one per automation apparently and I'm having the same issue with not providing those specific date options.

The "Milestone" fields are formula fields formatted as dates (if that's relevant). 

My best solution moving forward is to create 9 separate automation - one for each of the criteria listed above - using the "when a record matches conditions" trigger. This will accomplish the same thing but from my understanding, the automation will ONLY run when a record passes out of one "progress status" and into another (e.g., meeting the criteria for moving out of On Track and into Behind Schedule) as opposed to the current system which runs an automation every single time the %Completed field changes whether it affects the progress status or not. If I'm understanding what counts as an automation, this should drastically reduce the number of automations I'm being hit for. Is this a correct understanding?

Is there a more sensible way to do this (without running my automation count through the roof)?

5 Replies 5

Is there a reason that you're using an automation for this, instead of just creating a formula field called "% Completed"?

11 - Venus
11 - Venus

How about changing it to a conditional expression by Formula field instead of automation?
It looks possible since it seems to be complete in one record.

I'm not sure I follow. I already have a formula field called %Completed. Like I said, it calculates the complete vs. incomplete tasks from another Table that are associated with the records (projects) on the table I'm discussing in this post.

The progress field is a single select field. We also need to have the ability to manually change the status as well. I guess I could technically add several more fields to trigger those cases where the status changes based on other things, but that would get pretty complicated. Additionally, we use the color coding of the single select progress field to color the grid and timeline based on the progress status. We'd loose that as well if I converted the progress field to a formula.

6 - Interface Innovator
6 - Interface Innovator

To help clarify, the two things I'm asking of the community are:

  1. Is my understanding of when we are "charged" for an automation run correct? 
  2. What is the way to accomplish my goal with the least amount of runs possible?

I got the email today about my pro plan moving to a "team" plan. It mentions that I now have 25,000 automation runs per month. This is well over what I've been using, so I think this all may be a non-issue now. It might not have been an issue in the first place if the 50,000 runs I had before was per month - I thought it was per year. That said, I'm not sure how I racked up over 7,000 runs this month if that's the metric it is showing me on my account so I'd still like to have a better understanding of what classifies as an automation "run".