13 Replies Latest reply on Jan 13, 2014 8:38 AM by prtd

    Relationship between two portals on same layout not working as expected!

    prtd

      I have been battling with this problem for nearly a week and still don't understand why it doesn't work! Simply, I have three tables (a 'patients' table, 'treatment blocks' table, and 'sessions' table). The 'patients' table is related to the 'treatment blocks' table, and the 'treatment blocks table' is related to the 'sessions' table (so the number of times a patient is seen as part of a specific treatment). See table configuration below (the 'sessions' table in this instance is call 'MYMOP Records'. I then have a simple 'patient details' layout which as well as showing a patient's details (name, address and so forth) includes two portal tabs - one to the 'treatment block' data for the patient in question, the second to the 'session data' for a particular treatment block the patient is receiving.

       

      The 'treatment block' portal, as expected, lists the treatment blocks relevant to the patient selected. So this might be treatment for a bad back, say in 2012, treatment for a skin problem at another time in 2013 - so far so good? Selecting the whole of a particular row of the 'treatment block' portal, I would have expected would bring up the relevant sessions related to the selected treatment block (say for when the patient was being treated for a bad back), when veiwed via the 'session data' portal. Not so! Despite, the treatment block and session data being related (and the records being confirmed as so through 'Data Viewer'), all the sessions for the patient are listed in the 'session' portal and not just those related to the treatment block selected. Maybe using the portals in this way is not supported? It's as if the first relationship is honoured by not the second. Any guidance on how I can get it working properly would be much appreciated! - Peter Davies.

      Table.jpg

        • 1. Re: Relationship between two portals on same layout not working as expected!
          reelsteve

          Yeah - this is a nightmare hump to get over, one I remember well.  The basic problem is that the first portal you are getting the result you want because you are operating from just one layout, and thus one table, hop away.  when you are clicking on the portal and expecting to see the downstream light up correctly, you are now asking a table 2 hops away to have a connection with you selections.

          Thats the  breakdown.  perhaps it helps to think of the portals you are clicking on as dead, lifeless asteroids which have no functionality beyond the scripting you attach to them and that does nothing for the context.  IMHO, and this is very subjective to development styles and not the normal approach taken in filemaker, switching to the session model was a godSend.

          A quick overview - you would create a VIEWER table which would be there to provide scaffolding for your UI and serve up keys to data tables.  it would have no data and generally only one record.  Attached to the viewer table are your data tables, generally one to serve an all record feed and the other to serve a "selected record" feed.  I can pull up a demo later but if you poke around, I think you will find "session model" solves the issue of locking yourself to context and its unwanted side effects.

           

          hth

          steve

          1 of 1 people found this helpful
          • 2. Re: Relationship between two portals on same layout not working as expected!
            prtd

            Dear Steve - thank you for taking the time to answer my query. Much appreciated. As I thought then, what I'm attempting to do ain't possible! I'm new to programming with Filemaker, though did do some using Fox-Pro many moons ago! The analogy of the Portals simply being distant asteroids was very helpful. A pity FM don't highlight this issue in their guidance/reference documents.

             

            I used the 'Personel' example provided with FM13 as a starter, hence thought this particular format might work. You mention creating a VIEWER table and a demo that you could send me/point me towards. Is this on the Developer site or elsewherse? Again any good examples of how this kind of problem might be solved that you could either send me or point me towards would be very helpful.

             

            Regards,

             

            Peter

            • 3. Re: Relationship between two portals on same layout not working as expected!
              ch0c0halic

              Your basic problem is misunderstanding how relationships work. A relationship is the link between two tables. **The link always connects to all related records.** Your selection in the portal of a specific Treatment record in no way tells FMP that you want to change this behavior.

               

              To view ONLY the Treatment Sessions associated with a Single Treatment you must ‘instruct’ FMP that you want a different view of the data.

               

              One way to do this is to filter the Sessions portal. Select the Sessions portal and set the filter to use the currently selected Treatment Ref Matching Field = the MYMOP Matching ID.

               

              Anther way is to set up a new field with Global storage and match it to the Session ID. Attache a script to the Session portal fields, or add a button, that sets this new ‘Global’ field with the currently selected Matching Session ID. This changes the underlying structure for the purposes of displaying specific data.

               

              Another way, may be easier to understand, is to combine these two approaches. Create the new Global storage field and the script to fill it and use it in the Portal Filter.  This does NOT require changing your current relationships. You could use a Global Variable instead of a global storage field.

               

              Sorry for all the FMP speak. The FMP database jargon is a bit different than for other relational DBs. You’ll get used to it after awhile. If anything is new and/or doesn’t make sense let us know.

              • 4. Re: Relationship between two portals on same layout not working as expected!
                prtd

                Dear ChOcOhalic,

                 

                Many thanks for your helpful suggestions below, especially the clarification that the defined link connects to all related records. What was confusing was because the Patient to Treatment Block relationship produced just the blocks relating to the patient selected, I expected the Treatment Block to Session relationship to behave in the same way - but clearly not!

                 

                I will try one of your suggested solutions and hopefully this will solve the problem - it's just frustrating that FM behaves this way as the whole point of relational databases, I thought, was to make record retrieval easy and straightforward but for this particular table configuration which I thought would be quite common, it doesn't.

                 

                By the way, Steve of 'reelstev' who also responded to my query, suggested that 'session model' approach to what I am trying to do, would be worth considering. Do you have any good templates of this, so I can find out more on how this works? The example templates provided with FM13 seem all to use a more conventional approach, each being tailormade to the application in question.

                 

                Regards,

                 

                 

                Peter

                • 5. Re: Relationship between two portals on same layout not working as expected!
                  ch0c0halic

                  Sorry, I don’t have any templates showing a Session model. I got lost in the Asteroids of Steve’s explanation.

                  §^=)

                   

                  In case you aren’t familiar with the FMP jargon.

                  TO = Table Occurrence, the individual table boxes in the relationship graph.

                  TOG = Any group of TO’s connected by relationship lines. A TOG can be as simple as three connected TOs or as complex as hundreds of TOs.

                   

                   

                  The Context of the layout determines how things show up. FMP does perform the way you describe “from the context of the current layout”. When you look from the Patient record to the Treatments you show only the treatments for that patient. But the relationship from Treatments to MYMOP is not in the right context to show only one Treatment’s related MYMOP records. If it did it would not be performing correctly.

                   

                  You can think of the TOG as a road from Patient to Treatment to MYMOP. When you start on the road you can only go from the Patient to the Sessions. But you can take many roads from the Sessions to the MYMOP. That path has not been decided yet. So the DB must allow you to view all possibilities.

                   

                  The selection of a specific portal row is a visual aid for the user to know which one is selected. But it does not tell FMP what to do about it. What if there were no second portal? What should FMP do about that setup? If you select a portal row and that effected the relationship to limit the related records then a Summary field of total MYOP records (for example: count ( MYMOP::creation_date )) would change showing an erroneous count. It is supposed to be all MYMOP for the Patient.

                   

                  Try to think of all possible ways a Developer would like to show data. If the selection of the portal row prohibits showing all related records then its a limiting factor and makes it very difficult to do the most common operation of showing related data. Your design is, I believe, pretty common. But your desire for how it should work is too developmentally limiting.

                   

                  Try this: create a layout configured to show the Treatment TO. Use a list view. Show the patient’s data (its a 1 Treatment - to - one Patient relationship). Put a portal to the MYMOP on this layout. Find records for a specific patient and scroll down the records. This works the way you describe. Because, the context of each record is the Treatment and the TOG relates out to the individual records on either side.

                  1 of 1 people found this helpful
                  • 6. Re: Relationship between two portals on same layout not working as expected!
                    reelsteve

                    Hola Peter,

                     

                    I have a template file I can send your way.  After the day starts to cool off, I'll take a minute to make sure it has the latest revs of a few elements. I'd prefer to send it direct to you as it also contains the noc list from mission impossible 1 and I don't want ethan hunt to start blowing up my phone with rants.  can you pm me here with your email or send it to my email :  reelsteve at mac dot com.

                    thanks

                    • 7. Re: Relationship between two portals on same layout not working as expected!
                      Mike_Mitchell

                      Peter -

                       

                      I'm sure the other fellows can help you solve your actual issue, so I'll refrain. However, in the interest of helping you understand what's going on, let me see if I can shed a little light on what's actually going on.

                       

                      Each "hop" through the Relationships Graph carries with it what you might call an "implied found set", equal to all the records in table occurrence (TO) B that match the relational criteria from TO A. That chain of "implied found sets" is maintained as you proceed through the Graph - and it doesn't respect whatever record you select in a portal. All it knows is what records match the relational criteria.

                       

                      In your example, here's what's happening: As you jump from Patient Records to Treatment Block Records, FileMaker says, "Show me all the Treatment Block Records where the Patient ID Match Field in Treatment Block Records is the same (=) as the Patient ID Match Field in the Patient Records record I'm currently looking at." Which is the behavior you're observing.

                       

                      But here's where it gets interesting. You now have an "implied found set" of Treatment Block Records. As you jump from Treatment Block Records to MYMOP Records, FileMaker says, "Show me all the MYMOP Records where the Treatment Ref Match Field is equal to any of the Treatment Ref Match Field values in any of the Treatment Block Records that match the current Patient Record." In truth, that's what you want, because you want to see all the MYMOP records that are associated with the current patient.

                       

                      Clicking in a portal row does nothing to change the relational setup. You haven't told FileMaker that you want to alter the relational criteria when you just click in a portal row. All you've done is choose a related record. So, it happily continues to show you the records that match the relational criteria that have been given to it.

                       

                      What the guys are describing to you is a number of methods for making the relational joins dynamic, for changing them "on the fly", as it were, so you can "drill down" deeper into your structure. Different methods exist, and you can select the one that works best for your needs.

                       

                      HTH

                       

                      Mike

                      • 8. Re: Relationship between two portals on same layout not working as expected!
                        Oliver_Reid

                        I would do this

                         

                        Keep the outer join to Patients to give you a list of all patients.

                         

                        When you click on patient have a script set a global feild to that patient id, commit and "Refresh window, flush join results".

                         

                        Use conditional fomatting to highlight the selected patient (patiend id - global field)

                         

                        Have second T.O. called "Patient Selected" and relate t Practice Info to Patients by matching the global field to the patient id. Connect treatment and session T.O. chain to THAT T.O. instead.

                         

                        If were doing this I would also have a script set the global to the first patient in the list when the layout is first displayed - looks nice to have th second two portals populated, and clears out any prvious gobal value. It's also quite easy to have type ahead field to filter the patient portal by name.

                        • 9. Re: Relationship between two portals on same layout not working as expected!
                          prtd

                          Thanks for this - have just downloaded FOCUS file and now understand more about how the 'Session Model' works.

                           

                          Regards,

                           

                           

                          Peter

                          • 10. Re: Relationship between two portals on same layout not working as expected!
                            prtd

                            Thanks Steve - have emailed you separately.

                             

                            Peter

                            • 11. Re: Relationship between two portals on same layout not working as expected!
                              Stephen Huston

                              Have you considered portal filtering of the second portal.

                               

                              Have a script which sets a $$global variable from a field in the picked portal row of portal 1 (run when click in the portal row), and filter portal 2 based on that $$variable value.

                               

                              (You can use a script trigger to clear that global variable whenever changing parent records onscreen.)

                              • 12. Re: Relationship between two portals on same layout not working as expected!
                                prtd

                                Mike -

                                 

                                Thank you for taking the time to explain more about how FM works. I am beginning to see the 'light', and that from a programming point of view now understand better why FM includes all the MYMOP records in the "implied found set" rather than just those associated with a record selected within the Treatment Block portal.

                                 

                                Others, as you comment, have been very helpful in suggesting useful solutions which I will be trying out over the next few days. Indeed, I am much impressed by how many have responded to my query and people's support in general. It's good to be part of such a helpful and knowledgeable network!

                                 

                                Regards,

                                 

                                 

                                Peter

                                • 13. Re: Relationship between two portals on same layout not working as expected!
                                  prtd

                                  Dear Stephen,

                                   

                                  Thanks for your (and others) help on this. Have followed your suggestion and it worked a treat.

                                   

                                  Regards,

                                   

                                   

                                  Peter