Display Data from Second Related Record

[datalink]

In a system that tracks the service records for automobiles there are two tables (Automobile and ServiceVisit).  The operators of the system make notes about an automobile’s service visits each time a car is brought in for service.

The layout that the operator is working on while making visit notes is contextually based on ServiceVisits.  I’d like to be able to display the visit notes from the previous visit on the screen as the operator is making notes about the current visit.  I’d like to show in one glance what was done last time so the notes on the current visit are more thoughtful.

I guess I need a way to display the value of a field from the previous related record.  I’ve tried using a GetNthRecord construction on the Automobile records, but since operators will occasionally look at old service records and need to view what had been done on the previous visit building this on the Automobile level isn’t quite what I need…I think.

I’ve attached a prototype of what I’ve been playing with.  I’m not sure how helpful the prototype is, but thanks to the community for any suggestions or Comment.

SecondRecordPrototype.fmp12.zip

[comment]

You need a self-join relationship of the ServiceVisits table, based on these predicates:

 

ServiceVisits::AutomobileID = ServiceVisits 2::AutomobileID

AND

ServiceVisits::ServiceVisitID < ServiceVisits 2::ServiceVisitID

 

In the relationship definition, sort the records on the ServiceVisits 2 side by ServiceVisitID (or by date), descending.  That will make the immediately preceding visit the first related record - so you can just place any field from ServiceVisits 2 on the layout of ServiceVisits and it will display the data from the previous visit.

[bruceR]

Here's one  approach, similar to Comment's description.

SecondRecordPrototypeMODBFR.zip

[datalink]

Hi Bruce...I seem to still be having trouble getting this to work properly.  The link to your file seems to have expired.  I wonder if I could get you to post it again?

Thanks so much!

[bruceR]

Sorry, don't have it.

[Lee Smith]

Hi Bruce...I seem to still be having trouble getting this to work properly.  The link to your file seems to have expired.  I wonder if I could get you to post it again?

Thanks so much!

​I reported this earlier today, see here http://fmforums.com/forums/topic/95785-broken-links-and-lost-files/#comment-440409

Edited May 20, 2015 by Lee Smith

[LaRetta]

So Datalink, what happened when you tried Comment's suggestion?  Are you structured a bit differently so it did not work and if so, how are you structured?

[Jeff M]

I'm assuming you got what you need. If not, let me know, I can help.