3 posts in this topic
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?
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