10 Replies Latest reply on May 21, 2014 12:30 PM by philmodjunk

    Relational Database Question - Patients, Prescriptions and Pharmacies

    luciano991

      Title

      Relational Database Question - Patients, Prescriptions and Pharmacies

      Post


           Hello,

           So I made a project for a doctor's office and it came out quite well I think. I'm pretty new at FMPro, but the client liked it.

           Anyway, I have 3 tables: patients, visits, prescriptions.

           I have established relationships: one patient can have many visits; one patient can have many prescriptions.

           But now I have to add the name of the Pharmacy the patient uses to the patient record. I am assuming that one patient can have only one pharmacy. This information can be modified of course if the patient changes pharmacies. But a patient should only have one.

           A prescription could be filled at many pharmacies. But  prescription records are related to the patient through a portal on the form.

           I don't want to maintain a table of all the pharmacies in a given area.

           My solution is to add the pharmacy name, address, phone, fax etc. to the patient record and then display those fields on the prescriptions tab of the form. I know that one pharmacy can be used by many patients so there will be duplication of pharmacy records but my approach seems the most practical to me at this point.

           Can anyone suggest a better way.

           Thanks,

            

           Mark

        • 1. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
          philmodjunk

               I'd put that name and address into the table of pharmacies you don't want to create. Otherwise, you have to enter that data over and over again for each new patient instead of just selecting the Pharmacy from a value list and seeing the current contact info for that Pharmacy automatically appear.

               You could, in theory, set up a value list and data look up that pulls this data from your patients table, but this makes managing that value list of pharmacies (say the contact info for a pharmacy needs to be changed) much more problematic to do.

          • 2. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
            luciano991

                 Phil,

                  

                 Thanks. So I will make a table called pharmacies with the fields name, address, city, state, zip, phone, fax, email. It will have a primary key, of course, but would the foreign key be the primary key from the patient table? And would this be a one to one relationship?

                 Secondly, would I drag the fields from the pharmacy table onto the prescriptions tab of the layout (I have 3 tabs in the layout: patients, visits and prescriptions.) and then make a dropdown that would list all the pharmacies and select one from the list?

                 Third, should there be a button for entering a new pharmacy in the table if the pharmacy the doctor wants is not listed.

                 Thanks for your patience. Sometimes it's tough to figure out the right questions to ask. :-)

                  

                 Mark

            • 3. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
              philmodjunk

                   This would be a many to one relationship. Many patients to one pharmacy.

                   Patients::_fkPharmacyID = Pharmacies::__pkPharmacyID

                   With that relationship, you can add fields from Pharmacies to the Patients layout.

                   _fkPharmacyID can be formatted with a drop down list or popup menu of pharmacies where the value list enters the Pharmacy ID, but displays the pharmacy name.

                   And you might want to consult with your client to make sure that you are correct to assume that all patients will have exactly one preferred pharmacy. Otherwise, you may want to set up a join table between Patients and Pharmacies.

              • 4. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
                luciano991

                     OK, cool. I'm going to give that a go.

                     One clarification. It's not that all patients will have one preferred pharmacy it's that each patient will have a preferred pharmacy.

                     Thanks,

                      

                     Mark

                • 5. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
                  philmodjunk

                       Fair enough. I've tweaked several posters over inexact language today so it's only fair to receive the same back from others. cool

                  • 6. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
                    luciano991

                         Well, no, I wasn't breaking your chops, I was just making sure it didn't change your answer. :-). Thanks again for the help.

                          

                         Mark

                    • 7. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
                      luciano991

                           OK, so now to summarize I have a layout with three tabs: Patients, Visits, Prescriptions. I have created the table for Pharmacies that you suggested and I have created the relationship between that table and the Patients table as you advised. And I pulled the fields from the Pharmacies table onto the Prescriptions tab. That's all looking good.

                           Now I want to create a button that will allow the doctor to add a pharmacy to the table. Here is what I have for adding a new visit:

                           Freeze Window
                           Set Variable [ $PatientID ; value: Patients::PatientID ]
                           Go to Layout ["Visits" (Visits) ]
                           New Record/Request
                           Set Field [Visits::PatientID ; $PatientID ]
                           Go to Layout [original layout]
                           Go to Object ["VisitTab" ]

                            

                           Here's my best guess for a script to create a new Pharmacy record:

                           Freeze Window
                           Set Variable [ $PatientID ; value: Patients::PatientID ]
                           Go to Layout ["Prescriptions" (Prescriptions) ]
                           New Record/Request
                           Set Field [Pharmacies::PatientID ; $PatientID ]
                           Go to Layout [original layout]
                           Go to Object ["ScripsTab" ]

                           Am I close?

                           Mark

                      • 8. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
                        philmodjunk

                             I think you need Go to layout ["Pharmacies"

                             Not Go to Layout ["prescriptions"

                             And here's a radical trick I didn't even know worked til last year:

                             If you enable "allow creation of records via this relationship" for Pharmacies in the Patient to Pharmacy relationship, you can enter data into a blank field from Pharmacies (any field except the match field) and FileMaker will a) create a new record in Pharmacies and b) update the match field in Patients with the ID of the new Pharmacy record just created.

                             Normally we do this in reverse, create a new record in the table on the "many" side of the relationship via this method and then see a new record automatically linked by copying the current primary key from the current layout record into the foreign key of the new child record.

                             But this also works to create a new record in the Parent table linked to the current record in Child table when done from a layout based on the Child table.

                        • 9. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
                          luciano991

                               OK, here's what I have now:

                               Freeze Window
                               Set Variable [ $PatientID ; value: Patients::PatientID ]
                               Go to Layout ["pharmacies" (pharmacies) ]
                               New Record/Request
                               Set Field [pharmacies::_pkPharmacyID ; $PatientID ]
                               Go to Layout [original layout]
                               Go to Object ["ScripsTab" ]

                               I click add a Pharmacy but nothing happens. In other words I don't get a new form.

                               But wait, he says. Of course you don't because each patient can only have one pharmacy. So I don't need no button after all. If the patient is a new patient his Pharmacy fields will be blank. If he's an existing patient who wants to change his pharmacy the doctor can just put in new information.

                               So now I need to understand how to fix up a drop down menu listing all the pharmacies in the pharmacies table so that when the doctor has a new patient he just drops down the list, selects a pharmacy and the form is populated. I think you alluded to this in a previous reply but I'm still not sure how to fix up that dropdown and I have another post going in another thread about doing a drop down on the Patients tab to do the same thing so the doctor doesn't have to go into Find mode to look for another patient.

                               Thanks in advance.

                                

                               Mark

                          • 10. Re: Relational Database Question - Patients, Prescriptions and Pharmacies
                            philmodjunk

                                 This script isn't correct for adding anew Pharmacy. No script should ever need to modify a __pk field except in the most dire circumstances. you need to copy the _pk Id from Pharmacies back to Patients. (Sorry for not seeing that earlier).

                                 Freeze Window
                                 Go to Layout ["pharmacies" (pharmacies) ]
                                 New Record/Request
                                 Set Variable [ $PharmacyID ; value: Pharmacies::__pkPharmacyID ]
                                 Go to Layout [original layout]
                                 Go to Object ["ScripsTab" ]
                                 Set Field [Patients::_fkPharmacyId ; $PharmacyID ]

                                 For your value list of pharmacies:

                                 Open Manage | Value Lists
                                 Create a new value list with the "use values from a field" option selected
                                 Select __pkPharmacyID from Pharmacies as the value from the first field.
                                 Click the "also display values from" option
                                 Select the Pharmacy Name field as "field 2"
                                 Select the "Show only value from second field" option.

                                 Format the Patients::_fkPharmacyID field with this value list--either as a drop down list or pop up menu.

                                 Note this requires that the Pharmacy name field be unique for each pharmacy. Given the many chains out there, it's quite possible to have multiple Rite Aids or Walgreens for different locations in your table. You may need to set up a description field that combines the name of the pharmacy with additional info such as the street on which it is located for use in your value list.