Jump to content
sal88

Select sum related records

Recommended Posts

Hi all

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:

Clients
Client_ID, Seats
Jobs
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?

Many thanks

Share this post


Link to post
Share on other sites

the problem may well come from limitations in the ExecuteSQL function. Nested functions with the aggregates ( SUM, COUNT, etc.) don't always work as expected.

You may well do the SUM as a caclulation in Clients (use ExecuteSQL) to get the time in the last 30 days.

Or use a Let() statement to get the sum of the time (no join needed) in the last 30 days and push to a variable that can be then divided by the Seats. Use this field to FIND the Client with the "most".

I probably would NOT use a calculation (unstored), but a looping script to Set Field.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By mike13
      I am in the process of connecting an existing FileMaker database to a SQL server that is behind a web interface.  I wanted to know if there is a way to send a command from the web interface to kick off a sync.
      For example, a user opens a record on the web, makes some changes, hits a save button.  
       How do I get that "Save" to trigger a sync?
       
      And can I get it to sync that single record or do I need it to sync all records that have changed?
      Thanks in advance,
      Mike
       
    • By Eli Walker
      Hi all,
      I'm trying to use execute sql to populate a field with the sum of all the values in a field of related records. All I get is a ? so obviously something is wrong with my syntax but I cannot figure it out. Any help much appreciated! This calculation is occurring within the 'Collections' table.
      Let ( [
      query = "
      SELECT SUM(S.Volume.used)
      FROM \"Straws_for.entry\" AS S
      JOIN \"Animals.to.Collections_join\" AS A
           ON \"S.fk_a.to.c.join\" = \"A.pk_join\"
      WHERE \"A.fk_collection\" = ? " ;
      collection = Collections::pk_collection ;
      result = ExecuteSQL ( query ; "" ; "" ; collection)
      ] ; result )
       
      Also, after some reading on similar threads I see people suggest not using this function in a calc. field... if there's any better way of doing it I'd love to hear it. Thanks for the help, really appreciate it.
    • By Ponderosa
      I have a set of order forms, in which each order form has X amounts of orders from X amounts of clients. I can summarize with a Summary field the number of orders each client has ordered on one order form (weekly order form) and now I want to make a report that tells me the breakdown from week to week, and quarter to quarter. I can get the report to break apart the weeks by quarter, and it displays each week's correct total, but it won't do a total sum of each quarter, let alone for the entire database.
      I've tried summarizing the weekly summary field to get the quarterly amount, either in a new field, or on a trailing sub-summary part. The new field just copies the number from the weekly summary field from the record that is active, and the other way just shows the last above record. Wanna pull my hair out!
      I have a feeling that the problem is in how I set up the original weekly summary field, but I just can't see how, or how to make it work.
    • By sal88
      Hi all
      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?
      Many thanks
×

Important Information

By using this site, you agree to our Terms of Use.