I am trying to implement a Drag and Drop from a listing (not a portal) and I have my global variables working correctly, marking the start ($$Drag) and end ($$Drop) records. All I need to do is place the $$Drop variable back in the $$Drag record "order" field. I'm using the OnObjectEnter script trigger to get the id into the $$Drag variable and the Auto Calculation to place the $$Drag value into the destination "order" field, then use OnObjectModify script trigger to get the record number for that field ($$Drop) so I can put that back in the starting record. I want to use filemaker SQL because I don't wan't to clutter up my Relationship graph.
I'm having trouble with the SQL syntax for an update statement in the script step ExecuteSQL (as a script trigger). Here's what I have and it seems to run except the system wants an ODBC source. Execute SQL [UPDATE Course_Weeks SET order = $$Drop, WHERE WeekNumber = $$Drag]
I have been using the ExecuteSQL function elsewhere successfully with something like this:
~TableName = GTN ( Get ( LayoutTableName ) );
~FieldOrder = GFN ( Course_Weeks::order );
~FieldWeek = GFN ( Course_Weeks::WeekNumber );
~SQL = "Update " & ~TableName & " SET " & ~FieldOrder & " = " & $$Drop & " WHERE " & ~FieldWeek & " = ?"];
ExecuteSQL ( ~SQL ; ""; ""; $$Drag)
But this doesn't work at all (get the ? result). Perhaps because the Let statement works with the ExecuteSQL function and not the script step. So, where have I gone astray?
Thanks in advance;