6 Replies Latest reply on Mar 5, 2016 11:14 AM by beverly

    Summarizing data in portals

    dsimonson

      I have a layout based on the Anesthetic table where I want to create portal showing each medication given and total for that medication.

      On the layout, the anesthetist enters individual medication observations having AnesID (from the anesthetic), MedID (from the medications table), amount, and time.  These are entered into a medication observation table that is related by AnesID.

      I then display these observations from within a portal:

      Time   Medication   Amount

      8:35     fentanyl     50ucg

      8:45     fentanyl     50ucg

      9:00     propofol     100 mg

      9:15     propofol     50 mg

      This is easy!  But what I want to do is create another portal that will show only one entry per medication, and a summary of the total amount given during the case.

      Medication TotalAmount

      fentanyl     100ucg

      propofol     150 mg

      I tried creating a self-join of the MedObs table that looks like this:

      Anesthetic -> MedObs -> MedObsSummary, but it isn’t working.  Any ideas?  Is this something I could do with a SQL calculation? Thanks.

        • 1. Re: Summarizing data in portals
          user19752

          Bad thing is you use unit in Amount field, so you can't summarize it. You can get only number as 100 or 150, but "ucg" and "mg" will be lost.

          • 2. Re: Summarizing data in portals
            dsimonson

            I figured out how to do it using ExecuteSQL!  I have appended the calculation that I created using Soliant's Query Builder.  It works great in the Query Builder layout and in the data viewer - but when I created a calculation field in my layout table with the calculation equal to what you see below, I get nothing!  The field is empty.

             

            Any ideas on what I'm doing wrong?

             

            Let ( [

            query = "

            SELECT m.pk_med_name_generic

            , SUM (o.med_amt)

            , m.med_units

            FROM ane_ObsMeds o

            JOIN meds m

            ON o.fk_med_id = m.idMedication

            WHERE o.id_anesthetic = ?

            GROUP BY o.fk_med_id

            , m.pk_med_name_generic

            , o.med_units

            " ] ;

            ExecuteSQL ( query ; "  " ; "" ; $$AnesId)

            )

            • 3. Re: Summarizing data in portals
              beverly

              1. the $$ variable may need to be 'cast'

              GetAsNumber($$AnesId)

              to be sure the proper quoting is applied

               

              2. you don't need to GROUP BY o.fk_med_id, since it's not used in the SELECT. Only non-aggregate columns need to be listed

               

              3. you can ORDER BY (sort) after the GROUP BY, if that would help

               

              4. you only have two parts to the Let(), so the "[" & "]" aren't needed

              https://www.filemaker.com/help/14/fmp/en/html/func_ref1.32.196.html

              Let ( var=expression ; calculation )

               

              HTH,

              beverly

              • 4. Re: Summarizing data in portals
                dsimonson

                Thanks for the tips, Beverly - but it still doesn't show up in my field.  It works in the data viewer and gives exactly the results I want - but when I paste the same calculation into a new calculation field and put it on my layout, it is simply blank.  Here it is, now that I have cleaned it up as you suggested (I don't think $$AnesID needs to be cast, it is a text variable):

                 

                Let (

                query = "

                SELECT m.pk_med_name_generic

                , SUM (o.med_amt)

                , m.med_units

                FROM ane_ObsMeds o

                JOIN meds m

                ON o.fk_med_id = m.idMedication

                WHERE o.id_anesthetic = ?

                GROUP BY m.pk_med_name_generic

                , m.med_units

                ORDER BY m.pk_med_name_generic

                " ;

                ExecuteSQL ( query ; "" ; "" ; $$AnesId)

                )

                 

                Could I put this into a script and then have the results plunked into a field?  I'm going to try that.  Then I could have a script trigger run the script each time a new medication observation is entered, and have it update the new field.

                 

                I'll let you know if that works!

                 

                Dan

                • 5. Re: Summarizing data in portals
                  dsimonson

                  It worked!  I added it to the scripts governing medication data entry, and now it runs and then dumps the result into a field for me.  Yay!

                   

                  Now I just have to format the field make it look prettier.

                  • 6. Re: Summarizing data in portals
                    beverly

                    Yes, I try to script this kind of thing and then Set Field. I'm glad you got it working.

                     

                    beverly