12 Replies Latest reply on May 12, 2015 7:00 AM by alecgregory

    "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation

    ToddGeist

      Summary

      "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation

      Product

      FileMaker Pro

      Version

      12.0v3

      Operating system version

      mountain lion

      Description of the issue

      You can't use GetFieldName() in a Let Function with either Evaluate or ExecuteSQL, when defining a field calculation.

      Steps to reproduce the problem

      Try this in  Calculation Field.  Make sure the field you  are referencing with GetFieldName is unrelated.


      Let([
      s = GetFieldName(    )
      ];

      Evaluate(s)


      )

      Expected result

      the calculation dialog can be closed and the calculation field will work correctly

      Actual result

      You are not able to close the Calc Dialog.  You get "Parse Error." Instead.

      Exact text of any error message(s) that appear

      Parse Error.

      Workaround

      Getting rid of the LET function and doing this for example

      Evaluate(GetFieldName(   ))

      Works

      Screenshot_11%3A27%3A12_12%3A24_PM.png

        • 1. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
          philmodjunk

               I cannot reproduce this in Windows XP--which could mean that this is Mac or even Mountain Lion specific.

               let ( [s = Getfieldname ( NoData::cgWindowNames ) ] ; evaluate ( s ) )

               returns the contents of cgWIndowNames as expected.

          • 2. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation

                 Todd Geist:

                 Thank you for the post.

                 I replicated this on my work station running FileMaker Pro 12v3 installed on Mac OS X 10.7.5 and Windows 7 using the steps provided. This was the same behavior in FileMaker Pro 11. I forwarded your report and the sample file to Testing and Development for review. 

                  

                 TSFalcon

                 FileMaker, Inc.

            • 3. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
              philmodjunk

                   Just did some more tests in Windows 7 and could not at first reproduce these results. Then I realized that the key detail was that it must be a field from an Unrelated table, something that should not produce a valid results unless the field is a global field in the first place. Then I could get the Parse error--but only if the reference field is not a global field.

                   This makes this bug more a case of an uninformative error message--we should get a error message that tells us that we won't get a valid result, than anything else.

              • 4. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
                ToddGeist

                     @PhilModJunk, 

                     It is more than uninformative error message. The use of GetFieldName() doesn't require a relationship to the Field being referenced in every other case except in the cases listed.  It only fails when used in conjunction with Evaluate, Let, ExecuteSQL, and ( newly discovered ) Custom Functions.  If you just use GetFieldName alone it workd fine.  Thats why it's gotta be a bug.

                     Todd

                • 5. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
                  danshockley

                        

                       I think the issue is a bit broader even than what Todd put in the description.
                       It actually seems to fail when any variable set to a calculation that includes GetFieldName on an unrelated field is then used as a parameter in ANY other function, not just Evaluate or ExecuteSQL. So, the Description should be:
                        
                       "You can't use GetFieldName() in a Let Function to define a Let Variable and then use that Let Variable in any other function elsewhere in the Let statement, when defining a field calculation, auto-enter calculation, or field validation calculation. 
                        
                       To use Todd's example, the following works:
                        
                       Let([
                       
s = GetFieldName( TableB::SomeUnrelatedField )

                       ];
                       
" " & s  // just the Let variable s, but prepending a space character = NO error

                       )
                        
                       However, as soon as you try to include the Let variable inside of some FileMaker function, the syntax-checking fails with a "Parse error." Here's an example:
                        
                       Let([
                       s = GetFieldName( TableB::SomeUnrelatedField )
                       ];
                       Upper ( s )   // Let variable 's' used within a very simple function = ERROR
                       )
                        
                        
                       BUT, the following does NOT give the Parse Error:
                        
                       Let([
                       s = Upper ( GetFieldName( TableB::SomeUnrelatedField ) )
                       ];
                       s   // just the Let variable s, which has already had the Upper function applied to it = NO error
                       )
                        
                       So, the bug in syntax-checking appears to be in the following situation: When a calculation directly references an unrelated field with a GetFieldName wrapped around it, there is no problem. However, when result of that is included anywhere else, it fails.
                        
                       It seems that the syntax-checking code in FileMaker knows to allow an unrelated field if it is directly wrapped with GetFieldName in a calculation, but when an unrelated field is included somewhere in the chain indirectly, it doesn't see if the unrelated field reference is wrapped with GetFieldName.
                        
                       Note that the above (this bug) is true upon the syntax-checking that occurs when you try to hit OK to save a Calculation field, Auto-Enter Calculation on a field, and Validation by Calculation of a field. 
                        
                       Trying to apply any function on the line where you define the variable isn't much of a work-around, since the whole point of defining a variable in a Let statement is often to then use that variable to make code more legible. This bug essentially removes the ability to use the Let function in conjunction with GetFieldName most cases. 

                        

                  • 6. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
                    danshockley

                         Found a work-around:

                         Whatever function you want to use (Upper in the example above) can be replaced by a Custom Function that does the exact same thing. In fact, it can even be a simple wrapper for the built-in FileMaker function. 

                         So, for example: there is a custom function defined as: 

                    HTC_Upper ( someText ) = 

                         Upper ( someText )

                          

                         Then, in your calculation field, the following DOES work without getting a Parse Error when FileMaker does syntax-checking when you hit OK to save your calculation: 

                          

                         Let([
                         s = GetFieldName( TableB::SomeUnrelatedField )
                         ];
                         HTC_Upper ( s )
                         )
                          
                         It seems that the syntax-checking is more forgiving when it checks the parameters going into a custom function than it is for built-in functions. 
                         So, this is a crazy work-around, but it means you can actually use Let statements and GetFieldName to build, for example, complex SQL statements. You just have to make a custom function version of every built-in function you need to use on the variable(s) that are defined with a reference to GetFieldName ( <some unrelated field> ). Of course, this will be a problem when the built-in function allows optional parameters, since you can't do that in your custom function version. That will limit use of ExecuteSQL's dynamic parameters in FileMaker 12, so this isn't a complete work-around. 
                          
                    • 7. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
                      philmodjunk

                           The point I have tried to make here is that you SHOULD get an error message in these cases. It's just that the error message returned is not the correct error.

                      • 8. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
                        danshockley

                             Phil, the problem is that your point is wrong, as Todd pointed out above. The GetFieldName function IS allowed to use unrelated fields as a parameter. In fact, it DOES works as it should in most cases. The only problem, as documented extensively above, is that the syntax-checking is wrong when it is used indirectly via a Let variable. Interestingly enough, even in the situations above if you make a temporary relationship to the other table occurrence, save your calc, then break the relationship, the calc functions properly as expected and as it should. The bug is entirely in the syntax-checking: it erroneously gives a "Parse Error" when the GetFieldName ( <any unrelated field> ) is indirectly called by some other FileMaker function. 

                        • 9. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
                          philmodjunk

                               I had to open FileMaker 11 and run a test to confirm that, but now I stand corrected. I don't find any info in the help file that indicates that fields can be (or cannot be) referenced by GetFieldName, but that's definitely how it works in older versions.

                               In some cases, you might temporarily define the field to be referenced as a global field--in a few cases that may be quicker than building an extra relationship, as a different work around.

                               In any case, this report has been added to the Known Bug List.

                          • 10. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
                            danshockley

                                 Thanks for adding that to the Known Bugs list. 

                                 As you pointed out by saying "in some cases," people will have to be very careful with the temporarily-switch-field-to-global work-around, since that will wipe out any data in the field. The temporary relationship method will work, but is annoying. Also, either of those work-arounds mean you cannot then edit the calculation without putting the "temporary" work-around back in place. 

                                 I hope they fix this one soon. It makes using indirection more difficult to implement in a way that is durable to field and table name changes. 

                            • 11. Re: "Parse Error." when using ExecuteSQL  or Evaluate() with GetFieldName() in a field calculation
                              electric_soul

                                   how are the chances that this is being fixed in 11 ?