4 Replies Latest reply on Mar 17, 2015 4:20 PM by mtfowler

    Linking one record in a database to a separate sub database or set of records

    mtfowler

      Title

      Linking one record in a database to a separate sub database or set of records

      Post

      I am using version 11 Pro Advanced.

      As a personal injury lawyer, I have been using Filemaker Pro since version 6 for client notes and negotiation. I have a record for each client with multiple layouts linked a the client's file number. The problem I need to solve is how to set up another layout for a medical timeline for each client that allows sorting within the layout by date of treatment, doctor, diagnosis, treatment, etc. I want to be able to go through a stack of medical records that are out of sequence and have possibly overlapping treatment dates and enter each visit and then sort and print by date, doctor, diagnosis, treatment, medical charge, with a total charge summary line. 

      Ideally, while reviewing a client's file I could switch to the Medical Timeline Layout, and then add a "record" for each date of treatment with separate fields for DOT, Dr, Hospital or clinic, Diagnosis, charge, etc.

      I can't figure out how to link one record in the database (the client) to a different layout that contains multiple records specific to that layout. It seems like there would be a better way to do this than to make each timeline a separate database. 

        • 1. Re: Linking one record in a database to a separate sub database or set of records
          SteveMartino

          For quicker help, let's make sure we are on the same wavelength with all the terminology

          I have a record for each client with multiple layouts linked by the clients file number.

          1.  By multiple layouts do you mean linked from clients to different tables?

          The problem I need to solve is how to set up another layout for a medical timeline for each client that allows sorting within the layout by date of treatment, doctor, diagnosis, treatment, etc

          2.  Can I assume you mean you need to link another table (TimeLine) to the Client table?

          I want to be able to go through a stack of medical records that are out of sequence and have possibly overlapping treatment dates and enter each visit and then sort and print by date, doctor, diagnosis, treatment, medical charge, with a total charge summary line. 

          3.  "...stack of records..."  Records from the same client?

          ...enter each visit and then sort and print by date, doctor, diagnosis, treatment, medical charge, with a total charge summary line

          This is just a standard report, once you get the proper relationships set up

          4.  All tables should be have a primary key and the tables linking records to clients will have a primary key, and a foreign key to create the relationships.  It appears in your instance you are using a client's file number.  This number must be unique, and I assume you generate this number manually

          Can you post a pic of your relationship diagram?

           

           

           

          • 2. Re: Linking one record in a database to a separate sub database or set of records
            mtfowler

            Thanks you for your response Steve. I am obviously using the wrong vocabulary to describe my problem.

            1. No - I have one table. "Mark's Negotiated Files " (Clients) It is very simple table with 223 fields. 3800 records (one for each client).

            2. Yes - I want to be able to go from a specific record (Client) to another table (Timeline) - but I do not always need to create a timeline for every client. 

            3. Sorry - I am using "stack" in the sense of a pile of papers - medical records of a client.

            4. Yes the file number is generated manually.  I think I have attached the "relationships" pic you requested. They were created to find the last modified record, it works, but I am not sure why. 

             

             

             

             

             

            \

            • 3. Re: Linking one record in a database to a separate sub database or set of records
              SteveMartino

              I don't think you're taking advantage of FileMaker's power of relationship databases.  It's really hard to tell what's going on with your tables, but most will agree 233 fields for one table is not usually good.  It seems you should break some of these fields out  and into related tables

              Also,  your tables don't seem to be related by client ID.  I see duplicates of field names.  Are these all one table with 2 other table occurrences (also know as TO)?

              Yes it's working, but as you see, you are now having problems with a simple task...adding a table.

              I would take a look at some starter solutions to get an idea of how to set up the database

              If I were tackling this, I would start with a few tables.

              1.  Client Table with an auto enter serial number.  This will be your primary key.  In your case you are creating a client id, make sure in set up that its unique.  Keep in mind that once the relationships are set up, if you modify this number, all the relationships will break.  I suggest that when you first set up a new client, you enter the ID, and make it so it cant be changed.  This table has all the basic information about a client--name, address (sometimes this can be a related table if multiple addresses are involved), Phone number (also can be related table if multiple phone numbers are involved), email, referred by, notes, etc.

              2.  Case Table with an auto enter serial number as the primary key, and a foreign key (text field) that will populate with the clients id.

              In this table you would have fields referring to case information.  These tables will be related by Client Table::Primary Key to Case Table::Foreign Key.

              3.  Medical Timeline Table with an auto enter serial number as the primary key, and a foreign key (text field) that will populate with the clients id. These tables will be related by Client Table::Primary Key to Medical Timeline Table::Foreign Key.

              Allowing the relationship to create records will help

              Once you have these relationships set up, you could simply have a layout for Clients with their info at the top, and a 2 tab panels, one each with a portal-one for Case, one for Medical.  In each of these portals, on each row, would be a button to take you to the related record, on another layout, to show the full detail of Cases, or Medical.

              There are many good examples in the starter solutions where you can see this...Invoicing Solution, etc.

               

               

              • 4. Re: Linking one record in a database to a separate sub database or set of records
                mtfowler

                Thanks again. You are correct. I started this table 15 years ago purely for my own use and have expanded it as needed by adding fields, scripts, calculations etc. It is cobbled together, but it has worked for what I needed up to now. I want to make it better, but reading the missing manual and Help has not gotten me there.

                If you can please hang in here with me I think I am close to getting what you are saying. I understand what you mean about the Primary Key, but the phrase  "and a foreign key (text field) that will populate with the clients id"  is above my head.

                My Client table started out as a Starter Solution Contact list. Within it is a field called Contact ID that is an automatically generated serial number. If the Contact ID is the primary key, what do I do to set up the foreign key (text)?

                If I use the Starter Solution - Family Medical Records as the Template for my Timeline, what would be the primary key of that table and what would I do to set up the foreign key? Do I need to add a field to the Starter Solution?