13 Replies Latest reply on Jan 3, 2016 8:00 PM by BruceRobertson

    Relationship problem- 2 instances of key field in 1 table


      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


      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. 

        • 1. Re: Relationship problem- 2 instances of key field in 1 table

          Hello Hugh,

          I think its a relationship problem: the relationship between Cleaning and Endoscopes is established, when BOTH serials equal?! Thats impossible.

          My proposal would be: duplicate the table-occurences "Endoscope" and "Maintanance", name them "Endoscope_A" and "Endoscope_B", accordingly "Maintanance_A" and "B". Link as shown in the picture.

          Perhaps not the most elegant solution, but should be working.

          UlrichBildschirmfoto 2016-01-03 um 18.11.28.png

          • 2. Re: Relationship problem- 2 instances of key field in 1 table

            Thanks for your efforts.  I see how your solution will make the portal display, but I'm wondering how it will affect my ability to easily search through and scopes that are paired, without a lookup function.  Also,, the MAINTENANCE table is designed for me to follow the total costs and age of the scope, and the recurrences of certain problems, so this solution would make that function a bit more difficult for me.


            UPDATE:  for some reason I opened a copy of the database this morning, and I was trying the portal again, and i CAN pull through the data in a portal, but ONLY FOR SCOPE A.


            I created a separate portal that displays the IMAGE field from the ENDOSCOPES table, and with FIlter (ENDOSCOPE::SERIAL NUMBER = ENDOSCOPE CLEANING::SCOPE A) it reveals the image of scope A from the ENDOSCOPES table.  It works -- for SCOPE A and for analagous portal for SCOPE B - an image shows -- but in both portals, the IMAGE IS ALWAYS OF SCOPE A. When I change the data in SCOPEA field, the image changes in both portals, even though they are set to filter for SCOPE A and SCOPE B separately.  Somehow Filemaker has figured out a way to display the information by prioritizing the relationship and alsways selecting scope A.  But it can only do this when pulling from the ENDOSCOPES table; when it pulls info for SCOPE B from the ENDOSCOPE MAINTENANCE table, the set is empty.  screenshot here



            Hopefully this will make sense to someone....

            • 3. Re: Relationship problem- 2 instances of key field in 1 table


              Ulrich is right. You cannot have two different fields in EndoCleaning to be related to the same field in the Endo table and see results in the Maintenance table for the two different scopes. You do need to have one TO for each SCOPE to see the information for that scope.

              If you need to see information about the PAIRED scopes, then you should create a calculated field that puts those two field's data together into one and use that calc field in a relationship. Call it ScopeAB and set it to be YOURTABLE::ScopeA&YourTABLE::ScopeB. Then use this field in a relationship to the serial number, which, I'm guessing, is the two scopes put together.


              That way if you have a scope of 553, and a scope of 678, then I assume you have a serial number with 553678 as a EndoScope and have that same serial in the maintenance table. Is that correct?

              • 4. Re: Relationship problem- 2 instances of key field in 1 table

                Hi, Ullrich and Jeremy.  This has been extremely helpful.  It seems clear now that my problem is one of database design, and not just depth of knowledge in execution.  I cant get around the 2 occurrences of a related field in one table. I like the idea of the calculated field.  

                I'm going to copy the database and redesign it with a SCOPE A and SCOPE B table.  Having 2 SCOPE tables isnt a problem as the  records have few additions over years. 


                My question:  When I send scopes out for maintenance, how do I enter that information in a single table that is reflected in MAINT A and MAINT B?  Now I just create a record in one table.  HK

                • 5. Re: Relationship problem- 2 instances of key field in 1 table


                      I don't necessarily have a specific answer for you, but I do have some advice on how to think about relationships.  I believe that the relationship graph that Webcord provided is the way you need to go.  But you have to keep in mind that this may not be your ENTIRE graph.  The graph provides you starting points to establish your 'context'...


                  Now, the advice (I hope this doesn't get too basic for you):  a relationship on the graph, between two boxes, is simply a filter.  It behaves in nearly the exact same way as the filter on a Portal.  (It has other functions in the FM DB engine itself, but other than that...).  When you look through a relationship filter, you see a subset of 'related records'.


                  Now, for any filter to work you have to filter FROM something, TO a specific point of view.  FileMaker calls this FROM point of view 'context'.  It works by forcing you to start at one point on your graph - any of the boxes (aka "table occurrence") - and look through to a destination point on the graph (another box or table occurrence).  YOU set up the start point and the end point, and FileMaker will FILTER the data at the end point by every relationship that exists between those two points.  (FileMaker prevents you from establishing loops, so there is always only one route between any two points - if there is ANY route, that is.  You can have unrelated tables.)  This is how the graph that Webcord will help you:  it splits out the relationship of "Endoscopes" to the "Cleaning" table by separate fields, A and B, and gives you separate Destinations for your context, letting you see data for the two different positions.


                  So, depending on where you start you might have a different graph.  Webcord's graph is good if you start in Cleanings.  Here's one I might suggest this instead, if you start in Endscope instead - one Endoscope table and two Cleaning table occurrences.  Thusly:


                  ENDOSCOPES::Serial# = CleaningA::SerialA

                  ENDOSCOPES::Serial# = CleaningB::SerialB


                  So the graph might look like (sorry no image, just ASCII art):


                  ENDOSCOPES  --- -----  CleaningA


                             \ --- ----  CleaningB


                  You have to ask yourself a clarifying question:  1) Do I want to look at a Cleaning record, and show related A/B Endoscopes for that Cleaning? 2) Do I want to look at an Endoscope and see the different Cleaning records it is related to?  If the question is #1, start your context in the Cleaning table and create relations/filters to Endoscopes.  If the question is #2, then start in Endoscopes and make relations to Cleanings.


                  And when I said that this might only be part of your graph, you can create different filters/relations depending on what you want to achieve in the UI.  This relationship graph is NOT purely a Data Model, it is really more of a functional model with some data model thrown in.  If you want to see Cleaning Data for Endoscope A, create a table occurrence ("T.O." or "TO").  If you want to see data for EndoB, create another TO.  If you want to see maintenance data for A, create another TO.  Each of these TOs would start somewhere and look through a filter/relationship you create in order to show the data you want to see in that part of the layout or UI.  For each UI feature you create you may create a new TO.  Now, don't go hog wild; with careful planning and understanding of how FM works, you can be efficient with these TOs and get a few TOs to do lots of work for you.


                  Now, specific to your problem:  Your layout has to be based on a table occurrence.  This is the starting point of your 'context'.  So when you define a portal and you specify which related table to show records from, that defines your end point.  Answering my question #1 or #2 above will help you decide what to base your layout on.  What is the portal based on?  And what is your graph like between those two points?  Webcord made a good point:  if, from the first graph you showed, you start in Endoscopes and look to Cleaning, unless the same serial number is in both A & B fields of Cleaning, you won't see ANY related records.


                  There's one trick to FileMaker that could allow you to do some things without portals:  if you start at a Cleaning, say, and you want to see Endoscope info (e.g. the image), you would create the 2 Endoscope TOs mentioned above.  Then, on your layout in the Cleaning record, you would just put the field from SlotA=EndoA TO on your record in the Cleaning layout, and a field from SlotB=EndoB on the same Cleaning record.  FileMaker will fill in the data for the FIRST related record.  If you define those relationships correctly (the way Webcord suggested) there will only ever be ONE related Endoscope record via each relationship, and so you will get different data in each field on the Cleaning layout.

                  • 6. Re: Relationship problem- 2 instances of key field in 1 table


                       After posting, and reading the replies that came through while I was writing up my thesis statement, I wanted to clarify something:  You can create multiple Table Occurrences (TOs) from ONE table.  You don't NEED to make a separate actual Table for the two scopes.  Creating two tables is actually probably a bad idea, for the reason you mentioned:  how do you enter data that applies to one scope?


                    No, I would say leave 1 Endoscope table, 1 Cleaning table, 1 Maintenance table.  Putting two fields for IDs in the Cleaning table isn't a problem.  You just need to split it out along separate Table Occurrence lines with different relationships to Endoscopes.


                    Send me a private message if you want to have a more interactive chat (text or voice), or even a screen share.

                    • 7. Re: Relationship problem- 2 instances of key field in 1 table

                      Seems to me the design should just follow standard normalization practice, and add a join table.

                      Fields ScopeA and ScopeB would be eliminated from the Cleaning table.

                      The join table, perhaps called cleaningSlots, would have fields:

                      fk_CleaningCycle; Slot; ScopeID; and recordID.

                      • 8. Re: Relationship problem- 2 instances of key field in 1 table

                        Bruce, do you mean that the join table would have foreign key of Cleaning Cycle - related directly to ENDOSCOPE CLEANING::Cycle number, and have SCOPEID related to ENDOSCOPES::SERIAL NUMBER and to ENDOSCOPE MAINTENANCE::SERIAL NUMBER? 

                        • 10. Re: Relationship problem- 2 instances of key field in 1 table

                          OK - you have all done quite a bit of work on this for me, and I appreciate it.  I've been doing my work, learning about join tables, and the differences between tables and table occurrences.  I thought that relationship graph was more literal than it really is. 


                          Ullrich, your solution worked very nicely.  My portals work perfectly.  Justin, thank you very much for the long response; I did create a SCOPEA/SCOPEB table for searches - it works very well.  Only problem was syntax - I originally wanted a separator between the 2 serial numbers ( 553-750) to make it more clear what the two scopes were; (visually clearer than 553750) and I was also worried that given the FIND tools I might miss  or catch cycles containing serial numbers.  Turns out the hyphen makes it all 1 word, and the searches were wrong.  SO I went with (=ScopeA&" "&ScopeB) to give me 553 750 which is easily searchable. 

                          Bruce I'm learning about the join table, and I will futz around with it on another copy of the database - for now, I have a working database that fulfills all my needs.  Thanks to All. 

                          • 11. Re: Relationship problem- 2 instances of key field in 1 table

                            NOt a good idea. You cannot build efficient reports. That's what real normalization accomplishes.

                            • 12. Re: Relationship problem- 2 instances of key field in 1 table

                              Will post back after I read a bit on normalization practice in database design.  My reporting needs are not as important as creating an audit trail that is dependable.  What makes   reporting 'efficient'?

                              • 13. Re: Relationship problem- 2 instances of key field in 1 table

                                endoscope graph.pngVery basic example attached.


                                Less complicated graph

                                No extraneous fields required

                                Easier to report by slot

                                Report cleanings or maintenance by serial number