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

    Calculation based on most recent record by individual basis.

    ezeitgeist

      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.
          Johan Hedman

          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.
            ezeitgeist

            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.
              philmodjunk

              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.
                ezeitgeist

                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.