3 Replies Latest reply on Jun 21, 2013 10:01 AM by philmodjunk

    Problem with my Patients Database



      Problem with my Patients Database



           I am currently trying out FileMaker Pro on a project, which is working on a database of patients. I have been stuck at this step for a while, and it'd be appreciated if I could get some help / advice. Basically, I have a table of info of patients (called Patients) with fields: Name, Type of illness, Name of illness, Date Seen, etc. Type of Illness is another table, which I manually added some terms. Some examples of the terms that I added are Bone, Chest, Cardiac, and GI; and each of the illness would be categorized by one of these. I have a problem here: If I try to add a new type of illness in the Patients table that is NOT manually added in the Type of Illness table, (ex. neuro, somatic ,etc, as opposed to Bone, Chest, etc.), that specific type of illness does not get updated in the Type of Illness table. So how can I make the program update an object of the field that is added from the Patients table if it is not already in the Type of Illness table? 

           Also,What I want to be able to do is, everytime I add a new patient to the database, have a separate Layout update Type of illness and Name of illness automatically. This layout will be named Patients Database; and this part, I was able to do. Then, I would like another layout (called Sorted Database) to contain the same database, but sorted according to the type of illness. I have a problem here: I do not want any overlaps. For example, two patients with the type of illness: Bone, and name of illness: bone fracture would get updated in the Patients Database as well as in the Sorted Database, but in the Sorted Database, there would show 2 "bone fracture" 's. What I intend to do is that on the Sorted Database, I wish to list all of the Names of Illness only ONCE, sorted alphabetically and grouped by Type of Illness. Then, I wish to add a button on the Name of Illness that enables the user to click it and see a list of patients who have that illness on a pop-up or an extended tab. Or, if possible, create a Search bar on the top and enable users to type and search both type and name of illness. 

           I'm sorry this may be long and complicated, but this is one step that is really important to me in order to get started, but I am stuck. Thank you so much for you help in advance! 

        • 1. Re: Problem with my Patients Database

               I think you need to take a step back and look at your data model--the tables and relationships you have in place that then are referenced by your layouts in order to see and work with the information stored in them.

               Looks like you need something more like this:


               Patients::__pkPatientID = Patient_Illness::_fkPatientID
               Illneses::__pkIllnessID = Patient_Illness::_fkIllnessID

               A given patient can have many illnesses and many patients can have the same Illness. This set of tables allows you to document and use such a "many to many" relationship. Fields in Patients would identify that one patient, name, birthdate, phone, etc. Field in patient_Illness store data specific to that patient for a specific Illness for which they are receiving treatment and the Illnesses table would list all specific Illnesess for which at least one patient has recieved treatment. To add a new Illness to the list, you'd add a new record to this table.

          • 2. Re: Problem with my Patients Database

                 Thank you so much for helping me and getting me started! :) 

                 I did not know about the many-to-many relationships, but now I do.

                 But how can I make the Patient_Illness table to be automatically updated? So if I add a patient to the Patient table, I would like the Patient_Illness table to automatcially sort and update patients info and their illness. So that in the end, I would like the user to be able to search or choose from drop down menu the type of illnesses and then show all names of patients with that illness. 

                 Again, thank you in advance!

            • 3. Re: Problem with my Patients Database

                        I would like the Patient_Illness table to automatcially sort and update patients info and their illness.

                   The patient_Illness table does not store any patient info--only an Id field that links it to a record in the Patients table. Perhaps you are referring to a Patient_Illness layout? Tables, Layouts and Tutorial: What are Table Occurrences? are three different things in FIleMaker databases, that often have identical names.

                   Adding a new Illness that is not already recorded in your database requires adding a new record for that Illness. Linking a new patient to an Illness that is already listed in the Illness table simply requires selecting that Illness for that Patient.

                   A portal to Patient_Illness placed on the patient layout may be used to select an Illness for a given patient. A drop down list in the portal row can be used to select an Illness.

                   See this demo file on many to many relationships. Note the use of buttons with + on them that add new records and then automatically link the new record by creating a matching record in the join table with the appropriate ID numbers: