1 2 Previous Next 18 Replies Latest reply on May 14, 2014 6:23 AM by DamianKelly

    Max

    botony269

      For fun I have a database that records all the beers I drink. The two tables I am concerned about are BeerLog which records the beers, date, score etc and Beers which keeps information, such as the name, brewer, ABV etc, about the beers themselves. The two tables are linked via a field, BeerID which appears in both tables.

       

      I have been trying to use the MAX function with the following:

       

      ExecuteSQL ( " SELECT MAX ( Score ) FROM BeerLog INNER JOIN BeerLog.BeerID ON Beers.BeerID = ? ; " " ; " BeerID " )

       

      as the calculation for a field, MaxScore, in the Beers table which appears on a layout based on the Beers table.

       

      Whatever I do to the above statement I always get a blank in the Edit Box related to MaxScore on the layout.

       

      I am using FileMaker 12

       

      What am I missing something? Any help would be appreciated.

        • 1. Re: Max
          Mike_Mitchell

          Assuming "BeerID" is the name of a field, get rid of the quotes around it. The parameter is looking for a value, not a text constant. So what you're telling FileMaker, in essence, is:

           

          "Find me the maximum Score value from the BeerLog table where the BeerID field = 'BeerID'".

           

          You probably don't have any beers with a BeerID value of "BeerID".   

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Max
            botony269

            Thanks Mike

            Regret to say it did not work. The Edit Box shows '?'

            Tony

            • 3. Re: Max
              DamianKelly

              You are suffering from superfluous join syndome! You dont need to create the join because you already know the BeerID from the Beer record within the current context.

               

              Try:

               

              ExecuteSQL ( " SELECT MAX ( Score ) FROM BeerLog WHERE BeerID = ?" ; " " ; BeerID )

               

               

              BTW I would suggest just using a bog standard calc here, you probably already have a join in the relationship graph and ExecuteSQL is not always quicker than

               

              MAX(BeerLog::Score)

               

              Damian

              1 of 1 people found this helpful
              • 4. Re: Max
                coherentkris

                You wrote ExecuteSQL ( " SELECT MAX ( Score ) FROM BeerLog INNER JOIN BeerLog.BeerID ON Beers.BeerID = ? ; " " ; " BeerID " )

                Has a couple of issues...

                1. No end quote after the question mark.

                2. " BeerID " in wrong place for it to act as a parameter...it is specified as the RowSeperator

                3. " " is specified as the FieldSeperator.

                 

                The format of the function is ExecuteSQL ( "SQL" ; FieldSeperator ; RowSeperator ; Argument(s) )

                 

                4. probably unneeded join as stated by DamianKelly.

                5. extra quotes around the parameter as stated by mike

                 

                I always like to escape quote the field and table arguments.. thus we have

                ExecuteSQL ( "SELECT MAX ( \"Score\" ) FROM \"BeerLog\" WHERE \"BeerID\"=?" ; "" ; "" ; BeerID )

                • 5. Re: Max
                  DamianKelly

                  oops didnt pick up on the parameters being wrong, sorry. Did notice the quotes though :-)

                  • 6. Re: Max
                    DamianKelly

                    Is it really worth quoting words that are extremely unlikely to ever end up as SQL keywords? A bigger issue is making the SQL robust within FileMaker's relaxed attitude to field renaming? If you have custom functions to return quoted field names you can cover both bases and keep the SQL relatively simple and robust:

                     

                    Let([

                    tbl_BeerLog = GetTONameOnly (BeerLog::BeerLogID);

                     

                    fld_BeerID = GetFieldNameOnlyQualified ( BeerLog::BeerID );

                    fld_Score = GetFieldNameOnlyQualified ( BeerLog::Score )

                     

                     

                    ];

                    ExecuteSQL ( "SELECT MAX ( " & fld_Score & " ) FROM " & tbl_BeerLog & " WHERE " & fld_BeerID & "=?" ; "" ; "" ; BeerID )

                    )

                     

                    The custom functions GetTONameOnly and GetFieldNameOnlyQualified do what they say on the tin. Respectively returning "BeerLog", "BeerLog.BeerID" and "BeerLog.Score" (including quotes). Because the custom functions explicitly use the field names any schema changes do not break the SQL. There are examples of the custom functions on various blogs, Fabrice Nodmann's specifically springs to mind.

                    • 7. Re: Max
                      coherentkris

                      just habit on my part to escape quote SQL args.

                      Using custom functions in ExecuteSQL come with penalty that may not be worth it in every situation...a forced binding to the relationship graph. At least most of the ones ive tried

                      • 8. Re: Max
                        DamianKelly

                        Mine dont but now I come to look at it I do not understand why not. A simple getfieldname(table::field) requires the field to be within the context.

                         

                        A custom function:

                         

                        "\""

                        &

                        GetValue(Substitute (GetFieldName(field); "::"; "¶"); 2)

                        &

                        "\""

                         

                        called by (note no quotes around the field name, so renamed fields changes will be reflected

                         

                        getfieldnameonlyqualified ( pref_STAFFSUBDEPARTMENT_TrainingModule_SystemPref::ID_StaffSubDepartment )

                         

                        returns

                         

                        "ID_StaffSubDepartment"

                         

                        And I am sure I am in a different tog.....

                        • 9. Re: Max
                          erolst

                          coherentkris wrote:

                           

                          just habit on my part to escape quote SQL args.

                          Using custom functions in ExecuteSQL come with penalty that may not be worth it in every situation...a forced binding to the relationship graph. At least most of the ones ive tried

                          There is no penalty, and no ”forced binding“; without a CF, you pass existing TO and field names as strings; when using a CF, you pass it an existing fully qualified field name from an (arbitrary) TO. Where's the difference? (Sorry for being so “stuffy” … )

                          DamianKelly wrote:

                          Mine dont but now I come to look at it I do not understand why not. A simple getfieldname(table::field) requires the field to be within the context

                          No – a 'simple' GetField() would require a valid context, since then you would want to evaluate the fieldname as expression, i.e. query the field's contents.

                           

                          GetFieldName() asks for the name, which is context-independently registered in the schema.

                          DamianKelly wrote:

                          "\""

                          &

                          GetValue(Substitute (GetFieldName(field); "::"; "¶"); 2)

                          &

                          "\""

                          Whatever happened to Quote() ? … and ¶ being an operator?

                          • 10. Re: Max
                            DamianKelly

                            Am I missing something still?

                             

                            GetFieldName(table::field) in the data viewer requires table::field to be within the current TOG (assuming you use TOGs not spider)

                             

                            However a custom function that simple has GetFieldName(table::field) works everywhere.

                             

                            We dont need no stinking quote :-) Actually I never thought of it :-)

                            • 11. Re: Max
                              erolst

                              DamianKelly wrote:

                              GetFieldName(table::field) in the data viewer requires table::field to be within the current TOG (assuming you use TOGs not spider)

                              Not in my Data Viewer. In GetFieldName (TO::fieldName ), TO can be totally unrelated to the current layout's TO (i.e. the Data Viewer's context).

                               

                              See here: https://www.goya.com.au/category/blogtags/script-parameters (not directly related to this discussion; search for “context”)

                              • 12. Re: Max
                                DamianKelly

                                Mea cupla. You are right. Not sure what happened when I tried that.

                                • 13. Re: Max
                                  flybynight

                                  2 things…

                                   

                                  First, I love the amount of input going into something that is just a "for fun" database. You guys rock!

                                   

                                  Second, where else would someone start a sentance with, "For fun I have a database that …"  

                                   

                                  OK, 3 things… you are all making me thirsty!  #BeerID

                                   

                                  Laters,

                                  -Shawn

                                   

                                  Message was edited by: flybynight to add the missing word "start" as erolst pointed out. Always proofread to make sure you don't any words out!   ;)

                                  • 14. Re: Max
                                    erolst

                                    flybynight wrote:

                                    Second, where else would someone a sentance with, "For fun I have a database that …"  

                                     

                                    "start”?

                                     

                                    Probably not in MS Access forums

                                    1 2 Previous Next