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