If more than 2 weeks past due, change status

Hi @Rachael_Castelaz,

I think you’ll need to use a Rollup field, rather than a Formula field in your Steps table for this.

Since you have that “This is the most Recent Status Report” flag formula (which, I’m curious how you are doing that in your formula…), you can use that flag to filter which linked records will be included in the Rollup formula back in your Steps table.

So let’s say you make your Rollup field in the Steps table called {Needs Update}, and you point it at the Linked Record field that is linking to the Status reports table, and rollup the {Date Added} field. Now set it to filter for records where {Formula} = true (as you have it named currently). This should return only one record into the Rollup, if your Formula is actually working to show true only for the most recent.

Now, in the Rollup formula, you can operate on that one record, and return an ad-hoc value, rather than some value from the record(s) in the rollup (many people don’t realize this). Use a formula that looks something like this:

IF(
   IS_BEFORE(
      values,
      DATEADD(TODAY(), -2, 'weeks')
   ),
   "NEEDS UPDATE"
)

So your final rollup field should look something like this (and yes, I confirmed this is working):

I really appreciate your help!!

To answer your first question for how the formula field is working… (Essentially I wanted to see only the most recent update in my steps field. So I’m using:
In my Status reports table:

And in my steps table:

I believe the formula is working, however I’m realizing I need another status. If NO update is found (nothing in the status report for that step), it should read “Status Report Needed”. Right now it’s showing “#Error” instead.

Thanks again!!