9 Replies Latest reply on Oct 4, 2012 2:08 PM by philmodjunk

    Creating Queries using relationships

    XavierLapointe-Gagner

      Title

      Creating Queries using relationships

      Post

           I manage a database of patient files for a private clinic. I needed to create queries so that users could see a list of new records they had not reviewed yet.

           The architecture is relatively simple, with a central patient file related by  a one-to-many relationship to different kinds of record tables, i.e. medical records, lab tests, radiology, patient visits, etc. I'll call these peripheral record tables, as oppose to the central patient table.

           For every peripheral record table, I created a field "UsernameViewed" for every user.

           I created a new table titled "UserViewed", and established a one-to-many relationship with the central patient table.

           By creating a single record in the "UserViewed" table and linking it to every record in the central patient table, I was then able to create portals for every different peripheral record table, with filtering based on whether the "UsernameViewed" field = to "Yes". Thus, if the field was empty, the record would show in the portal. I added the "UsernameViewed" field in the portal so that users could simply click a checkbox "Yes" after having viewed the record, which would then no longer appear in the portal...

           The solution worked beautifully. Every user has their own "UserViewed" table that links to all the records in the central patient table, and thus can see only the records that they have not checked off as "Viewed".

           There is one big problem: the information I am displaying in the portals are fields from the original peripheral record along with the patient name field found in the central patient table. For some reason, the associated patient name is not showing up. 

           In the screenshot, you can see the patient name "Penny" being displayed, but that is not the name of the patient associated with each of those records...

           How can I fix this problem? 

           Thank you in advance for any help! This has been buggin me all day...

           Xavier Lapointe-Gagner

            

            

      Screen_Shot_2012-10-04_at_3.42.44_PM.png

        • 1. Re: Creating Queries using relationships
          philmodjunk

               We need to see the actual relationships. Can you upload a screen shot of Manage | database | relationships and then identify which table occurrence "boxes" are being referenced in the above screen shot? (which is is specified for the layout, which for the portal, and which for the fields that appear in the portal's row...)

          • 2. Re: Creating Queries using relationships
            XavierLapointe-Gagner

                 you can see the central patient table in the center, the peripheral record tables around the central patient table, and a single "UserViewed" table on the right. 

                  

                 UserViewed "1-many" PatientTable "1-to-many" PeripheralRecords

                  

                 Thats how the architecture goes...

            • 3. Re: Creating Queries using relationships
              XavierLapointe-Gagner

                   So in the above screenshot of the portal, the first field is in the center patient table, while the other fields are fields in the periphary record table, which is where the actual record is.

              • 4. Re: Creating Queries using relationships
                philmodjunk

                     There is no table occurrence named "user viewed". I find one named DrViewed. Is your layout based on that table?

                     And then the portal is to "Patient File"?

                     I don't think that's what you have here.

                      I believe that in the screen shot shown, in the portal that is visible, you have a portal to "Lab Tests". In layout setup for this layout, is "Patient File" or "DrViewed" selected in the "show records from" drop down?

                • 5. Re: Creating Queries using relationships
                  XavierLapointe-Gagner

                       By "Userviewed" I meant any user, its actually the name of the user that takes up the "user", so DrXViewed for the user Dr. X, I just wanted to make my explanation simpler.

                       I only included one of the "UserViewed" tables, and changed the name so that it would display "DrViewed" in the relationship diagram I sent you.

                        

                       The original portal screenshot is a portal to one of the peripheral records table, the "LabTest" table. This table is related to the central patient table, which is then related to the DrViewed table.

                       In the layout setup for this layout, it shows records from the DrViewed table. 

                  • 6. Re: Creating Queries using relationships
                    XavierLapointe-Gagner

                         In the layout setup, it must show records from the DrViewed table in order to show all of the lab tests from all the patients that have not been viewed yet. If the layout showed records from the central Patient table, I would not be able to see all of the lab tests of all of the patients, but only the lab test related to a single patient.

                    • 7. Re: Creating Queries using relationships
                      philmodjunk

                           Just making sure I understand what you have. It's important for me to know exactly what you have before I can be sure what I am about to suggest will work for you.

                           For the portal shown in the screen shot, the table occurrences in use are:

                           DrViewed-----<Patient Files-----<Lab Tests

                           To get data from patient files to display correctly in the portal, you need to add an new occurrence of Patient Files. Select Patient Files in Manage | Database | Relationships by clicking it. Then click the duplicate button (2 green plus signs). Double click the new occurrence to open up a dialog where you can rename the occurrence to make it: Lab Tests PATIENT FILES. Link this new occurrence of Patient Fiels to Lab Test to make it:

                           DrViewed-----<Patient Files-----<Lab Tests>----Lab Tests PATIENT FILES   

                           Use the same two match fields in this relationship you used in the Patient Files to Lab Tests relationship. Now add any fields you need (such as the patient name) to yoru portal row from Lab Tests PATIENT FILES instead of Patient Files.

                           I don't think adding a table and a layout for every doctor will prove to be a practical approach here. A single DrViewed table where you have one record for each doctor should make for a much more flexible approach here.

                      • 8. Re: Creating Queries using relationships
                        XavierLapointe-Gagner

                             Your solution worked fantastically!

                             What is the reasoning behind the solution? (Just in case I have to apply it in the future!)

                             Thanks again for the help!

                             Xavier Lapointe-Gagner

                        • 9. Re: Creating Queries using relationships
                          philmodjunk

                               Everything on your layout evaluates from the context of the current record in the layout's table via the layout's table occurrence (DrVisit).

                               Thus any fields from Patient Files, whether in the portal or not, all refer to the first record in Patient Files that is related to the current DrVisit record. By adding in an occurrence 'downstream' from the portal's table occurrence, the relationship between the portal row record to then new occurrence of Patient Files determines what record supplies data to the field.