I'm trying to export my filemaker records to my external sql database via the execute sql script step but am getting a number of errors.
I can see my external ODBC source and can generally write to it (I can go to the respective layout and click 'new record'). My query basically as follows:
"INSERT INTO dbo.Log ( dbo.Log.log_id, dbo.Log.log_case_idf, dbo.Log.Labour_TOTALS_Labour_Cost, dbo.Log.Labour_TOTALS_Item_Sale) SELECT Log_Log.log_id, Log_Log.log_case_idf, Log_Log.Labour_TOTALS_Labour_Cost, Log_Log.Labour_TOTALS_Item_Sale, FROM Log WHERE Log_Log.Log_Type = '2'" My first question is: is it even possible to export to an odbc source with a 'insert INTO SELECT' query?
I've been working with SQL databases for about 12 years now, and have stumbled across this monstrosity in a new position - and I really don't get the translation between Filemaker and SQL.
This should work according to the documentation:
ExecuteSQL ( " SELECT Customer_EVENT::Inv_dollars ) FROM Customer_Event WHERE Customer_EVENT::FlagReview = \" " ; "" ; "" )
...or something. I really can't even get a simple SQL Statement just pulling anything more than 1 field to work. Is there a reference out there (not Filemaker or FM14 Bible) that actually explains in detail WHY FQL statements are made the way they are and how each piece interacts?
I'm not sure I can do this with a calculation, but it's entirely possible. I'm just trying to get a SUM on a certain set of records to display SOMEWHERE in a layout. The fields are from a related table, but have nothing to do with the related records. It's just a way to SUM the objects in a portal by "filtering" the amount.
I am looking to add a line into the following code into the 'prepare payload for client' section to select only records that match the following criteria
I have a variable set called: $additional_settings The field that needs to match with $additional_settings is called _kf_uuid_companys the code that i have already is as follows, i need to add a line into this code to pick records where the field _kf_uuid_companys matches $additional Settings.
Any help would be greatly appreciated:
& $dyn_sql & ", '"
& $$record_delimiter & "'"
& " FROM \\"" & $sync_table & "\\""
& " WHERE " &
// Exclude records flagged for sync exclusion.
"( COALESCE ( BS_Exclude, 0 ) = 0 ) " &
// If the client is merging new/updated data with data already on the device...
// Only include records that have been added/updated since the last pull...
// And only include data that they did not just push (i.e. no "round tripping!")...
If ( ( $$sync_method = "Merge" )
( $last_pull_utc > 0 ) ;
"AND ( BS_UTC_Time > " & $last_pull_utc & " ) " &
"AND ( COALESCE ( BS_Device_ID, 'X' ) <> '" & $client_persistent_id & "') "; "" )
I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are:
Client_IDF, Job_time, Job_Date
The best I've managed so far is to find the distinct Client_IDF from all Jobs:
ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J WHERE j.Log_date >= ?"; ""; ""; $date_30_days_ago) And then loop through the list and perform the 'divide by seats' calculation for each line
Or to do something similar but from the Clients table:
ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C JOIN Jobs J ON j.log_client_IDF = c.Client_ID WHERE l.Log_date >= ?"; ""; ""; $date_30_days_ago) But no joy in either case when it comes to actually performing calculations within SQL.
Is this possible in FM SQL?