AnsweredAssumed Answered

Comparison across a One to Many Relationship

Question asked by MattLeach on Sep 15, 2015
Latest reply on Sep 15, 2015 by Mike_Mitchell

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

Outcomes