8 Replies Latest reply on Jun 23, 2014 1:22 PM by liyangao

    How to show the chosen child record on parent record

    liyangao

      I have a database with patient and multiple visits and multiple ImageA, ImageB and ImageC table. The patient visit was divided into 3 visit types: baseline, followup1 and followup2. For each visit type, I need to choose a combination of ImageA, ImageB and ImageC across different visits. Each patient will have their overall review for each visit type, which combines the individual review of ImageA, ImageB and ImageC and gives overall review of diagnosis.

       

      My first question is how to choose a group of ImageA, ImageB ad ImageC across different visits within the same visittype? I tried to use the last() function, but it will not distinguish visit types.

       

      The second question is how to show the right child record? On the overall review layout, I would like to show the corresponding review of ImageA, ImageB and ImageC for reference purpose. If I put the fields from table ImageA, table ImageB and table Image C directly on the overall layout, it will show the first record of the ImageA, ImageB, and ImageC correspondingly. Since I might have multiple ImageA for the same visitType, how do I show the correct record on the overall review layout? There are multiple fields to show at ImageA, ImageB and ImageC. Do I have to use portal for each field? How do I identifiy the combination of ImageA, ImageB and ImageC?

       

      Thank you for your help,

      Liyan

        • 1. Re: How to show the chosen child record on parent record
          Mike_Mitchell

          Hello, Liyan.

           

          Your first question can be answered in basically one of two ways. You can use either a filtered portal, where you set the visit type = the visit type you want (presumably another field, or a global field selected by the user), or a calculated secondary relationship using the same criteria. The choice will depend on exactly what you want to accomplish. A filtered portal is intended for display; it doesn't alter the underlying relationship, so (for example) the List ( ) function will still show all related records. A relationship that incorporates the additional criteria won't do that - it'll filter the results properly, no matter what you do - but it does involve the creation of another table occurrence on your Relationships Graph and the attendant overhead.

           

          You can also use something like ExecuteSQL to fetch the records you need, but that does require at least a basic knowledge of SQL. Of course, that might be very comfortable for you.   

           

          To your second question, yes, you will likely need to use a portal to show multiple related records. I'm not entirely sure what you're asking about the "correct" related record; a single related field will be from the first related record, which will always be either the oldest record or the first record in the relationship's sort order, depending on whether the relationship is sorted or not. But if you have multiple records to display, then a portal will, in most cases, be necessary. (There are other solutions, such as a separate window using a List view, but they're a bit more effort to set up.)

           

          As to how you identify the combination of ImageA, ImageB, and ImageC, I can't answer without knowing more about your setup. Are these images all in the same table? How are they related to the parent record? What criteria do you use to establish which records are the "correct" records? We'd need to know more in order to render useful advice.

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: How to show the chosen child record on parent record
            liyangao

            Mike,

            Thank you for your prompt reply.

             

            Here is my setup. The patient table has patient demogaphic information. A patient is allowed to hae multiple visits identified by patID, visitType and visitDate. At each patient visit, an image review form is generated by sript ONLY if the image is taken on the visit date, which is identified by patID, visitType and Image Date for imageA, imageB and imageC respectively. At each visit, there might be no image, 1, 2, or 3 images taken. The Patient, Visits, ImageA, ImageB and ImageC are in different tables respectively. In each Image table, there is a field to indicate whether the individual image has been reviewed.  The condition is that if in the same visit type, all 3 images ImageA, ImageB and ImageC have been taken and reviewed then an overall review form will be generated by script, which is identified by PatId, visitType, ImageA_Date, ImageB_Date, and ImageC_date.  So it is possible there are 0, 1, 2 or 3 set of 3 Images at different visitType for the same patient, how can I identify those combinations. Patient table is directly 1-to-m related to visits and overall review tables. Visits table is directly related to ImageA, ImageB and ImageC table. I can not find a good function to capture this combination.

             

            I only know some basic SQL and havenot used ExecuteSQL before. If I cannot figure out filtered portal, I will try ExecuteSQL.

             

            Thank you for your insightful points,

            Liyan

            • 3. Re: How to show the chosen child record on parent record
              erolst

              liyangao wrote:

               

              The Patient, Visits, ImageA, ImageB and ImageC are in different tables respectively.

               

              This is the source of your problems. Why not use a single table instead of three tables with identical structure? This gives you this structure

               

              Patients --< Visits --< Images

               

              where you use a category field to distinguish between type A, B and C, and a Boolean field to set the review status, e.g. Images::reviewed?

               

              Use a script to avoid having the same image type multiple times, i.e. after type A has been added to a visit, only allow B and C etc.

               

              A calc field in Visits, like Count ( Images::reviewed? ) = 3, allows you to perform the desired check; but you could as well dispense with that field and check for this condition whenever you change the reviewed? status for an image; then, if applicable, create the review form.

              • 4. Re: How to show the chosen child record on parent record
                Mike_Mitchell

                Indeed. Not only does this dramatically simplify your life, it also leaves open the possibility that you might, at some point in the future, decide you want a fourth image - or more.

                • 5. Re: How to show the chosen child record on parent record
                  liyangao

                  But the three images are different and the three review forms have different structure/fields, which is why they are in different tables. So it is too much to juggle with...

                  • 6. Re: How to show the chosen child record on parent record
                    erolst

                    liyangao wrote:

                     

                    But the three images are different and the three review forms have different structure/fields, which is why they are in different tables. So it is too much to juggle with...

                     

                    As long as the Reviews aren't too different, why not create a table that has all fields you need for each type? (You should know when it's time to throw the normalization book out of the window…).

                     

                    Or use a Review table that has all the fields that are common to all Reviews, and an additional table to store the additional/specific data as records (based on a blueprint kind of definition; search for 'Surveys' to see how this works).

                     

                    The thing is that from a certain perspective/context, you should be able to treat Images and Reviews as one type of entities, regardless of the differences they may have from another perspective. It's usually easier to deal with a number of different attributes of what is basically the same thing, than to deal with different entities that are actually the same one.

                     

                    btw, even with your current setup, all you need is a calculation field like

                     

                    Count ( ImagesA::reviewed? ; ImagesB::reviewed? ; ImagesC::reviewed ) = 3

                     

                    – provided that you use aBoolean (number) field 'reviewed? ' –

                     

                    and three relationships from Visits to the respective Images table, where you only show one row – since by your rules, each table can only have a single image for a visit. (Or a filtered portal; but since each Images table holds all Images of that type for all visits, this may be too slow).

                     

                    Notice how you need to do everything thrice in this setup … and btw, I have a feeling of déjà vu (Visits, Images, count of 3 …) – did you ask this question before?

                    • 7. Re: How to show the chosen child record on parent record
                      erolst

                      See if the attached sample file helps you. It outlines some of ideas described in my posts.

                      • 8. Re: How to show the chosen child record on parent record
                        liyangao

                        Erolst and Mike,

                         

                        Thank you so much for your responses and for showing me the same file for the ideas.With your help,  I am able to use a filtered protal to show the corresponding record and check the all 3 images for completed review.

                         

                        We did had all image review on the same page and in the same table at the beginning then switched to individual review table/form later to better model the work flow. Thanks for the lesson on the normalization.

                        Thank you,

                        Liyan