6 Replies Latest reply on Nov 3, 2014 3:29 PM by BruceHerbach

    SQL for field definition gets Parse Error when creating an auto-enter calculation?

    BruceHerbach

      I'm trying to get FileMaker 13 Advanced 13.0V3 to accept the following calculation for an auto-enter calculation. This is out of SeedCode SQLExplorer version 1.3. When I try to save the definition I get a Parse Error message and the definition can't be saved. If I paste this into the data viewer, it returns the correct result. So the SQL is functional.

       

      This started as an older solution for some of the fields originally started with an _ character. In an attempt to get this to work, I renamed the fields. This didn't help.

       

      Any suggestion on how to get past this would be greatly appreciated.

       

      ----------- SQL ---------------

      // Built by SQLExplorer. Compliments of SeedCode… Cheers!

       

      Let ( [

       

      TPA = TSK_assembly::templateID;

      TPT = TASK::templateID;

       

      // Define Carriage Return Substitution Character

      ReturnSub = " " ;

       

      // Enable the second line here if you want the header in your results

       

      header = "";

      //header = "a.L2_Size_Select";

       

       

      // Define Table variables

      aTPL_TSK_SIZE_SELECT = Quote ( GetValue ( Substitute ( GetFieldName ( Tpl_TSK_Size_select::kf_Assembly ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

       

      // Define Field Variables

      aAssembly = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Tpl_TSK_Size_select::kf_Assembly ) ; "::" ; ¶ ) ; 2 ) ) ;

      aTask = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Tpl_TSK_Size_select::kf_Task ) ; "::" ; ¶ ) ; 2 ) ) ;

      aL2SizeSelect = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Tpl_TSK_Size_select::L2_Size_Select ) ; "::" ; ¶ ) ; 2 ) ) ;

       

      // Build SQL Query

      q =

      "SELECT " & aL2SizeSelect & "

      FROM " & aTPL_TSK_SIZE_SELECT & "

      WHERE " & aAssembly & " = ? AND " & aTask & " = ? " ;

       

      // Run SQL Query

      result = ExecuteSQL ( q ; "" ; "" ; TPA ; TPT ) ] ;

       

      // Clean up carriage returns

      GetAsNumber(result))

        • 1. Re: SQL for field definition gets Parse Error when creating an auto-enter calculation?
          steve_ssh

          Hello Bruce,

           

          I'll post one thought below.  I will be interested to see what others suggest.

           

          Sincere regards,

           

          -steve

           

           

           

          You might try the following and see what happens:

           

           

          1) Define a Custom Funcion, MyQueryString, as follows:

           

          Let([

           

              aTPL_TSK_SIZE_SELECT = Quote ( GetValue ( Substitute ( GetFieldName ( Tpl_TSK_Size_select::kf_Assembly ) ; "::" ; ¶ ) ; 1 ) ) & " a";

           

              aAssembly = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Tpl_TSK_Size_select::kf_Assembly ) ; "::" ; ¶ ) ; 2 ) ) ;

           

              aTask = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Tpl_TSK_Size_select::kf_Task ) ; "::" ; ¶ ) ; 2 ) ) ;

           

              aL2SizeSelect = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Tpl_TSK_Size_select::L2_Size_Select ) ; "::" ; ¶ ) ; 2 ) )

           

          ];

           

              "SELECT " & aL2SizeSelect & " FROM " & aTPL_TSK_SIZE_SELECT & " WHERE " & aAssembly & " = ? AND " & aTask & " = ? "

          )

           

           

           

          2) Then redefine your AEC as follows:

           

          Let ([

           

              TPA = TSK_assembly::templateID;

           

              TPT = TASK::templateID;

           

              q = MyQueryString;

           

              result = ExecuteSQL ( q ; "" ; "" ; TPA ; TPT )

           

          ];

           

              GetAsNumber( result )

          )

           

           

           

           

          Note:

           

          Personally I would also like to factor the following construct into one or two CFs:

           

              Quote( GetValue( Substitute( GetFieldName() ) ) ) )

           

          but I do not believe that this would be required for you to get beyond the parse error situation.

           

          It would be just to simplify the definition of the MyQueryString CF to make it a bit more readable (and also because the factored CFs might be useful elsewhere if there's more use of ExecuteSQL going on in the file).

          • 2. Re: SQL for field definition gets Parse Error when creating an auto-enter calculation?
            wimdecorte

            Bruce Herbach wrote:

             

            I'm trying to get FileMaker 13 Advanced 13.0V3 to accept the following calculation for an auto-enter calculation.  This is out of SeedCode SQLExplorer version 1.3.  When I try to save the definition I get a Parse Error message and the definition can't be saved.  If I paste this into the data viewer,  it returns the correct result.  So the SQL is functional.

             

            In the calculation window, is the "evaluate from" valid for all referenced fields?

            • 3. Re: SQL for field definition gets Parse Error when creating an auto-enter calculation?
              BruceHerbach

              Win,

               

              In the data-viewer all of the fields and values are valid.  After posting

              this,  I tried it with the standard calculation instead of the abstracted

              version shown in the post. FileMaker accepted this.

               

              I'll try the custom function approach and see if that helps.

               

              Thanks

              Bruce

               

              Sent from my mobile device...

              Please excuse typos.

              • 4. Re: SQL for field definition gets Parse Error when creating an auto-enter calculation?
                BruceHerbach

                Wim, Steve,

                 

                Thanks for the suggestions.  This dicsussion pointed me to the correct solution.  https://fmdev.filemaker.com/thread/67765

                The issue was that that the TO for the data fields and keys was unrelated to the TO::field I was running the SQL on.  So that when I used the base version of the code without abstraction,  FileMaker accepted it and it worked.  When I added the abstraction using the getfieldname() function,  I get the Parse error.

                 

                The fix was to add a TO that connected the destination Table:: field to the SQL source table TO.

                 

                Running the query in the dataviewer or using it in a Set Variable statement works fine using any TO on the graph so it doesn't have to be related.  Trying to put it into a field definition ups the requirements and it has to be connected.

                 

                Funny thing is I have done this in a number of solutions,  but they always had a connection to the TO so it worked.  This is a slighly older setup which uses Anchor bouy and this TOG didn't have a connection to the TO because it didn't need it until now.

                • 5. Re: SQL for field definition gets Parse Error when creating an auto-enter calculation?
                  davidhamannmedia

                  One way to get around adding another TO is to add the TO just temporarily. You can define the relationship, write your calculation, save it and then remove the (unnecessary) relationship.

                   

                  FileMaker keeps the calculation intact and the SQL query still works.

                   

                  Best,

                  David

                  • 6. Re: SQL for field definition gets Parse Error when creating an auto-enter calculation?
                    BruceHerbach

                    David,

                     

                    Thanks for the suggestion.  I left the TO in place. I don't want to open

                    the field definition sometime in the future only to have the Parse Error

                    return because I tweaked something.

                     

                     

                     

                    Sent from my mobile device... Please excuse typos.