1 Reply Latest reply on Sep 15, 2015 8:02 AM by Mike_Mitchell

    Comparison across a One to Many Relationship

    MattLeach

      I have 2 tables with a one to many relationship.

       

      Table 1 - Clients

      Table 2 - Notes

      Relationship - Clients::_pk_ClientID = Notes::_fk_ClientID

       

      In the Clients table there is a field called Revised (date field) which is used to store the current revised date of the software the client is running.

       

      The notes table also has a field called revised (also a date field) where employees document the current revised date of the software the client is running on each call with the client when they enter a note. I have a script setup that compares the two upon saving a note and if the revised in the note is greater than the revised in the client record, a prompt appears asking if they want to update it.

       

      Apparently I have been told this script randomly does not update the revised in the client table - I have tested and doesn't appear to be the case, I believe they are just clicking no to update and not yes but I want to see how often this occurs.

       

      What I would like to do is some type of scripted comparison that will find any record where the latest note record has a revised that is greater than the revised in the client record. Seeing as there are many note records to one client, how can I isolate just the more recent note.

       

      Would I simply create another TO and relate it with those two fields like

       

      Clients::_pk_ClientID = Notes::_fk_ClientID AND

      Clients::Revised < Notes::Revised

       

      Message was edited by: Matt Leach - added possible solution

        • 1. Re: Comparison across a One to Many Relationship
          Mike_Mitchell

          One simple way to do this is to use a purpose-specific relationship. Just create a new relationship that matches the existing one, but add the two date fields as an additional predicate (Clients::Revised < Notes::Revised). A portal will reveal any records that match the criteria.

           

          If you don't want to do that, you can run a script from the Notes table and perform a Find where the criteria are the same (Clients::Revised < Notes::Revised). That will return a found set of Notes records that correspond to the mismatch.

           

          HTH

           

          Mike