11 Replies Latest reply on Nov 18, 2013 5:57 PM by DerekPopovich

    Making multiple series with Perform Find

    DerekPopovich

      Title

      Making multiple series with Perform Find

      Post

           Hi, 

           Is it possible to make 2 Perform Find Scrips to work in the same chart as series 1 and series 2? 

           I have 2 button that changes the found set that are charted (line graph) based on what button is press (each button executes the same Perform Find except that the date Year is different),

           Button one will run a perform find for records which contain dates for 2013 and their total value is calculated in a summary field.

           Button two perform the same Perform Find but for dates in 2012 and their total value is calculated in the same summary field as button one.

           I want to be able to have them both display in the same chart to compare how is 2013 doing in comparison to last calendar year (2013). The Line Chart is Summary value vs date.

           Sincerely, DPP

            

            

        • 1. Re: Making multiple series with Perform Find
          philmodjunk
               

                    Is it possible to make 2 Perform Find Scrips to work in the same chart as series 1 and series 2?

               No. You can only have one found set with any given layout in any given window. The second found set produced by the second script will replace the first.

               But it may be possible to use a relationship or ExecuteSQl to produce two series of delimited data that you can graph. Delimited data is a list of values in one field separated by returns. Each delimited value would be a sub total of the related data and a point on your chart.

          • 2. Re: Making multiple series with Perform Find
            DerekPopovich

                 Thanks Phil, 

                 I have used ExecuteSQL in other charts to display 2 series of a single record type, the Perform Find I have is a summary of up to 8 records type grouped by date, which I have not been able to work out the right ExecuteSQL code for it.

                 I have a MYSQL code which will do that, and it works great on my intranet server (APACHE, PHP, MYSQL), but I do to think it is supported in ExecuteSQL as every time I use it I get the "?", and can't work out what is causing it. 

                 My records type are a code that is assigned to a particular entry, I have  52 entries per year for each record type. I have over 400 records types.

                 The Perform Find does a great job with a summary field and that is the only way I have been able to make it work.

                 Have you seen any ExecuteSQL sample which do what I need? I have search around but can not find any samples that coves this type of operation, I even write to a few developer asking for a custom code (ExecuteSQL) which will do what I need but they are too busy to take on my request.

                 Sincerely, DPP

            • 3. Re: Making multiple series with Perform Find
              philmodjunk

                   See this thread for how to produce a list of summary values with ExecuteSQL: FMP 12 Tip: Summary Recaps (Portal Subtotals)

              • 4. Re: Making multiple series with Perform Find
                DerekPopovich

                     Hello Phil, 

                     Thank you, 

                     I have created the following query with help from other people on the net, it works fine when I use it on on MYSQL but I get

                     a "?" or a "0" as a result in Filemaker Pro:

                     
                          ExecuteSQL("SELECT COALESCE(S693,  0) + COALESCE(S698,  0) + COALESCE(S2010, 0) + COALESCE(S2020, 0) AS STOTAL
                     
                            FROM
                     
                          (
                     
                            SELECT sqldate AS sqldate,
                     
                                   CAST(MAX(CASE WHEN stat_number = 693  THEN sqlvalue END) AS UNSIGNED) AS S693,
                     
                                   CAST(MAX(CASE WHEN stat_number = 698  THEN sqlvalue END) AS UNSIGNED) AS S698,
                     
                                   CAST(MAX(CASE WHEN stat_number = 2010 THEN sqlvalue END) AS UNSIGNED) AS S2010,
                     
                                   CAST(MAX(CASE WHEN stat_number = 2020 THEN sqlvalue END) AS UNSIGNED) AS S2020
                     
                              FROM \"statsvalue copy2\"
                     
                             WHERE sqldate > '2013-01-07'
                     
                             GROUP BY sqldate
                     
                          ) AS T
                     
                            ORDER BY sqldate";"";"")
                     Can you see any problems with this ExecuteSQL query?
                     I was using GROUP_CONCAT before but Filemaker does not Support GROUP_CONCAT so I have replace it and still not working,
                      
                     Sincerely, 
                     Derek P
                • 5. Re: Making multiple series with Perform Find
                  philmodjunk

                       There are a number of possible problems. I'm not familiar with CAST and I don't recognize Coalesce()--don't know if that function is supported in FileMaker SQL.

                       '2013-01-07' is text, not a date and will not be compared to sqldate as a date.

                       For what you described in your original post, this SQL seems a needlessly complex approach to get a list of values where each value is an aggregate value to plot on the chart.

                  • 6. Re: Making multiple series with Perform Find
                    DerekPopovich

                         Thank you, 

                         By using the sql approach will allow me to use an sql query per Series without using Perform Find, that will let me have the "multiple Series"  in one chart. Using the Perform Find as I have it right now I will have to have 1 chart in 2 separate layouts in order to compare 2 calendar years.

                         Sincerely, 

                         Derek P

                    • 7. Re: Making multiple series with Perform Find
                      philmodjunk

                           I'm not saying that you shouldn't use SQL, I'm saying that the SQL that you have looks much more complex than is needed to get the values you need. If you look at the thread that I suggested earlier, it's possible to get a series of aggregate values with a much simpler SQL query.

                           But this is an observation based on very limited info. If I knew the actual structure of your table, I might find that my suggested "simple SQL" isn't likely to work.

                            

                      • 8. Re: Making multiple series with Perform Find
                        DerekPopovich

                             Hi, 

                             Perhaps it is complex, 

                             I try to use simple queries such as:

                             

                        SELECT sqldate, SUM ( sqlvalue ) FROM statsvalue WHERE stat_number = 693  GROUP BY sqldate ORDER BY 1 ASC

                              

                         and it works for only one stat_number (693), the moment I introduce " AND stat_number = 2010" my result goes blank.

                              

                        The table structure is:

                        "ID", "STAT_NUMBER" (NUMBER), "SQLDATE" (DATE), "SQLVALUE" (NUMBER VALUE).

                              

                        One set of values per row (one ID, one STAT_NUMBER, one SQLDATE, one SQLVALUE 

                              

                        I need to be able to add the sqlvalue for several stat_numbers and group them by sqldate where the sqldate is > 01-01-2013.

                              

                        The chart will only show the combine total "sqlvalue" for the stat_numbers specified in the query and plot them against the sqldate.

                              

                        Thank you for your help.

                        DPP

                              

                              

                              

                              

                        • 9. Re: Making multiple series with Perform Find
                          philmodjunk
                               

                           and it works for only one stat_number (693), the moment I introduce " AND stat_number = 2010"

                          Of course you get no data. No record in your table has a stat_number of 693 and 2010. Try using Or instead of And.

                               But do you want a series of Sums for stat number 693 and another series of sums for stat number 2010 or do you do you need one series that combines the values from these two stat numbers in a single group?

                          • 10. Re: Making multiple series with Perform Find
                            DerekPopovich

                                 I need one series that combines all the values.

                                 I have also used:

                                 
                                      <!--StartFragment-->ExecuteSQL("SELECT total,
                                      (SELECT MAX(sqlvalue) FROM data where stat_number = 693 AND sqldate > DATE '2013-01-01') AS S2,
                                      (SELECT MAX(sqlvalue) FROM data where stat_number = 698 AND sqldate > DATE '2013-01-01') AS S3,
                                      (SELECT MAX(sqlvalue) FROM data where stat_number = 2010 AND sqldate > DATE '2013-01-01') AS S4,
                                      (SELECT MAX(sqlvalue) FROM data where stat_number = 693 AND sqldate > DATE '2013-01-01') +
                                      (SELECT MAX(sqlvalue) FROM data where stat_number = 698 AND sqldate > DATE '2013-01-01') +
                                      (SELECT MAX(sqlvalue) FROM data where stat_number = 2010 AND sqldate > DATE '2013-01-01') AS TOTAL
                                      FROM data WHERE sqldate > DATE '2013-01-01' GROUP BY sqldate";"";"")
                            <!--EndFragment-->
                                  
                                      and selected summarized group of records on the chart and result is "0"
                                      Individual Records data and the result is "0" for some reason my "TOTAL" is not showing up anywhere.
                                       
                                      I have also used:
                                           
                                                ExecuteSQL(
                                           
                                                 
                                           
                                                "SELECT  SUM(sqlvalue)
                                           
                                                FROM \"statsvalue copy2\"
                                           
                                                WHERE (stat_number = 2010 OR stat_number = 698 OR stat_number = 693 OR stat_number = 2020)
                                           
                                                AND sqldate BETWEEN '01-07-2013' AND '31-12-2013'
                                           
                                                ORDER BY sqldate ASC"; 
                                           
                                                 
                                           
                                                ""; ¶ )
                                      
                                      But the screen shows nothing,
                                       
                                      If I change the query to:
                                       
                                           
                                                ExecuteSQL(
                                           
                                                 
                                           
                                                "SELECT  sqlvalue
                                           
                                                FROM \"statsvalue copy2\"
                                           
                                                WHERE (stat_number = 2010 OR stat_number = 698 OR stat_number = 693 OR stat_number = 2020)
                                           
                                                AND sqldate BETWEEN '01-07-2013' AND '31-12-2013'
                                           
                                                ORDER BY sqldate ASC"; 
                                           
                                                 
                                           
                                                ""; ¶ )
                                      
                                            
                                           It gives me the individual total for each stat_number, If I add "GROUP BY sqldate" nothing shows up on the screen.
                                            
                                           I am not sure what else to try.
                                       
                                       
                                       
                                       
                                       

                                  

                            • 11. Re: Making multiple series with Perform Find
                              DerekPopovich

                                   OK, Finally it is working: 

                                   The ExecuteSQL under the Y-Axis is:

                                   
                                        ExecuteSQL(
                                   
                                         
                                   
                                        "SELECT  SUM(sqlvalue)
                                   
                                        FROM \"statsvalue copy2\"
                                   
                                        WHERE (stat_number = 2010 OR stat_number = 698 OR stat_number = 693 OR stat_number = 2020)
                                   
                                        AND sqldate BETWEEN '01-07-2013' AND '31-12-2013' 
                                   
                                        GROUP BY sqldate
                                   
                                        ORDER BY sqldate ASC"; 
                                   
                                         
                                   
                                        ""; ¶ )
                                    
                                   and the ExecuteSQL under the X-Axis is:
                                    
                                        
                                             ExecuteSQL(
                                        
                                              
                                        
                                             "SELECT  sqldate
                                        
                                             FROM \"statsvalue copy2\"
                                        
                                             WHERE (stat_number = 2010 OR stat_number = 698 OR stat_number = 693 OR stat_number = 2020)
                                        
                                             AND sqldate BETWEEN '01-07-2013' AND '31-12-2013' 
                                        
                                             GROUP BY sqldate
                                        
                                             ORDER BY sqldate ASC"; 
                                        
                                              
                                        
                                             ""; ¶ )
                                   

                                   The ExecuteSQL for the Y and the X axis have to have the same query the only difference is that X-Axis has SELECT sqldate and Y-Axis has SELECT SUM(sqlvalue) everything else has to be exactly the same or it does not work (Per what I have seen).

                                   Thank Phil, for directing me to simplify my query.