8 Replies Latest reply on Dec 3, 2013 6:15 PM by sicoa

    How to summarize information from a 1-to-m relationship automatically?

    liyangao

      I am working on a database with a table for patients and a related table for patient visits at clinic, where the relationship is 1-m. On the patient visit table, I have three variables to record whether certain iamge was taken at the visit as Yes/No. I call the three variables as Img1, Img2, and Img3. A patient might get all the three images done at one visit or multiple visits.

       

      My question is how to find those patients who havenot taken all the three images across multiple visits from the database? I tried a portal of the visits on the patient layout but I can not do a direct search to find those who havenot taken all the three images across their visits. It seems that I need some aggregate function to summarize the data but I did not find an appropriate function to use.

       

      Your input and suggestions on answering the question is highly appreciated.

      Thanks a lot,

      Liyan

        • 1. Re: How to summarize information from a 1-to-m relationship automatically?
          Malcolm

          My question is how to find those patients who havenot taken all the three images across multiple visits from the database? I tried a portal of the visits on the patient layout but I can not do a direct search to find those who havenot taken all the three images across their visits.  It seems that I need some aggregate function to summarize the data but I did not find an appropriate function to use.

           

          The issue you are facing is that you cannot find what is not there. If a second or third record has not been created it doesn't exist so you can't work with it.

           

          You have to work with what does exist then use logic to make inferences.  Here's one way.

           

          Create an unstored calculation field in the patient record which counts images :  count(images::_id)

           

          An alternate method would be to use a script to push that value into a number field whenever an image is added or removed.

           

          You now search that field for values less than three.

           

          Malcolm

          • 2. Re: How to summarize information from a 1-to-m relationship automatically?
            JaredHague

            say your visits table is Visits and patients is Patients

            in the Visits table..

            assuming the img1, img2, img3 are fields and they have auto enter No.  Then you could have a calculation that does something like this...

            let call this field "allImages"

            Let ([

            image1 = case ( img1 = "Yes" ; 1 ; 0 );

            image2 = case ( img2 = "Yes" ; 1 ; 0 );

            image3 = case ( img3 = "Yes" ; 1 ; 0 )

            ];

            image1+image2+image3/3

            )

            so "allImages" would be 1 if its a yes 3x

             

            then in your paitent table have a calc like

            Evaluate ( Substitute ( List ( Visits::allImages ) ; "¶" ; '+" ) = ValueCount ( List ( Visits::PrimaryKey ) )

             

            if its true they have all the visits

             

            hope this helps

            • 3. Re: How to summarize information from a 1-to-m relationship automatically?
              DavidJondreau

              You can do this with a script.

               

              From the Patients table:

               

              Enter Find Mode[]

              Set Field [ img1 ; "yes" ]

              Perform Find[]

              Constrain Found Set [find ; img2 ; "yes" ]

              Constrain Found Set [find ; img3 ; "yes" ]

              #Now you got all the records with a "yes" in all three fields

              Show Omitted Only[]

              • 4. Re: How to summarize information from a 1-to-m relationship automatically?
                ariley

                There are lots of ways you can do this, which is the beauty of FileMaker. 

                 

                The quick and dirty method is always a calculation which in return weighs your database down as time goes by.

                 

                So the best is to have a field called img_count and add a 1 to it every time a new image gets added via script. Then you can easily search on this by entering <3 when you need to.

                For beauty you can also add another field that would display the status:

                If ( img_count > 3 ; "unfulfilled"; "fulfilled" )

                 

                And then add conditional formatting to show the text in green if it's fulfilled. Or red if it isn't.  

                 

                Best regards,

                 

                Agnes Riley

                ZeroBlue

                • 5. Re: How to summarize information from a 1-to-m relationship automatically?
                  jetalmage

                  Sounds to me like you should further normalize the solution by having a separate table for Images. That table would have fields for:

                  • A unique ImageID primary key
                  • The image (container)
                  • A Patient foreign key
                  • A Visit foreign key
                  • ImageType
                  • ImageDate

                  etc., etc.

                   

                  The relationship between Visits and Images would allow for Image records to be created from Visits.

                  Then you could have three calc fields on Patients (ImageTypeA, ImageTypeB, ImageTypeC) to indicate whether any related records exist in Images having the corresponding ImageType.

                   

                  James

                  1 of 1 people found this helpful
                  • 6. Re: How to summarize information from a 1-to-m relationship automatically?
                    LyndsayHowarth

                    I'd consider Malcolm's suggestion (with the detail from James).

                    Agnes's solution is manageable and fine if there are only a few options...as in this case. It is used as a technique in some of the starter solutions to eg. track inventory and I see people get into muddles with it all the time. The problem is that it relies on a snapshot number that has no context in terms of the transactions it relates to. If the increment gets interupted, you don't know and have no other checks in place to ensure it is right. It does have the benefits Agnes cites, though. Malcolms solution is in a dynamic state where you can actually see the image records it counts but when many records are involved can bog things down.

                     

                    - Lyndsay

                    1 of 1 people found this helpful
                    • 7. Re: How to summarize information from a 1-to-m relationship automatically?
                      liyangao

                      Thank you all for the reponses and answers. I really appreciate your help!

                      Liyan

                      • 8. Re: How to summarize information from a 1-to-m relationship automatically?
                        sicoa

                        Patients have m Visits which have n Images potentially without the need to store the patients pk within the Image itself (however that s something you can add if required).

                         

                        I would also use simple List and Count functions and Portals to aggregate the data and display it as required.

                         

                        See attached example file. Two layouts to look at Patients and Visits. You can simply do a find onthe Number of Images taken field within the Patient Layout to see who has no images or 1, 2 and 3 images.