9 Replies Latest reply on Oct 25, 2012 1:42 AM by TimAnderson

    ExecuteSQL Summaries

    TimAnderson

      Hi All,

       

      I have been playing with fql for a while, using plugins before FM12, but now have come up with something that, from my reading, may not be possible! before I spend (waste?) any more time can anyone tell me if the below is possible.

       

      In the table I am looking at there are 3 fields I am interested in, ImageID, imagesize and quantity.

       

      I want to summarise the data such that I get something like below. I want to use the results in a webviewer so cannot take into another table and process. I am returning the html tags in the result, but am leaving them out here for clarity.

       

      Image 1

      10x8 3

      6x4 1

       

      Image 2

      10x8 1

      6x4 2

       

      Image 3

      6x4 2

       

      I think I am going to have to find some way of processing the results of the ExecuteSQL to get this, unless someone with a great deal more knowledge of SQL than me (Ok, that will not be difficult!) can tell me otherwise.

       

       

      Thanks

       

      Tim

        • 1. Re: ExecuteSQL Summaries
          Greg

          Good Day Tim,

           

          I would suggest you take a look at this article written by Beverly Voth  http://www.filemakerhacks.com

           

          It does an execellent job of explaining what you are trying to do.

           

          Basicly you just need to Sum by Group.

           

          greg

          • 2. Re: ExecuteSQL Summaries
            TimAnderson

            Thanks Greg,

             

            Had been through that article and it was useful, but not for this. I perhaps wasn't clear (or I missed the section in Beverly's article).

             

            I have the following rows

             

            Image ID         iSize     quantity

            Image 1        10x8         3

            Image 1                6x4                   1

            Image 2        10x8         1

            Image 2                6x4           2

            Image 3        6x4           2

             

            In FleMaker I would have a subsummary part by Image ID and show only fields iSize and quantity in the body to get the result as shown in my original post

             

            So, can the ExecuteSQL command do this? My challenge!

            • 3. Re: ExecuteSQL Summaries
              beverly

              Do you already have summary fields (total_quantity, for example)?

               

              By what are you sorting to get what you want now (totals by image?, iSize?, ??)

               

              Let (

                 [ $query = " SELECT DISTINCT images.ImageID

                    , ( SELECT SUM(ten.quantity) FROM images as ten where ten.iSize = '10x8' AND images.ImageID = ten.ImageID ), ( SELECT SUM(six.quantity) FROM images as six where six.iSize = '6x4' AND images.ImageID = six.ImageID )

                    FROM images

                    ORDER BY images.imageID "

               

                 ; $header = "Image ID" & Char(9) & "10x8" & Char(9) & "6x4¶"

                 ; $result = $header & ExecuteSQL ( $query ; Char(9) ; "" )

               

                 ]; $result

              )

               

              gives you:

              Image ID     10x8     6x4

              Image 1          3     1

              Image 2          1     2

              Image 3               2

               

              A little more complex! Perhaps you'd rather use filtered portal summaries? <http://www.filemakerhacks.com/?p=5950>

              Beverly

              • 4. Re: ExecuteSQL Summaries
                TimAnderson

                Thanks Beverly,

                 

                I think that's getting close! I do not need to total the quantities, I just want something like

                 

                Image1          10x8          3

                            6x4     1

                Image2          10x8    1

                                    6x4     2

                Image3          6x4     2

                 

                from

                 

                Image1     10x8     3

                Image1      6x4     1

                Image2     10x8    1

                Image2     6x4     2

                Image3     6x4     2

                 

                 

                where I can put in a break (in the SQL) after each image and so make it a heading. If there are 20 entries for Image1 I only want Image1 to be returned once. I am thinking perhaps a custom function might be the way to go!

                • 5. Re: ExecuteSQL Summaries
                  beverly

                  Hello, Tim! Perhaps you just need conditional formatting on a "list view"?

                   

                  <http://www.filemakerhacks.com/?p=25>

                   

                  Create the field "flag_unique" (unstored) =

                  Let ( x = GetNthRecord ( Image ID ; Get(RecordNumber) - 1 ) ;
                  
                  If ( ImageID <> x ; 1 ; "" )
                  
                  )   //   end let
                  

                   

                  Then on the layout format the text of Image ID to blend in with the background.

                  Format with conditional formatting =

                  images::flag_unique, TextColor: black, bold

                   

                  Sort the list of found records by "Image ID" to trigger the correct flag_unique.

                  Your "label" now shows once per Image ID (on the first row).

                   

                  Beverly

                  • 6. Re: ExecuteSQL Summaries
                    TimAnderson

                    Ah Beverly,

                     

                    If only this was going to be used as simple text! I am building some HTML commands with the ExecuteSQL command which is currently

                     

                    ExecuteSQL ( "Select '<B>'||i.imageName||' </B></p><p>'||t.photoSize||' '||t.quantity||' off</p>' from TreatmentImages t Left Join Images i on t.zkImageUID= i.zkUIDp Where t.zkJobUID=? and t.zkTreatmentUID=?" ; "" ; "" ; Jobs_Build::zkUIDp; Jobs_Build::zkJobTreatmentSelected )

                     

                    Which when combined with the css gives me the following in a webviewer. I really want to have another 'box' for each Image with the sizes and quantity of each size. That is 1 'box' for Flume.jpg, 1 for audio.png etc.

                     

                    Screen Shot 2012-10-22 at 21.07.25.png

                     

                    I think your answer really answers my question - I need to post-process the result!

                     

                    Thanks for your input

                    • 7. Re: ExecuteSQL Summaries
                      carlo.m

                      Hi Tim,

                       

                           I too believe the answer is post-result work. I had a relativily similar problem. I had info obtained by executesql function that I wanted to display as html on a webviewer. The problem I was tackling is I wanted the rowspan and colspan to be calculated automatically.

                       

                      So using your example with the solution I came up with, I would get:

                      Image1          10x8          3

                                  6x4     1

                      Image2          10x8    1

                                          6x4     2

                      Image3          6x4     2

                       

                      Interpreting the result for Image 1, my custom function would output:

                      <tr><th rowspan="2" colspan="1">Image1</th><td>10x8</td><td>3</td></tr>

                      <tr><td>6x4</td><td>1</td>

                       

                      You can find the discussion here: https://fmdev.filemaker.com/message/96032#96032

                      where I posted the commented custom function.

                       

                      Hope it helps...

                      Let us know how you finally resolve it!

                      • 8. Re: ExecuteSQL Summaries
                        TimAnderson

                        Thanks Carlo, will be looking at it tomorrow

                         

                        Tim

                        • 9. Re: ExecuteSQL Summaries
                          TimAnderson

                          Hi Carlo,

                           

                          I have exactly the result I was looking for thanks to the genius of Agnès Barouh and her amazing CustomList custom function ( http://www.tictac.fr/CoinFileMaker/PageCustomList.html ).

                           

                          I now have

                          status.png

                          which scrolls beautifully as a webviewer

                           

                          To get this I took the SQL result ( sorted in the SELECT statement) and ran it through a custom function I modifed from her UniqueList cf as below.

                           

                          Fairly easy thanks to the genius of Agnès!

                           

                          Hope this helps some others

                           

                          Tim

                           

                          //-------------------------------------------------------------------*/

                          // Based on the UniqueList functon by Agnès Barouh - Juillet 2007 -

                          //-------------------------------------------------------------------*/

                           

                          // ParseHTMLForSummaryHeaders( TheList )

                           

                          Let(

                           

                              $_TheList = TheList ;

                           

                              CustomList( 1 ; ValueCount( $_TheList ) ;

                                  "Let( [

                                      TheValue = GetValue( $_TheList ; Num );

                              headerEnd=position(TheValue;\"</B>\";1;1);

                          lVal=length(TheValue);

                          thePrefix=left(TheValue;headerEnd);

                                        theRest=right(TheValue;(lVal-headerEnd)-3)

                           

                                      ] ;

                                      Case(

                                      PatternCount( $_Value ; thePrefix ) < 1 ;

                                      Let ( $_Value = $_Value & \" \" & TheValue  ; TheValue );

                          Let ( $_Value = $_Value & \" \" & TheValue  ; theRest )

                          ))"

                                  )

                          )

                          & Let( [ $_Value = "" ; $_TheList = "" ] ; "" )