3 Replies Latest reply on Nov 25, 2009 1:55 PM by philmodjunk

    complex joins, portals, the limits of FMP, and potential insanity



      complex joins, portals, the limits of FMP, and potential insanity


      I am setting up a more flexible means of maintaining records related to psychological treatments. I used to use a very simple, single event record which held date, type, note, client billing, and insurance billing. These events records were used for treatment records, billing, and therapist practice accounting. In it's simplicity it was quite limited to a single client with a single therapist and a single billing for client and insurance. I have been pushed by circumstances to move to a more complicated table structure.


      Treatment situation is based around "events" which are usually a treatment session, but can also be phone calls, receipt of payment, invoice mailed, etc


      Treatments can have multiple clients

      Treatments can have multiple therapists 

      Clients can be in multiple treatments

      Therapists can be in multiple treatements 



      So a join is in order, right.


      And any particular "event" is going to be many instances of the join.


      Here is where my question comes in.


      An event has additional information associated with it.


      An event can have one or more notes associated with it

      A single note might be associated with all therapists and clients

      addtional notes might be specific to a client


      An event can have one or more fee charged, fee collected, and/or billings associated with the therapist and/or clients


      1) So I set up the basic join above.

      2) I added a Note_fk field to the join which usually wil usually be the same for all join records for the event,

          but there might later allow an additional client-specific note through another join record

      3) In my FinancialTransactions table I added a field for join_event_fk

          e.g. any part of the event can then have multiple fee/billing events (client can be charged, insurance can be charge, payment can be collected, therapist pay-by-session can be issued)

          This seemed straightforward in that an particular attendee would have a single join_event record and could have all the associated accounting related to it. 


      All seemed well in conceiving this, but when I started to set up the layout it got problematic. I think the problem may be trying to related multiple financial transaction to a join_event. It seems difficult to get the correct reference to a record using relationships, e.g. I couldnt get portals to properly display all related info. I started to think the problem was in my setup.


      Is this design going to lead me down the road to insanity?

      It occurred to me that maybe I should make the relationship between join_event and financial transaction like those to of client, therapist, and treatment, where the join_event holds a single fk to a single financial transaction. So more join_event records.  


       Any advice on what will really be workable given FMP's referential powers and layouts.





        • 1. Re: complex joins, portals, the limits of FMP, and potential insanity

          Welcome to the Joys of Relational Database Design. :smileywink:


          First let's confirm you conceptualization of your tables and relationships

          It seems you have conceived of the following data tables:







          You then mention "events" and give examples of many different kinds of such events. If you are thinking of recording all such "events" in a single table, you may be over complicating your design.


          I'd consider storing Clients and Therapists in a single table, People, with a field that distinguishes threrapists from clients. THen a join table can be created, we'll call it "sessions" that link many treatment records to many People records.

          People:: PeopleID = Sessions:: PeopleID

          Treatments::TreatmentID = Sessions:: TreatmentID


          One additional advantage you might find to merging clients and therapists is that you probably will be needing to record very similar contact info for each and this will be easy to do with a such a combined table.



          • 2. Re: complex joins, portals, the limits of FMP, and potential insanity

            Thanks for the reply. In fact your suggestion is already in place. All people, regardless of types, role, function are in a single table called "Contacts" with a field ContactType which identifies the persons role. The join you suggest is indeed the basics of the structure I have in place. The way I wrote it made it sound like separate tables. This part of my join seems to be working fine.


            My data tables are


            Contacts (includes clients, therapist, etc) basically everyone who has a name, phone, email, address, etc







            where Session_join has using your notation:



            Session_join::ContactID_fk = Contacts::ContactID_kprime

            Session_join::TreatmentID_fk = Treatment::TreatmentID_kprime

            Session_join::NotesID_fk = Notes::NoteID_kprime 


            and in the Billing Table

            Billing::SessionID_fk = Session_join::SessionID:kprime 

            Billing::SourceAccount_fk = Billing_Accounts::AccountID_kprime

            Billing::DestAccount_fk = Billing_Accounts::AccountID_kprime


            My problems became evident when I set up a portal to display data about the session. A portal into the Session_join table gave me the appropriate list, but getting to the multiple Billing records became problematic, requiring hard-coding access to assumed records (such as assuming a single billing record for the client and a single for the insurance co). This is not a desirable developmental style, certainly, but it also wouldnt return the correct data at times. In some instances it would return data from the wrong Billing record.


            I tried using a portal into the Billing table and had similar problems getting to the Contact records. All lines in the portal showed data from the first Contact record from the test session.


            Does that clarify the conceptualization? I hope so, because I'm in need.... I keep thinking the problem lies in making the relationship between the Session_join and the Billing to be one Session_join having many Billing records. I could change the relationship so that one Session_join has only one Billing record and move to having many Session_join/Billing pairs (at that point I could have the Billing fields in the Session_join, but might not for purposes of conceptually separating data by function) 


            Thanks for helping me through this bit of complexity... 

            • 3. Re: complex joins, portals, the limits of FMP, and potential insanity

              One approach comes to mind that is as much an interface design as it is a relational database design...


              You might consider a separate billing layout based on individual session records with a portal showing related billing records.

              To view the billing info for a given session, you'd click a button in the Sessions portal and use Go To Related Records to switch to the Billing layout where you'd then see all the billing entries related to that one session.


              Go To Related Record is powerful but incompletely documented by Filemaker. For more on GTRR, see the following thread:


              The Complete Go To Related Record