8 Replies Latest reply on Jan 31, 2014 8:22 PM by philmodjunk

    Creating an extendable table layout in a portal row - possible?

    PaulP_1

      Title

      Creating an extendable table layout in a portal row - possible?

      Post

           Hi all,

           I recently started on a research project and it was suggested we use Filemaker Pro as a way of collecting data using a graphical interface. Noone else in my research team has used FMP before and I started using it for the first time in my life 3 days ago, despite never doing much database stuff. I am amazed at what this program can do. It's not the easiest to use but sure is fun to learn. Apologies if I have done something dumb!

           I currently have completed 90% of the data collection form we are going to use. I currently have two issues:

           1. I have created a portal in which I want to record the drugs which the patient is receiving along with the dosage for that drug for a particular date. A patient can be one drug or ten, I don't know. I currently have two pertinent tables set up, Patients and Drugs, linked via Patients::PatientID and Drugs::PatientIDFk. The Drugs table contains the following fields: ID, PatientIDFk, DrugName1, DrugDose1, DrugDoseSum.

           In a portal row, I have drawn a "table" using rectangles and lines. I put the fields in and everything is great. What I want to do though is to "extend" that table and add more "table" rows so I can add more drugs if that patient is on more than one. I appreciate that this may not be possible but is there a way I can do this? I would rather avoid making multiple fields i.e. DrugName2, DrugName3, etc as it will analysis annoying down the track. I have tried nesting a portal inside the portal but this obviously does not work! My current "solution" is to just start a new portal row for a new drug even if it is the same date.

           2. My second issue is with the field DrugDoseSum. I want it to show me the cumulative dose received by a patient for any one particular drug e.g. when DrugName1="Adrenaline" then calculate running total for doses pertaining to "Adrenaline" only. I currently have it set up as a running total summary of DrugDose1 with restart when sorted by DrugName1. 

           This doesn't work however and I keep getting the dose running total for all drugs even when the drug names are different.

           I have uploaded a screenshot for some context and hopefully someone will be able to shine some light on my problems. Thank you in advance.

      Screen_Shot_2014-01-29_at_1.32.36_pm.png

        • 1. Re: Creating an extendable table layout in a portal row - possible?
          philmodjunk

               My post only applies to the first part of your question. You need to work out those details before taking a look at what needs to be set up to compute and display cumulative doses for each patient.

               What you have is a many to many relationship. A Patient can be taking many drugs and a given drug can be taken by many patients.

               Start with these relationships:

               Patients-----<Patient_Drug>-----Drugs

               Patients::__pkPatientID = Patient_Drug::_fkPatientID
               Drugs::__pkDrugID = Patient_Drug::_fkDrugID

               You can place a portal to Patient_Drug on the Patients layout to list and select  Drugs records for each given Patients record. Fields from Drugs can be included in the Portal to show additional info about each selected Drugs record and the _fkDrugID field can be set up with a value list for selecting Drugs records by their ID field. A field in Patient_Drug can record the dosage for that one drug for that one patient.

          • 2. Re: Creating an extendable table layout in a portal row - possible?
            PaulP_1

                 Thanks for your reply Phil.

                 I have set up the relationships as you suggested.

                 Table Drugs contains DrugID (the PK) and DrugName. Table Patient_Drugs contains PatientIDFk, DrugIDFk, Date and DrugDose. I placed the portal as you suggested and placed in the portal the following fields: Patient_Drugs::Date,  Drugs::DrugName and Patient_Drugs:DrugDose.

                 I am confused by what you mean "_fkDrugID field can be set up with a value list for selecting Drugs records by their ID field." I'm not sure if this is right but I set Patient_Drugs::DrugIDFk to auto-enter a looked up value from Drugs::DrugID. This didn't work because when I try to modify the field in browse mode I get an error saying "This field cannot be modified until "DrugIDFk" is given a valid value."

            Any idea what i've done wrong? Thank again!

            • 3. Re: Creating an extendable table layout in a portal row - possible?
              philmodjunk

                   That is not what I had in mind for the FK field. Open Manage | Value Lists and start a new value list. Select the "use values from a field" option and select Drugs::__pkDrugID as the first field and a description or name field from the same table as the second field. You can hide the first field so that all you see in the value list is a list of drug names from which to select.

              • 4. Re: Creating an extendable table layout in a portal row - possible?
                philmodjunk

                     And once you have this value list set up, you can select the FKDrugID field while in layout mode and use options in the Inspector's data tab to set it up as either a pop up menu or drop down list.

                     This method for selecting a value is the "simple to set up, beginner level method". More sophisticated methods for selecting a given drug from the value list that are more user friendly also are possible.

                • 5. Re: Creating an extendable table layout in a portal row - possible?
                  PaulP_1

                       Thanks for your reply.

                       I've updated it as you have said but not sure if this is the right solution. Right now, I can see that the drug names are being stored in Patient_Drugs::DrugIDFk which is the field I have on the layout. I however want the name to be stored in Drugs::DrugName which is the second field in my value list, the same value list set up for the field Patient_Drugs::DrugIDFk.

                       Do I need to set up the list of drugs beforehand in the database?

                  • 6. Re: Creating an extendable table layout in a portal row - possible?
                    philmodjunk

                         Patient_Drugs::DrugIDFk Should not store the name of the drug. It should store the assigned ID number. If, however, you used the popup menu option, it may look like the field is storing the name when it is actually storing the ID number.

                         Do I need to set up the list of drugs beforehand in the database?

                         Yes, but "before hand' can be just a few seconds before you select it for the first patient to use that drug. It merely requires switching to the Drugs layout, creating a new record to name that drug and switching back. With scripting, you can pop up a custom dialog box, enter the new drug name and then a script does the rest for you when you click OK in the dialog.

                         And this set up is quite a ways from what you'll likely need in the final working version. You will likely need yet another table for logging each time that a patient takes a dose of their medication to use to calculate the cumulative dosage that you wanted.

                    • 7. Re: Creating an extendable table layout in a portal row - possible?
                      PaulP_1

                           I got it to store the name of the drug in Drugs::DrugName but I wonder if I have Patient_Drugs::DrugIDFk set up wrong. 

                           eg. If drugname=XYZ then every instance of XYZ should have the same Patient_Drugs::DrugIDFk right? At the moment the numbers look different.

                           Also the custom dialog box isn't working properly. At the moment it only works when i have a drug selected already in the popup menu and the custom dialog box just allows me to edit that drug name. I can't actually add a new drug. If I don't select a drug in DrugIDFk before using the custom dialog script then it won't let me hit Ok.

                      • 8. Re: Creating an extendable table layout in a portal row - possible?
                        philmodjunk
                             

                                  If drugname=XYZ then every instance of XYZ should have the same Patient_Drugs::DrugIDFk right?

                             That is correct. You'll need to figure how/why you are getting different values in your Fk field.

                             

                                  Also the custom dialog box isn't working properly. At the moment it only works when i have a drug selected already in the popup menu and the custom dialog box just allows me to edit that drug name. I can't actually add a new drug. If I don't select a drug in DrugIDFk before using the custom dialog script then it won't let me hit Ok.

                             Define a text field gNewDrugName and specify global storage for it.

                             Set up the script like this:

                             Show Custom Dialog ["Enter new drug name:" ]    (specify gNewDrugName as the "input field" for the custom dialog.)
                             If [Get ( LastMessageChoice ) = 1 // OK was clicked ]
                                Freeze Window
                                Set Variable [$PatientID ; Patients::__pkPatientID ]
                                Go to Layout [ "Drugs"  (Drugs) ]
                                New Record/Request
                                Set Field [ Drugs::DrugName ; YourTable::gNewDrugName ]
                                Set Variable [$DrugID ; value: Drugs::__pkDrugID ]
                                Go To Layout ["Patient_Drug" (Patient_Drug)]
                                New Record/Request
                                Set Field [Patient_Drug::_fkDrugID ; $DrugID ]
                                Set Field [Patient_Drug::_fkPatientID ; $PatientID ]
                                Go to Layout [Original Layout]
                             End If
                             Set FIeld [ YourTable::gNewDrugName ; "" ]