1 2 Previous Next 15 Replies Latest reply on May 10, 2013 5:35 PM by WalsySry

    Multiple tables to one layout strategy?



      Multiple tables to one layout strategy?



      I have some familiarity with Filemaker, but my purposes are pretty limited, so, as always, forgive the potential naive question.

      I am trying to put together a client database for a psychotherapy practice.  Included is a table for professionals (physicians, psychologists, social workers, etc.).  A professional can have one or more roles in a client's life.  The professional might

      1: Refer the client to me

      2: Serve as the client's primary care provider

      3: Serve some other role (e.g., neurologist, coach, etc.)

      Professional to client is a many to many relationship, and I have set up three join tables corresponding to the above possibilities.  This has required three copies of the Professional table.

      On the client side, this works.  I have fields that will show the referrant and PCP and a portal that shows other professionals.

      The problem is on the Professional side.  I want a single layout to show me the clients that relate to the professional.  So, a single professional might refer 5 clients, serve as the PCP for 3 others, etc.  I prefer to show the clients in separate portals on the same layout.  However, a layout pulls data from a single table and FM is treating the multiple copies sort of as separate tables.  So, if I use the PCP table for a layout, it seems to have no way to show related files through the Referant join.

      I realize I can probably create another table that combines all related clients, labels the relationship and then show them on a single portal.  That seems inelegant to me, somehow.  Any thoughts would be greatly appreciated.

      And just for fun extra credit, I really want to get this to work on FMTouch, so I can justify an iPad...

      Thanks very much,


        • 1. Re: Multiple tables to one layout strategy?

          It would be better to have one join table. These joins are the same thing, from a database entity point of view.

          • 2. Re: Multiple tables to one layout strategy?

            Fenton, I am interested in the exact same question as the original poster.  Can you elaborate a little on the structure that you think would work best?  I'm struggling with the relationships.

            • 3. Re: Multiple tables to one layout strategy?

              I misinterpreted the original poster. I somehow thought he was saying mulitple "tables", and I thought real tables (which would be wrong); but I should have realized he meant "table occurrences" (the boxes on the graph, TO for short).

              So my advice was kind of irrelevant. It is a problem. What I would do would be to use the 1st table occurrence created for that "base table" of Professionals (there's a reason for this).* Disconnect it. Create a new table occurrence, and reestablish the one we changed.

              Take the now disconnected table occurrence, and use it for the "anchor" table occurrence for the table "Professional". Base the main Professional layout on that table. 

              Then create 3 new table occurrences of the Client table, connect them to the Professional anchor TO, using the same logical connections as the existing Client table was using (which already has a working setup), but in reverse.

              So each of the two main tables have their own connected group of table occurrences, eliminating the cross-conflict (or whatever we call it).

              So it's more table occurrences, but less confusion. It could possibly (likely) be done in FileMaker 11 with less TOs, using filtered portals. It kind of depends what else you want to use the relationships for, other than viewing in a portal, whether just portal filters would be enough.

              * Forgot to add, the reason why you really want to use the "original" table occurrence for the "base anchor" for a table. It becomes the default entry in every subsequent calculation "context". Which usually doesn't matter, but certainly matters if its a relational calculation, which doesn't evaluate properly from your base layout; best to have the default be your base table occurrence anchor.

              • 4. Re: Multiple tables to one layout strategy?

                Fenton, I'm pretty new at filemaker, so please excuse my ignorance.  I have created a database that has table1 with my patients that have been referred to me.  That table has their demographic and also the A) referring physician and B) their primary care physician (PCP).  Then I have table2 that is a list of physicians.  On that table is the physician's contact details and I would like two tabs.  The first one, I would like to show a portal that shows all the patient's that the physician has referred to me.  The second tab would be all the patient's that he is acting as primary care physician for.

                I am struggling with how to do this given that I do not want duplicate records for physicians or have to enter them twice on two separate tables.  I've spent hours trying to figure this out, but haven't come up with anything that works.  I have no problem with just the referring physicians, but don't know how to integrate the PCP aspect.  A physician may be either a referring physician for a patient, a PCP for a patient or both.

                I would be exceptionally appreciative of any help you can provide.

                • 5. Re: Multiple tables to one layout strategy?

                  There are 2 ways of doing this. I believe your current setup has the PhysicianID for Primary Care Physician (PCP) and Referral Physician directly IN the Patient record. That structure assumes two things: 1. A patient has only 1 possible PCP or Referral, and 2. No history of such is needed.

                  This example file uses that structure: Physician_Patients_fej.fp7.zip

                  There is another possible structure, which would not make those assumptions. It would not have ANY Physican IDs directly in the Patient record; it would use "flag" fields in the Physician|Patients "join" table for PCP and Referral connections. It would require pretty much the same number (or more) of relationships. But it would have an advantage if you wanted to run a report on who does what, and when; as all of that would be in the join table. Every connection, PCP, Referral, or any other role, would have its own record in the join table, hence could be sliced and diced in one report from that table.

                  • 6. Re: Multiple tables to one layout strategy?

                    Fenton, thank you for your thoughtful reply.  I have been successful doing it this way.

                    relationship table

                    As you can see, I have two table occurences of patient.  Each with a different indexed field pointing to the same serial ID field in physician.  It seems to work well, but I don't know if I'm going to run into problems in the future with this design.  What do you think?

                    • 7. Re: Multiple tables to one layout strategy?

                      It looks like you're pointing the "referring physician's ID in Patient" to the Physician's ID (in the main Physician table). Then from that Physician table pointing to another table occurrence of the Patient table, this time pointing to the PCP ID in Patients.

                      From what I think, your solution so far will only work for:

                      Physician can see both IDs, by looking in both directions. But it is a little "trapped" on the graph.

                      Patient's main layout can only the "referring" Physician(s). They cannot see their PCP Physician from that same layout.

                      You could add another table occurrence instance of Physicians, and point Client at that. Then you could see both from Client.

                      But there is currently no table occurrence of the "join" table required to have "other" physicians, for the other "roles". And adding them to an already convoluted graph would be a PITA, in my opinion.

                      I use the "anchor buoy" Relationship Graph organization method for that reason. It allows further expansion of the database, without getting balled up in knots.

                      In my graph Patients and Physicians each have their own table occurrence group (TOG). Their layouts are based on the two "anchors" (far left). That way there's no conflict. Each handles its own PCP, Referral, and multiple "other" connections.

                      P.S. the two table occurrences on the far right are not entirely necessary, as the popup menus handles showing the name directly from the join table. But they would be useful for navigation, or reports where you do not want to use popups menu formatting. The colors represent the 3 base tables. The naming convention is "structural" (more or less) and alphabetic.

                      • 8. Re: Multiple tables to one layout strategy?

                        Fenton,  you absolutely rock!  Thanks, I implemented what you advised and its working great.

                        I hope you might help me with another solution.  I am now trying to keep track of each encounter that I have with patients.  This initially will be for billing so that I can generate a billing sheet each time.  An encounter may consist of inpatient or outpatient visits.  If it is an inpatient visit, it could include a consultation, surgery (with multiple procedures per surgery) as well as multiple follow up visits.  Outpatient visits may either be consultations or followups.  There could also be procedures (surgeries) with each visit.  Consultations, followup visits and surgeries are all defined by CPT codes.  For each code, there must be a diagnosis (ICD-9).  I have set up a diagram below to show how I set things up.

                        When I try to make a "superbill" which is similar to an invoice from the encounter, I can only get a single line item out of each surgery or followup.  There is never more than a single consultation for a single encounter, so this isn't a problem for that.

                        Any thoughts?  I promise that I am not trying to put together a complete electronic medical record. Laughing   This is about it as far as what I would like to accomplish.

                        • 9. Re: Multiple tables to one layout strategy?

                          If "Surgery" on your graph is a "child" of Encounter, a record created as needed as data entry for each Encounter, then your structure would work. I would assume in that case that "Case_Join_Surgery" would be a "reference" table, which listed all surgeries. But I don't see where "procedures" would go in that case, or are they always implied by the surgery itself.

                          The "totals" in Encounter would then be Surgery::amount + Consultation::amount + Followup::amount, if 

                          There is always confusing whenever "reference" tables are in the mix, as it is hard for others to know which are which, which are "data entry" tables and which are references. From the graph I can't really tell if another set of join tables is needed or not.

                          • 10. Re: Multiple tables to one layout strategy?

                            Thanks Fenton.  I ended up simplifying things considerably by just adding a field in CPT_Codes to categorize each code into whether it was a consult, followup or procedure code.  That way I just have the Encounter table, a single join table and then the one CPT and one ICD-9 table.  It seems to work great.  I've spent a considerable amount of time refining the look and usability of the interface.  I have another question for you.  I have a portal in my encounter table that links to the join-file (case_join_encounter).  In that portal is entered in individual billable events such as a consultation or procedure or both.  If there is a surgery involved, many times there will be multiple codes to be entered.  My layout looks like this:

                            Encounter Layout

                            I also have another table with a layout for CPT and ICD-9 codes that looks like this:


                            I'd like to be able to have someway of pressing a button on my encounter table that would run a script or something and allow me to go to the CPT code or ICD-9 code table and perform a find.  I would then like to be able to select multiple codes with check boxes or the like and have them populate the encounter table's portal upon my return.  Is there anyway to do this?

                            • 11. Re: Multiple tables to one layout strategy?

                              I'm not really sure what you've done with an "encounter." It looks like to me that you're just entering codes directly into the join table, and there no longer is an actual "encounter." Which would be a mistake in my opinion, as an encounter is an entity, a person/timestamp entity. I can't really tell these things for sure by looking screenshots. Many times the only sure way to diagnose (pub) databases is to see it.

                              But in answer to the question, I can see that you need something, because that drop-down is too long, especially if you'd have to do it over and over again, for multiple codes. In this situation I think of another window, or portal in a tab. Both have their pros and cons. 

                              A window requires that you script control of that window. You'd need to control opening and closing; it would need to be made "modal", so the user could not just wander off; because each choice would create a join record for a specific parent. It would be a disaster if they moved to another parent. 

                              The advantage of a window is that it could be a "code" table window, could be shown in a regular list view, and be easily "findable". 

                              A portal on the other hand has the advantage that you can put it in another tab on any layout (using globals field(s) as the source key(s) of the relationship, hence portable). But portals don't lend themselves easily to Finds. Though one can implement a scripted Find, and swap the portal's records accordingly (by swapping in the IDs of the found records as the source key).

                              In this case it seems you'd want to have EITHER the code itself to filter, as well as the name (I don't know about the code, but is seems so). Trickier, but doable. If it is not a "find", but a filtered portal, then it would need to target each word (or part of a word) in the code's name. That would require some way to target each word by typing only a part; not so easy to do, especially if each (partial) word is expected to narrow the results. [Type "chro" and "tons" to filter, for example. Easy to do with a Find, hard to do with a relational filter.]

                              No matter how you do the list for choosing, the mechanism for adding codes would be a button on each row/record of the found/filtered results. Clicking it would immediately create the join record with the code AND whatever was the ID of the "encounter" (or whatever the parent ID was where you came from). There is no need for "selecting multiple", since it adds each immediately, via a script.

                              • 12. Re: Multiple tables to one layout strategy?

                                Fenton, sorry I didn't explain the screenshots very well.  The first one looks like its still on the Patient table, but it is actually an occurence of the encounter table.  The tabs contact details and encounter are not real tabs, but script buttons so that they switch back and forth between the two tables.  The encounter script checks to see if there is a related encounter record and goes to that if there is.  If not, it opens a dialog box that asks if you want to create a new encounter for that patient.  If you click yes, it goes to the encounter table and creates a new record relating it to that patient.  It seems to work well.

                                Thank you so much for your direction.  I'll have to do a little reading and experimenting on how to implement the window technique.  It seems like the better option.  I'll report back when I have something working or I reach a roadblock.

                                • 13. Re: Multiple tables to one layout strategy?

                                  Well that didn't take long.  I'm not sure what is meant by "modal" or what you mean by script control opening and closing.  Are you just referring you the script commands of open new window and maybe placing a button on the layout somewhere that closes the window?  I suspect its something more advanced than that.  If you have any interest in seeing a copy of my database, I could send it to you minus the patient records of course.

                                  • 14. Re: Multiple tables to one layout strategy?

                                    Attached is a simple example file to create what I call a "windoid", what I mean by "modal" is that you are trapped within the window. You cannot click into other FileMaker windows while the "windoid" window is open. This is accomplished by a script which goes into a never-ending Loop. 

                                    That way no one can wander away from the parent record (at least no easily). Which is helpful when you have buttons running scripted routines from the "windoid" that set data into the the parent record, or into a child table of that parent, using that parent's ID.

                                    One hitch with this is that the never-ending Loop will not stop visible scripts in the Scripts menu from running. One way to stop that is to use the Get ( AllowAbortState ) function to check that you are not in an Allow User Abort [“Off“] state (which you are within the Loop script). 

                                    If you preface the visible scripts with this, it will stop them from running:

                                    If [ Get ( AllowAbortState ) ≠ 1
                                    Exit Script
                                    End If

                                    Or, fancier, you can create a Custom Menu (if you have FileMaker Pro Advanced) which removes the Scripts menu when you load this layout (every layout can have a Custom Menu attached).

                                    Anyway, the example file has I think the simplest method that will work for this. The "window placement" is pretty basic on this one. You can specify the size and location (more or less) for a new window. You can calculate the size and position of the current window, a place your new window much as you want, either within or along-side the existing window.

                                    Learn how this one works, especially how to get out of the Loop. Create a button on the "windoid" layout. Do this first. Create a script for it, with Close Window [ current window ]. When you attach the script to the button, use the Halt current scripts option. Otherwise be prepared for an endless Loop, which may force you to Force Quit FileMaker; which is bad news for any open files. Script Debugger can get you out, if you have it.

                                    The advantage of a modal window is that you no longer have to worry that users might set data into the wrong records (though you can test for the ID changing, by capturing it, then comparing later; you should do this in any case). You can show another table in a regular List view, not a portal, so users have full access the Find engine, without all the fuss and limitations of a portal. If you're data is kind of loose like that.


                                    1 2 Previous Next