I'll post one thought below. I will be interested to see what others suggest.
You might try the following and see what happens:
1) Define a Custom Funcion, MyQueryString, as follows:
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:
TPA = TSK_assembly::templateID;
TPT = TASK::templateID;
q = MyQueryString;
result = ExecuteSQL ( q ; "" ; "" ; TPA ; TPT )
GetAsNumber( result )
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).
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?
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.
Sent from my mobile device...
Please excuse typos.
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.
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.
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.