1 2 3 Previous Next 40 Replies Latest reply on Aug 21, 2017 4:08 PM by xxx202xxx

    ExecuteSQL to FMP Calculation assistance

    xxx202xxx

      I was attempting to use executeSQL which would have been better but I could not get the rows to show like I needed them to and the directions where to deep for me to follow.  So I would like to come up with 3 filemaker calculation fields given the executesql querry. Field 1 = I need to show the top ten IncidentSummary_Software for whatever the current month is.  Field 2 = I need the count of those IncidentSummary_Software and Field 3 (which I could figure out) = the % of the Total..  Thank You

       

      Example would be:   Disk Drives     15     19%

       

      ExecuteSQL ("

      SELECT COUNT (IncidentSummary_Software), IncidentSummary_Software
      FROM Service_Management
      WHERE Month (Time_Created)=?
      GROUP BY  IncidentSummary_Software
      ORDER BY 1 DESC
      FETCH FIRST 10 ROWS ONLY"

      ; Char(9)  ; ""; Month (Get (CurrentDate)))

        • 1. Re: ExecuteSQL to FMP Calculation assistance
          bigtom

          ORDER BY does not specify a column from the SELECT directly. I am not sure if FM support order by ordinal.

           

          Selecting a COUNT and a column as you use might need to return the same numbers rows to work.

           

          Start with the simple select and add things for testing.

           

          Does this work:

          ExecuteSQL ("

          SELECT COUNT (IncidentSummary_Software), IncidentSummary_Software
          FROM Service_Management"; ""; "")

          • 2. Re: ExecuteSQL to FMP Calculation assistance
            xxx202xxx

            That does work but it doesn't look right.  I had put the cout first to make look neat but I really want it to look like my example

            • 3. Re: ExecuteSQL to FMP Calculation assistance
              bigtom

              You have three things in your example, but only two in the SELECT.  How does it not look right?

              • 4. Re: ExecuteSQL to FMP Calculation assistance
                xxx202xxx

                I never got to the 3rd item.  Just added that in before submitting my question

                • 5. Re: ExecuteSQL to FMP Calculation assistance
                  greglane

                  I know you asked for a non-ExecuteSQL solution, but I'm a fan of correlated subqueries for this kind of thing. Something like this should be close:

                   

                  ExecuteSQL ("

                  SELECT IncidentSummary_Software, COUNT (IncidentSummary_Software), '' || round(

                    (SELECT COUNT(IncidentSummary_Software)

                    FROM Service_Management

                    WHERE Month (Time_Created) = ? and IncidentSummary_Software = a.IncidentSummary_Software) / (

                      SELECT COUNT(IncidentSummary_Software)

                      FROM Service_Management

                      WHERE Month (Time_Created) = ? ),2) * 100 || '%'

                  FROM Service_Management a

                  WHERE Month (Time_Created)=?

                  GROUP BY  IncidentSummary_Software

                  ORDER BY 2 DESC

                  FETCH FIRST 10 ROWS ONLY"

                  ; Char(9)  ; ""; Month (Get (CurrentDate)); Month (Get (CurrentDate)); Month (Get (CurrentDate)))

                   

                  A couple of things to consider:

                   

                  • This is calculating the percentage based on all of the records for the month, not just the total of the top 10.
                  • If you have more than one year of records, you'll want to add the year to the WHERE clauses.
                  • If it's a large table and performance is an issue, consider restructing the WHERE clauses to use the BETWEEN operator and pass the first and last day of the month as arguments. That should perform better since FileMaker wouldn't have to calculate the month (and year) for the date in each record in the table.
                  2 of 2 people found this helpful
                  • 6. Re: ExecuteSQL to FMP Calculation assistance
                    xxx202xxx

                    Let me give this a go

                    • 7. Re: ExecuteSQL to FMP Calculation assistance
                      philmodjunk

                      There are non-SQL ways to get this report, but it wouldn't use calculation fields. You'd use summary fields for both the count and % (fraction of total).

                       

                      You'd need a script to narrow the results to top ten, but it would not be a complex script.

                      • 8. Re: ExecuteSQL to FMP Calculation assistance
                        xxx202xxx

                        Do tell please sir.  EXECUTESQL just doesn't look as pleasing.

                        • 9. Re: ExecuteSQL to FMP Calculation assistance
                          bigtom

                          I agree with Phil. I would use a script and likely use eSQL in the script for a few things.

                          • 10. Re: ExecuteSQL to FMP Calculation assistance
                            xxx202xxx

                            I'm just learning sql from the group so I am a lot green on this stuff.  Never been good at reading code.  I learn from a lot of questions as it pertains to what I'm working  on.

                            • 11. Re: ExecuteSQL to FMP Calculation assistance
                              philmodjunk

                              Well you "agree" with me but then go on to recomend eSQL.

                               

                              Hey, there's many ways to skin this cat and I'm not saying one is better than the other, just noting that this is rather odd "agreement"!

                               

                              Since I was asked to spell out the details. A summary field can be a 'count of' field to count records. that's field 1. You have field 2. A summary field that computes a "fraction of total" can be formatted to display as a percent works as field 3.

                               

                              The totally non-SQL method that I had in mind works like this:

                               

                              Find your records, except you don't yet try to figure "top ten" as that requires computing some sub totals.

                              Sort to group your records. In this case that would be sorting by IncidentSummary_Software. Then you sort again. There's a sort option that allows you to "re-order based on summary field" where you select the counting summary field and descending order to re-order them from largest subtotal to smaller.

                               

                              On a layout, you can use a sub summary part "when sorted by" IncidentSummary_Software to show the incident category, count and %. You can delete the body so that you only get one row of data to a group.

                               

                              The last step needs a script. You use a script to loop through the records until you get to the first record not part of the top ten values and then use Omit Multiple Records to omit the remaining records.

                               

                              The re-ordering step can be slow with large found sets, but it does work.

                              1 of 1 people found this helpful
                              • 12. Re: ExecuteSQL to FMP Calculation assistance
                                bigtom

                                The agreement is that there are easier ways than trying to squeeze it all into one SQL query.

                                 

                                Easier to grasp. Easier to debug. Easier to read. I am not against the non eSQL method. I just think that using simpler queries in parts of the script would be effective and it is likely what I would choose to do.

                                 

                                That being said, knowing how to do it without eSQL is a valuable thing.

                                • 13. Re: ExecuteSQL to FMP Calculation assistance
                                  fmpdude

                                  SQL can get quite complicated. A "Correlated Sub-query" is when you use fields in the outer query referenced (used) in an inner query. It's a bit advanced. SQL is amazing since it's "declarative" -- meaning you don't have to "code it". You say "what" rather than "how" and the SQL engine figures out how to do it.

                                   

                                  I would recommend mastering SQL, even if it's a bit painful, as it's used in every database out there. Fortunately, there are so many excellent free resources for that!

                                   

                                  There are sometimes proprietary additions made to SQL (like Oracle's "Minus" clause), but I believe basic SQL, including correlated sub-queries should be in any database developer's wheelhouse.

                                  2 of 2 people found this helpful
                                  • 14. Re: ExecuteSQL to FMP Calculation assistance
                                    xxx202xxx

                                    Thanks for your help Phil.  However I'm having issues now with the final part:

                                    "The last step needs a script. You use a script to loop through the records until you get to the first record not part of the top ten values and then use Omit Multiple Records to omit the remaining records."

                                    1 2 3 Previous Next