AnsweredAssumed Answered

Using SQL Update

Question asked by mark_b on Jun 24, 2014
Latest reply on Jun 25, 2014 by tomasd

Hi All,


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:


Let ([

~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;