11 Replies Latest reply on Dec 26, 2012 10:35 AM by philmodjunk

    Problems with FMP GROUP BY in ExecuteSQL

    MorkAfur

      Title

      Problems with FMP GROUP BY in ExecuteSQL

      Post

           When I try to run the query below (withouth the YEAR(CHECK_DATE), it works OK but doesn't properly summarize by year.

           For some reason, when I add the YEAR(CHECK_DATE) to the GROUP BY clause, FMP tells me I have a query error ("There is an error in the syntax of the query."). I had the same issue trying to select the current year in the "FROM" clause, but temporarily just hard coded the year to 2012.

           So, how do you have a summary listing like this:

           3 2010

           4 2011

           5 2012

           If you can't GROUP BY using a YEAR of a date field as I'm trying to do below?

           I don't have to have summary fields defined in the database to be able to use GROUP BY, right?

           -------------------------------------

           SELECT SUM(NUM_THIS_MONTH) as Num_by_year , YEAR(CHECK_DATE) as YEAR_DATE
           FROM TABLE1
           WHERE Year(CHECK_DATE) = 2012
           Group by NUM_THIS_MONTH , YEAR(CHECK_DATE)
           order by CHECK_DATE DESC

           Thanks in advance,

           -m

        • 1. Re: Problems with FMP GROUP BY in ExecuteSQL
          philmodjunk

               I don't think that FileMaker SQL has a year function. Instead, define a calculation field in your table as Year ( Check_Date ) and group by this field instead of trying to use the function as part of your SQL.

               Either that or use Group by Year_Date instead of the function call.

               I know that the first option will work, but try the second approach first to see if that resolves it.

          • 2. Re: Problems with FMP GROUP BY in ExecuteSQL
            MorkAfur

                 Hey Phil,

                 Actually FMP does have a Year function in the SQL implementation, but it's not working as expected -- it doesn't work in the GROUP BY, but does work in the WHERE. So, creating a calculated field is apparently necessary.

                 So, I created a calculated field as you suggested, but the SQL still doesn't work correctly -- within FMP, that is. This could be bug.

            I created a script trigger to run each time a record was loaded on the layout.

                 What's very strange is that within the debugger (a hard-coded experimentation value), the following returns blank -- instead of zero (zero is the correct result):

                     SELECT SUM(NUM_THIS_MONTH)
                  FROM
                       DATA_CHECKS
                  WHERE
                     YEAR(CHECK_DATE) = 2010

                 Yet, if I run the EXACT same SQL from a remote data console, I get zero as expected.

                 ???

                 -----------------

                 Furthremore, the following Let condition always returns zero (note: DATA_CHECKS::YEAR_OF_CHECK_DATE is the calculated field for just the year):

                 -- this Let is in a SetVariable, followed by a SET FIELD to set the variable returned from the Let.

                 Let ( $thisYear = ExecuteSQL ( "SELECT SUM(NUM_THIS_MONTH)
                  FROM
                       DATA_CHECKS
                  WHERE
                     YEAR(CHECK_DATE) = ?"  ; ""  ;DATA_CHECKS::YEAR_OF_CHECK_DATE) ;

                     If ($thisYear = 0 or $thisYear = ""; 0; $thisYear)
                 )

                 --------------

                 I also tried just having a set variable where I did the following SQL:

                     SELECT SUM(NUM_THIS_MONTH)
                  FROM
                       DATA_CHECKS
                  WHERE
                     YEAR(CHECK_DATE) = YEAR_OF_CHECK_DATE

                 But this always returns the overall total for 2012 (the only year with data). When I move to 2011 or 2010, etc., the total stays at the total for 2012.

                 In the debugger, the year is being updated correctly (both YEAR(CHECK_DATE) and YEAR_OF_CHECK_DATE), but somehow, the SQL isn't being correctly updated.

                 (Sigh, I also tried just using a Set Field and using the ExecuteSQL as the calculated result with the same issue: overall sum for the field used regardless of which year I'm in in the layout)

            I have this script to run on record load.

                 ---------

                 Very confusing. FMP isn't giving me what I expect here at all.

                 --------

                 I also thought about creating another table occurance as another approach and experimenting with that, but I wanted to get this working first.

                 Look forward to your thoughts.

                 Thanks,

                 - m

            • 3. Re: Problems with FMP GROUP BY in ExecuteSQL
              philmodjunk

                   Did you use stored on unstored calculatons? I'm still getting used to ExecuteSQL myself so don't know if Unstored is a necessary requirement here, but it's one of the options that I would try. I'll see if I can find time later to run some tests here. I'd like to see if I can replicate this.

              • 4. Re: Problems with FMP GROUP BY in ExecuteSQL
                MorkAfur

                     Hi Phil,

                     I'm down to just using a SET Field with a calculation for the target field, the ExecuteSQL.

                     What do you mean by "Stored calculation"?

                     Looks like an update/eval bug (and a limitation in the GROUP BY to using Year).

                     I could also create a separate FMP12 file and post that on my public Dropbox account if you want.

                     Look forward to hearing back.

                     Thanks,

                     -m

                • 5. Re: Problems with FMP GROUP BY in ExecuteSQL
                  philmodjunk

                       Apologies for not reading your post with sufficient care. I was thinking in terms of a calculation field.

                       I've been using the Known Bugs List database as my test subject and trying out different variations of SQL expressions in the Dataviewer on a Windows 7 machine:

                       ExecuteSQL( "SELECT count ( a.cYear ) From \"FMP Bugs\" a
                       Where Year ( a.crDate ) = ? "
                       ; "   " ; ¶ ; GlobalFields::gYear )

                       produces a valid result for any value that I enter into the global field. This includes a result of 0 if I enter a year for which there are no records with that year.

                       But if I modify the expression to be:

                       ExecuteSQL( "SELECT count ( a.cYear ) , Year ( a.crDate ) From \"FMP Bugs\" a
                       Where Year ( a.crDate ) = ? "
                       ; "   " ; ¶ ; GlobalFields::gYear )

                       I get a ? result

                       Yet

                       ExecuteSQL( "SELECT Year ( a.crDate ) as Ydate From \"FMP Bugs\" a "
                       ; "   " ; ¶ ; GlobalFields::gYear )

                       produces a valid result and I can't see a reason for the ? in the previous test.

                  • 6. Re: Problems with FMP GROUP BY in ExecuteSQL
                    MorkAfur

                         OK, now tie your working query you have to a table where there are only some years with data. The query should recalculate for each row (I used a layout script trigger of on record enter) to update a "Total <some field> for this year".

                         Then step through the records when having the SQL be part of a calculation to update the field on the screen. See if the field is correctly updated when you either change years and those years have different data or you change years and they have some different data.

                         For me, it always shows the total for all years regardless of what year I'm in.

                         I'm sure my description probably leaves questions so I'll create a sample tomorrow demonstrating the problem.

                         Thanks Phil.

                    • 7. Re: Problems with FMP GROUP BY in ExecuteSQL
                      MorkAfur

                           Hi Phil and any others following this thread,

                           Below is a link to the TestSQL.FMP12 file I created using two methods to try to get Sums by year using ExecuteSQL. Neither works.

                           In one case (the calculation), I just get a "?" and in the other case, I get the entire sum for the entire table, not by year. The second case I believe should update as it uses the record load script trigger -- so it should re-evaluate the fields in the SQL. It doesn't.

                           100 random records created.

                           In the zip file link below, I also have a png showing the correct breakouts (sums) by year.

                           If nobody sees an issue with the code or the implementation (using a calculated field in one case and a script trigger in another), I'll submit a bug report.

                           I'm guessing it's probably me or maybe a rough edge or two with the new ExecuteSQL, but my remote SQL console works fine.

                           I'll leave this file on Drobox until Monday, Dec 24 at 12:00 PM (noon) EST.

                           Look forward to any input.

                           Thanks,

                           -m

                           https://www.dropbox.com/s/0khpqxkbqe43wlm/FMP%20SQL%20Issue.zip

                      • 8. Re: Problems with FMP GROUP BY in ExecuteSQL
                        MorkAfur

                             Since nobody has yet responded to this thread with the FMP example I created, should I assume that I've identified an ExecuteSQL issue?

                             Even with this possible bug aside, it's unclear to me how you would get summaries (totals by year) using only a single table, say, by year given data like this:

                             2000  4

                             2001 3

                             2001 2

                             2002 3

                             2002 5

                             etc..

                             In any case, the link to the zip file with my FMP 12 example is below.

                             I will report this problem as a bug if someone agrees (Phil?) with my assessment.

                             Thanks,

                             - m

                             https://www.dropbox.com/s/0khpqxkbqe43wlm/FMP%20SQL%20Issue.zip

                        • 9. Re: Problems with FMP GROUP BY in ExecuteSQL
                          philmodjunk

                               When I tested this, I was careful to specify years for which there would be no matching records and this did not create any issues for me. (I was using a field that auto-enters creation dates, so using 2013 was an easy way to test this for years with no matching records.

                          • 10. Re: Problems with FMP GROUP BY in ExecuteSQL
                            MorkAfur

                                 Hi Phil,

                                 I'm not sure exactly what you're saying, but I did post a problem report ("report a problem") on the FMP forum referencing the FMP12 file that doesn't work as expected. I also included a link to the FMP 12 file itself I took a couple hours (at least) creating for illustration.

                            The basic problem is that the replaceable parameter isn't re-evaluated by ExecuteSQL on the Record Load Script Trigger.

                                 Did you see that report? I posted it yesterday, on 12/25/12.

                                 It's possible I'm trying to tie the ExecuteSQL to the Record Load Event in a way that wasn't thought by the FMP staff of or hasn't been implemented or whatever. Maybe I'm even doing this incorrectly -- though I've used the same ExecuteSQL syntax in other contexts (without tying it to the On Record Load script trigger) where it works. FMP's ExecuteSQL is very stingy with any kinds of details.

                                 I also noted that the YEAR function, while it works fine in the WHERE clause didn't work in the GRUOP BY. That must be a bug since in SQL this would be an obvious need (without needing to create a separate calculated field to extract the year from the date). Possibly yet unimplemnted functionality. Not sure.

                                 This "simple sum" idea was one of those cases where I thought, knowing FMP, what I was trying to do would take me a few minutes only to find it still not done after several days and several dents in my desk (from constant head banging!).

                                 Your input is always appreciated.

                                 Thanks,

                                 -m

                            • 11. Re: Problems with FMP GROUP BY in ExecuteSQL
                              philmodjunk
                                   

                                        Did you see that report? I posted it yesterday, on 12/25/12.

                                   I did not access the forum on Christmas Day.