If more than 2 weeks past due, change status

[Rachael_Castela]

Hey there!

I have a Status Report table built to allow for all updates about each step to be created.

In the Steps table, I need to create a field / formula that says "if the most recent update for each step is more than 2 weeks from today’s date (in the past), change status to “NEEDS UPDATE”. The “Formula” field in Status reports shows “true” if it’s the most recent update for the step, which is the row I’d want to reference as my most recent update.

Thanks in advance for your help!

[Jeremy_Oglesby]

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):

[Rachael_Castela]

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!!