Conditional Formula to Update Status from Form

[Jason_Friedlan1]

Hey Everyone:

I have a form that takes multiple inputs such as Date/ Status/ Update String. They all link to another table where I can see the most recent update per item. So as my team updates items from week to week I have a view of the most recent.

The issue is that not each input updates from week to week. So I want to use a conditional formula in my form table that will see if the input Update Status is blank and if it is then take the previous update that was not blank.

Having a hard time figuring this one out.

I hate that forms if left blank blow out a record that was there. I should be able to conditionally control those by saying if no response keep previous.

[Justin_Barrett]

This is doable with a little back-and-forth between your tables. You didn’t mention table names, so I’ll refer to the table where the form data comes in as [Updates], and the linked table where the master item list lives as [Items].

Here’s my [Updates] table with a handful of random items. Most have notes, but the second one for Item A does not:

On the [Items] table, add a rollup field named {Latest Update Date}. Using the field containing the incoming links from the [Updates] table, the field to roll up will be the {Date} field. Add a condition to the rollup to only pick records where the {Update Status} field is not empty, and use MAX(values) for the aggregation formula. That should give you the date of the most recent non-blank update on a per item basis.

In the [Updates] table, add a lookup field named {Latest Update Date} that looks up the value from that rollup field.

Add a formula field named {Latest Match} with the following formula:

AND({Latest Update Date} = Date, {Update Status})

That will output a 1 where the dates match and the status isn’t blank (in the unlikely case that two status updates are added for the same item on the same date). Now it looks like this:

Back on the [Items] table, roll up the {Update Status} values, using a condition to only pull the status where that formula equals 1.

That will leave you with the latest non-blank status for the item.

[Jason_Friedlan1]

Justin_Barrett:

AND({Latest Update Date} = Date, {Update Status})

YOU SAVED MY LIFE! This is awesome!