I am using FMPA 14.
I have a bit of SQL created by the excellent SQL Explorer from SeedCode. I then transported the output into the database I am creating.
The SQL has two parameters, 1 a text one, and the other a numeric one.I am enclosing the text of the code here:
// Built by SQLExplorer. Compliments of SeedCode… Cheers!
Let ( [
// Define Carriage Return Substitution Character
ReturnSub = "\n" ;
// Enable the second line here if you want the header in your results
header = "";
//header = "a.__ChoiceId";
// Define Table variables
aCHOICES = Quote ( GetValue ( Substitute ( GetFieldName ( Choices::__ChoiceId ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;
// Define Field Variables
a__ChoiceId = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Choices::__ChoiceId ) ; "::" ; ¶ ) ; 2 ) ) ;
aChoiceText = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Choices::ChoiceText ) ; "::" ; ¶ ) ; 2 ) ) ;
a_EventId_fk = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Choices::_EventId_fk ) ; "::" ; ¶ ) ; 2 ) ) ;
// Build SQL Query
"SELECT " & a__ChoiceId & "
FROM " & aCHOICES & "
WHERE " & aChoiceText & " = ? AND " & a_EventId_fk & " = ? " ;
// Run SQL Query
result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $$Test ; $$SelectedEventId ) ] ;
// Clean up carriage returns
List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) ) )
I have tried to get this working in a calculation field, in a script, but also in the Data Viewer. When the code was copied, the line in RED was actually
result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; "AMO" ; "1") ] ;
In this form, it worked and found the required record from the table CHOICES.
In order to make the code more useful, I tried to substitute the two parameters as Global Variable.
It no longer worked.
I have tried switching the order of the variables, to no avail.
I tried to substitute one parameter at a time.
When the value "1" was changed to $$SelectedEventId it works.
When the value "AMO" was changed to $$Test it doesn't work.
Just in case $$Test was a reserved word or something, I have used other Global Variable names, and nothing works.
Instead of an error - "?" being returned, it just doesn't return anything. The record & field I want returned is definitely in the file CHOICES.
I am at a loss. Can anybody please help me!!