Who Viewed the Topic1 member has viewed this topic:dpwilton
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?
By Mark Stuller
We are going to take the plunge and setup a sync between our Oracle tables and 'shadow' FileMaker tables and we're seeking input.
One thing we are wondering about is a 'one-way' sync. We don't want changes to be sync'd back to Oracle. The bigger issue is that we don't want deletions in the Oracle table to be deleted in the FileMaker table. There often are children to these records we don't want deleted or orphaned.
As well, are there any gotchas we should consider? I've read the Advanced topics and feel mostly comfortable but you never know what you don't know!
I need some help with ExecuteSQL.
I have a TO “Sales” that is related to Estimates, Inspections, and Proposals via ID_SALES:id_sales.
I created some simple ExecuteSQL calculations that work on Estimates and Inspections, but the same calculations will not work with the related Proposals TO.
· I know that the relationship is good because I can get the SUM and COUNT from Proposals with a basic calculation (not ExecuteSQL).
· I know there is not a problem with the name on the related Proposals TO (like a space between words). I've copied and pasted the working calculations and I have started from scratch, and neither works.
This statement works:
ExecuteSQL ( "SELECT COUNT
(ID_ESTIMATES) FROM TO40_ESTIMATES
WHERE ID_SALES = ? “ ;
"" ; "" ;
This statement does not work:
ExecuteSQL ( " SELECT COUNT
(ID_PROPOSAL) FROM TO26_PROPOSALS
WHERE ID_SALES = ? ";
"" ; "" ;
Any help would be appreciated.
By Ben Moore
I am trying to create a list of dates from multiple tables to create a gantt chart. My projects have Shipping dates, Installation dates and task item dates. Each of which come from their own tables. Is there a good way to import all of these dates into one table related to the project, each with it's own record of Start date and end date.
I'd rather have:
Project | Item | Start_Date | End_Date|
Project | Install_Start_Date | Install_End_Date| Ship_date | Ship_Arrive_Date | Task_Start_Date | Task_End_date