4 Replies Latest reply on Aug 26, 2016 2:54 PM by ezeitgeist

# Calculation based on most recent record by individual basis.

I have a Meetings table, and a Contacts table. I have created a calculation that changes the button in a portal orange if the meeting is over 30 days old, so I know to reach out to the person. That said, I have multiple meetings with Contacts. Is there a way for the button color change to only look at the most recent meeting date with EACH Contact?

Instead of everything past 30 days old being orange, I want just to target the most recent meeting with each individual (because of course if I have 4 meetings and the last one is over 30 days the others will be over 30 days as well, but I don't want to see that).

• ###### 1. Re: Calculation based on most recent record by individual basis.

You then have to create another relationship from your Meetings table to same table with Contact ID´s on both sides and then add Date.

Your relateionship graph should look like this

Contacts-<Meetings-<MeetingsSameDay (based on Meetings)

When you are in Contacts table you looking at the portal you can do Conditional formatting based on MeetingsSameDay

• ###### 2. Re: Calculation based on most recent record by individual basis.

I already have a Conditional formatting trigger:

Connections::Date < (Get ( CurrentDate ) - 30)

To turn the button Orange when Connection (AKA "Meeting") date is over 30 days old.

SINCE all Connections with the same contact will have the same Contact_ID, is there a way to add a Conditional format, or the like, that compares the Dates of all Connections with the SAME Contact_ID and then only apply the above Conditional formatting to the most recent? Seems like something that should be doable. Like a "If" of Date1 being > than Date2?

OR, because the Connection_IDs go up numerically, is there a way to Conditional format (or similar) that compares the Connections of SAME Contact_ID and only applies the above Conditional formatting to the Connection_ID of highest value? (ie. If I have one Connection from a week ago and it has Connection_ID = 8, and then make one today the new Connection would have something like Connection_ID = 25, but both would have the same Contact_ID. I'd only want the Conditional formatting of color change to address the Connection_ID of greatest value.)

• ###### 3. Re: Calculation based on most recent record by individual basis.

What Johan is suggesting is to modify your calculation to refer to the same data but from the related table occurrence:

MeetingsSameDay::Date < (Get ( CurrentDate ) - 30)

We are both assuming that "contacts" refers to the records in your portal. MeetingsSameDay would be a Table Occurrence (Box in your relationships graph) that refers to the same table as contacts.

• ###### 4. Re: Calculation based on most recent record by individual basis.

Sorry for my newbie-ness in this, trying to follow.

I am three tables:

Users

Connections (AKA Meetings)

Contacts

A User is linked to Connections via User_ID, and Contacts are linked to Connections via Contacts_ID. Connections does have a date field. Where I get confused is on the "MeetingSameDay::Date" reference. I think I understand that that is a 2nd table occurrence of Connections. But then I get lost on to link up a hierarchical relationship.

Thank you for the help, I apologize for not being able to fully follow from the start.