Field needed that displays most recent status based on date field

[ConnorTomasko]

(Similar to this post though with an additional layer)

I have a table with Interactions. There's a Status single-select field and a Timestamp field (since this data is being imported from elsewhere) that shows when the data was captured in the original system.

There's a second table named Opportunities. All the interactions with one individual are linked to their respective Opportunity record. I need a field in the Opportunities table that reflects the Status of the linked Interaction record with the most recent / max Timestamp.

The solution to the post I linked above pulls the max date, which would work if all I needed was the most recent Timestamp date pulled into the Opportunity record. However I need to know the most recent Status based on the Timestamp.

Any thoughts? I may be missing a simple solution here. @Ben_Young1 ?

[Ben_Young1]

There are three ways to do this.

The easiest and most straightforward way to do this is to ditch the rollup field. In its place, create a lookup field of your status single-select. Configure that lookup field to only return the last item in the array.

Here's an example:

 

I personally don't use lookup fields, but this is by far the easiest way to implement such a feature without having to resort to more elaborate solutions.

[ConnorTomasko]

I've tried this, but the "Last" item it displays appears to be the most recently created record, not the record with the most recent Timestamp (a date field that is unrelated to the Record Created or Modified fields, imported with the rest of the Interaction data from another location), which is the date that is relevant to the Status -- not the record created date.

[Ben_Young1 · Accepted solution]

As a note for anyone in the future that might find it useful, the order of the records in a lookup field is not based on anything other than the order in which they were linked.

With the clarifying information you provided, we can make a few tweaks to our solution.
While my personal recommendation would be to script this operation to avoid cluttered fields, this approach will get you generally where you need to go.

First, I've created a new date field on the interaction object: Secondary Date. This will serve as the date field we want to reference for how to isolate the interaction record we want to look at.

Next, we need to find a way to isolate which date value is the most recent as precisely as possible. To do this, we need to get the unix value of our Secondary Date field. I've done this by creating a new formula field called Secondary Unix, for lack of a better name.

IF(
  {Secondary Date},
  VALUE(
    DATETIME_FORMAT(
      {Secondary Date},
      'x'
    )
  )
)

Now, we'll return to the opportunities table and create a new rollup field. This field should reference the new Secondary Unix value we created on the interaction object. 

You'll end up with something that looks like this:

From there, hop back to your interactions table. Here, we're going to create a rollup field I've called Recent Match. It references the Recent Timestamp rollup from the opportunity object.

We'll use the following formula:

IF(
  AND(
    {Secondary Unix},
    {Secondary Date}
  ),
  IF(
    {Secondary Unix} = MAX(values),
    'Match'
  )
)

You should have something that looks like this:

Now, we'll create our final field. This field will return the status value that we're looking for.

 

Naturally, you can now just hide the Recent Timestamp, Secondary Unix, and Recent Match fields. A scripted approach would resolve the need for these fields, but this works fine for a no-code approach.

[ScottWorld]

Hi @ConnorTomasko,

I demonstrate how to do what you're looking to do on this episode of the BuiltOnAir podcast.

[ConnorTomasko]

Thank you - this worked!