7 Replies Latest reply on Feb 7, 2012 8:44 AM by philmodjunk

    Tables/Relationships

    Annette

      Title

      Tables/Relationships

      Post

      Hello all, 

       

      I am a filemaker/database creator noob so please forgive my ignorance.  And thanks in advance for any help you are able to provide. I apologise for how long this is but it looks worse than it is, most of it is just informative so you might be able to follow my train of thought.   

       

      Basically I am just trying to determine how to properly seperate information on tables and create relationships.  I am creating a database for a medical office working with several therapists.  I will need it to keep client info, name, address etc, referral info such as date of referrals, by whom, etc, any contact that is made with the client, whether a letter was sent, a phone call was made, etc and of all appointments offered by each of the different therapits.  

       

      Am I correct in assuming filemaker can achieve all this in one database?  I would like the screan to be broken up something like the screen shot I made below as an example.  Now that is just an idea, if someone thinks it won't work or has a better idea I'm open for suggestion as I haven't begun the design process ye

       

      So far I have the following tables and fields listed out ready to create:

      Clients:  ClientID, First Name, Last Name, Full Name, Address, D.O.B., Age, Mother, Father, Telephone, Mothers Mobile, Fathers Mobile, Team, Diagnosis, , GP(Doctor) Name, GP Address, GP Phone ,PHN(Nurse) Name, PHN Address, PHN Phone, Medical Card No. PPS No, EIT(one of two teams) Referral Date, EIT Referred By(select from drop down list from referrers table), EITDate Received, EIT Date Accepted, EIT Date Ineligible, EIT Date Discharged, EIT MDT Date, 5-18 (Second team) Referral Date,5-18 Referred By(Select from drop down list from referrers table),  5-18Date Received, 5-18 Date Accepted, 5-18 Date Ineligible, 5-18 Date Discharged

       

      Referrers:  ReferrerID, First Name, Last Name, Full Name, Address, Phone

       

      Speech Thearpists:  Speech ID, First Name, Last Name, Full Name, Address, Phone (Also a table like this for Psychologist, OT, and PHN)

       

      GP's:  GPID, GP Name, Gp Address, GP Phone 

       

      Contacts:  Contact ID, contact Type, Date of contact, reason for contact

       

      Where it gets tricky (For me) and I am lost is with the correct type of relationships and linking various info to portals, etc.  

       

      I would like to give each of the therapists their own table that collects information pertaining to them as well so that it may be easier to collect stats.  That's why on my screen shot below I have tabs set aside for psychologist, physiotherapists, etc.  

       

      I forgot to show in my screen shot example that I would also have a tab for contacts where the fields listed above for my contact table.  This information would then feed onto the portal shown in my screen shot.  one of the areas I am unsure about is if I go to the letters tab, choose an appt letter and send that, I want it not only to appear in the appts portal, but also as a form of contact.  Do I need to do that in two different steps? i.e. send the letter it will show in appts portal and then fill out the contacts layout to get it to appear on contact portal.  

       

      Basically, I am unclear as to whether I need all the different tables I mentioned or have a made too many? not enough (should EIT and 5-18 have their own table)? Two, the proper way to create the relationships, etc.  Im assuming that I put a clientID field onto the referrers table and link clientID on client table with client ID on referrers table. (that's a big assumption as the field is going to be a drop down menu referring to the referrer table) etc, things like that.  

       

      I'll leave it at that for now, and start working on it up to that point.  I really hope I didn't confuse people more by providing too much info.   

      THANKS AGAIN!!!

       

      Database_Example.JPG

        • 1. Re: Tables/Relationships
          philmodjunk

          "Am I correct in assuming filemaker can achieve all this in one database?"

          Yes, but the key question is "Can YOU achieve it with filemaker?" Hope you are prepared to invest time in both developing the database and in learning how FileMaker and Relational Databases work. There are a number of training resources available, including books, videos and classes taught by FileMaker trainers.

          One of the first major challenges to database design is setting up your tables and relationships. You try to avoid tables that store the same type of data in separate tables as there are better and more flexible ways to work with groups of data than using separate tables. If you look at your list of tables and fields, you'll see that many of them have very similar name, address, phone fields. Instead of that, you can have one related contact info table and use it for all contact information recorded in your database, both that of clients and professionals.

          You haven't done too badly with your initial data modeling, but here's how I'd set this up:

          Tables:

          ContactInfo (__pk_ContactID ; Category, Name, Address ) This table records personal info for all people in your database including clients, care providers and people making referrals. Category identifies the contact as "client, Referrer, Provider, etc and can be a checkbox list.

          PhoneInfo (_fk_ContactID , PhoneType , Number ) Linked to contact info so that you can list as many phone numbers for a given contact as needed. Email addresses and social networking info can also be considered "Phone Types" and entered here as well if you choose.)

          Clients (__pk_ClientID , _fk_ContactID, _fk_ReferralID add any other fields not already part of ContactInfo and PhoneInfo that are unique to clients.)

          ClientReferrals (__pk_ReferralID, _fk_ContactID, _fk_ClientID, ReferralDate, ReferredFor, add other fields as needed if not already defined in ContactInfo, Clients or PhoneInfo. _fk_ContactID links this record to the professional who referred the client to you.)

          ContactLog (_fk_ClientID, _fk_ProviderID, Date, ContactType, Notes)

          Appointments (_fk_ClientID, _fk_ProviderID, Date, Time, Notes, plus any other fields needed to describe an appt not already defined in related tables.)

          Providers (__pk_ProviderID, _fk_ContactID, ProviderType ) Add one record here for each Speech Therapist, OT, Psychologist, etc.

          Fields starting with __pk are primary key fields and should be defined as auto-entered serial number fields. Fields starting with _fk are foreign key fields. Define these as numbers. They usually are linked on the Relationship tab in Manage | Database to primary key fields of other tables that have the same name. __pk and _fk are just a naming convention that makes it easier to keep track of these fields when you work with them. You do not have to use this convention if you don't want to, but they have the advantage of sorting to the top of the list whenever you have a sorted list of field names in one of your dialogs or drop down lists of the same.

          • 2. Re: Tables/Relationships
            Annette

            Thanks for taking the time to respond Phil.  I will start making the tables as you suggested and see how it goes.  I am aware that it is going to take a lot of time and effort in order for me to get this going.  As it's necessary for work and we have no other means available to us I'm willing to try my best to get this up and running.  I just hope that people don't lose patience with me if I ask what may seem like a silly question on here.  

             

            Thanks again!  :)

            • 3. Re: Tables/Relationships
              philmodjunk

              Answering beginner questions are one of the main purposes of this forum.

              Do keep in mind that the above tables are just a start. Chances are that you'll be adding more before you are done.

              • 4. Re: Tables/Relationships
                Annette

                I'm sure I will........No doubt I've gotten myself into a lot of work here! ha ha 

                • 5. Re: Tables/Relationships
                  Annette

                  Ok, so I've created the tables as you suggested rather than how I originally intended and was looking to start creating my layouts.  Will I still be able to create a layout similar to the idea I had above?  i.e. tabs for various related info and the portals for appts and contacts?  I presume so.  

                   

                  My next question, if I create a tab as I intended for client info like name, address, etc and then the tabs with the referral information, etc how do I get it to only show certain categories (client, professional, referrer) on each tab?  is this done by a script or filter or something?  Because when I'm scrolling through the records in this particular layout I don't want anyone but clients to show not all the contacts entered.  I'd only want the other categories to show say in drop down lists for appropariate fields like referrer and professionals.  

                   

                  Thanks again

                  • 6. Re: Tables/Relationships
                    Annette

                    Sorry, another question, you said create a phoneinfo table and a contact can have as many numbers as needed.  But, ....if I'm creating a layout where you enter a contact, and I add a field for numbers, how do I allow the entry of multiple phones, since it's the only field I'm referencing? 

                    • 7. Re: Tables/Relationships
                      philmodjunk

                      Q1:

                      If you base your layout on clients, you should be able to use such a set of tabs with portal to show/edit data in clients and also the related tables.

                      Q2:

                      Portals are based on a relationship you define. The relationship will limit the portal records shown to just those that are linked to the layout's current record. If the resulting set of related records needs to be further limited, you can define a portal filter.

                      example:

                      If you have a portal to providers and you want to limit the list to only related providers that are Psychologist, you can use this portal filter:

                      Provider::ProviderType = "Psychologist"

                      Q3:

                      You can place a portal to PhoneNumbers on a contactInfo layout and then you can add as many phone numbers as you need by adding related records in this portal.  A portal to PhoneNumbers placed on a clients layout should also work, provided you first create the related contactInfo record before adding phone numbers.