I have three tables with the following relationships
LOCATIONS one to many DETAIL
SYSTEMS one to many DETAIL
DETAIL contains drop downs for the related location and the related system with additional Start and End Dates and a formula text field call Status. Status will equal "current" or "inactive". Only one of the LOCATION's related child records will = "current".
My issue is that I need to create a reportable field in the LOCATIONS Table that that pulls the name of the related system from the DETAIL record where the status = "current"
I tried creating a summary field on the Locations table that is the max of the record id where the status = current. My problem is that this only displays the record ID number and I need the actual word or name of the system to use in various reports on the LOCATIONS table.
Any suggestions? Thank you,