AnsweredAssumed Answered

Relationship problem- 2 instances of key field in 1 table

Question asked by HughKendall on Jan 3, 2016
Latest reply on Jan 3, 2016 by BruceRobertson

First, I apologize for my newbie-ness; I am an experienced Novice at Filemaker, but I can't write code, and I can create straitforward scripts, but not with loops, etc.  Nested 'if' statements is my limit.  I have looked through the forums and I can't find a thread that addresses my problem, or that lay it out in a way I can understand.

 

I have a simple database with 3 tables.  The purpose is to track the sterilization cycles of medical endoscopes, and their repair and maintenance.The machine that sterilizes the endoscopes can accommodate 2 endoscopes per cycle. Each time the machine is run, there is an endoscope in Slot A and another in Slot B.  I want to track both scopes in each cycle, so I can say not only how and when each scope was sterilized, but if there were an infection, be able to track what OTHER scope was in the machine with the suspect scope.

 

My first table is the ENDOSCOPES table, with fields for Scope serial number, series, purchase date and image.  The key field for relationships is Serial Number.  The attributes of the fields are as follows:

Endoscopes table screenshot.jpg

The next table is ENDOSCOPE CLEANING which has fields for the parameters of each cycle like temperature, timestamp, etc., and also a "SCOPE A" field, and a "SCOPE B" field.  All records in this table will have entries in the SCOPE A and SCOPE B field that are only populated from the ENDOSCOPES table, SCOPE SERIAL NUMBER field.(i.e. ENDOSCOPES::SCOPE SERIAL NUMBER).  The attributes of the fields in this table , and the relationship I defined, are below:

EndoscopeCleaningTable screenshot.jpg

fmprelationships.jpg

The last table is ENDOSCOPE MAINTENANCE, with fields including Scope Serial number, and the parameters of each maintenance encounter (date, problem, findings, party replaced, etc.)  The fields are as follows:

Endoscope Maintenance Table screenshot.jpg

Thanks for reading this far.  MY PROBLEM:  I want a portal on my ENDOSCOPE CLEANING table that pulls data from the ENDOSCOPE MAINTENANCE table for each scope in SCOPE A field, and SCOPE B field.  (i.e. when I enter #553 in SCOPE A field, the details of scope #553's maintenance appear in that portal).  I want the same thing to happen for the scope I enter in SCOPE B slot.  The portals are empty, and I suspect it has to do with the relationship I defined.  I have tried deleting the relationships between SCOPE A/SCOPE B and ENDOSCOPES, leaving only the ENDOSCOPES --ENDOSCOPE MAINTENANCE tables related by a single key field (Scope serial number) and on the ENDOSCOPES table I can pull through the MAINTENANCE data in a portal.  I can't pull through the data into the ENDOSCOPE CLEANING table.  I've fiddled with the FILTER option in the Portal setup, (using GetAsText, even trying text manipulation such as Right(text;3) to pull the last 3 numbers of each scope serial numbers) but nothing.  I'm convinced no data shows up because the data set is empty, and that it is not seemingly a filter problem, its a relationship problem.  Sorry for all the data, but if I want a solution, it seems best.  Any help appreciated. 

Outcomes