9 Replies Latest reply on Nov 16, 2012 9:39 AM by philmodjunk

    Relationships

    tays01s

      Title

      Relationships

      Post

           I'm not quite getting them! See attach diagram.

           Ignore 'Front'. I'm just using this as an index of buttons to go to other layouts. I assume it could simply be a layout and doesn't need a separate table (I was hedging my bets!).

           Main point:

           - Patients: I will have several falling into 'active', 'non-active' and 'all' filter categories.

           - Prescription: Several per patient.

           - Records: Several per Prescription.

           Records has a lot of fields arranged in a column next to the related prescription column. For this reason, I was aiming to have a pop-up of patient names on Records. Once a patient name was chosen a pop-up next to it would be filled with the prescription dates for that patient (ie. contextual).

            

           However, these pop-ups aren't populating despite apparent relationships and use of value lists from related tables.

           a) I assume I'm getting something fundamentally wrong re. relationships; what?

           b) FM solutions commonly appear to pull child table data onto 'parent layouts' using portals. I've kept to an individual layout per table because I was attempting to stay within iPhone size layouts and 'Records' has many fields and field names are relatively long compared to the numeric data entry. Is there a better solution.

      Relationships.png

        • 1. Re: Relationships
          philmodjunk

               The issue would seem to center around Prescribe and it's relationships--the very part that you don't explain in your first post.

               What is the purpose of this table? (I'd think that each record would represent a drug or therapy that can be selected as a prescription for a patient, but then the link to Record makes no sense.)

               Why does it link to Patient_Prescribe by Date_Modified?

               Since any number of records in Patient_Prescribe can have the same date and the same is true for Prescribe, I don't see how this can be of use to you.

          • 2. Re: Relationships
            tays01s

                 Each Patient may have several Prescriptions. The Prescription may change after 1 or more days, hence knowing the modification date is important. However, the Record of drug given is daily. So you might have for a single Patient, 2 Prescriptions: a) lasting 1 day (ie. 1 prescription, 1 record), b) lasting 3 days (ie. 1 prescription, 3 records).

                  

                 I had assumed that if you choose a patient while on Record, you would need the Prescription dates for that patient to be pulled up in a field beside it. And to obtain this contextual value list I'd need another table occurance where the Prescription dates would be listed for the Patient chosen. However, I admit, I'm not quite how to use relationships !

            • 3. Re: Relationships
              philmodjunk

                   Knowing the modification date IS important, but it makes no sense to use like you have as a match field in a relationship. It will match to many records for other patients that happen to have the same date.

                   What does one record in Prescribe represent?

              • 4. Re: Relationships
                tays01s

                     1 record in Prescription is the drug/dose for 1 patient on that day. As mentioned the Prescription may be used for 1 or more days and for each day the actual delivery of drug and dose will be recorded (in Record; I'm probably better using the term 'Input' so as not to confuse with the generic term 'record').

                • 5. Re: Relationships
                  philmodjunk

                       Sorry to keep asking questions like this but by answering them, I get sufficient info to explain what you need to change to get this to work.

                       If one record in prescription is the drug/dow for 1 patient "on that day", what purpose is served by the Patient_Prescribe table?

                       And with your relationships as they are currently set up, there is no link from Prescribe to a specfiic record in Patients.

                       It sounds lik you should have:

                       Patients-----<Prescribe------<Records
                       Patients::PatientID = Prescribe::PatientID
                       Prescribe::PrescribeID = Records::RecordID

                       Patient_Prescribe is labeled and set up in relationships as though it is the join table in a many to many relationship between Prescribe and Patients, but if one record in Prescribe is one dose of one prescription for one patient on a specific date, then you do not have a many to many relationship.

                       I think you DO have the need for a Many to Many relationship, but not for the tables as you have currently set them up. If you had a table that served as a master list of drugs--one record for each drug that might be prescribed, then you would have use for a join table in a many to many relationship between Patient and such a master list table.

                  • 6. Re: Relationships
                    tays01s

                         I've put back the relationship diagram to that you suggest. How would I use this to get value lists for popups: Patient (selection) and Prescription (selection based on what Patient is chosen). I'd planned to put these on 'Record' because input is a long list needing portrait layout so a portal wouldn't easily fit if I instead inserted one on a Patient tab to draw data from Prescription and Record. However, if there had been space, would the latter have been better?

                          

                         Your comment on the end: Are you suggesting instead having: Patient < Join table > Drug master list?

                         I take it there would still be Patient < Prescription < Record (Input) relationships.

                    • 7. Re: Relationships
                      philmodjunk

                           You may not actually need any pop up for selecting a patient for a given record. In fact, if there is any pop up, it would be to select a record from Prescribe, which in turn selects the patient via the relationship between prescribe and patient. You have a number of options.

                           You can place a portal to Prescribe on your patients layout. A second portal, linked via scripts and an additional relationship can allow you to create linked records in the Records table directly in the portal.

                           As to my last comment, I can't tell you what you need as I am not the one using the database. IF you need a master list of all medications that could be prescribed with data about each such medication recorded. Such a list could be linked to your patient with a join table as you would have many patients receiving the same medication and a given patient might receive multiple medications--which produces the "many to many" relationship that requires such a "Join" table. If you find that you need that, your current Prescribe table could easily be set up as such a join table by linking it to the master list table.

                      • 8. Re: Relationships
                        tays01s

                             OK, thanks.

                             Re. portals, I can see how they could deal with my need, but can they be set up with fields as rows, records as columns. This would fit the screen I'm planning for but I've never seen a portal like this.

                        • 9. Re: Relationships
                          philmodjunk

                               Not with a single portal.

                               You can put in a row of one row portals and resize the portal row to be several fields tall so that you can arrange your fields from one record in a column. The first portal would specify "initial row 1". The portal next to it: "initial row 2" and so forth. This can create a few design issues for your layout that require some scripting to handle.