4 Replies Latest reply on Jan 19, 2015 8:35 AM by Stu412

    Using Sub-Summary results on another record and layout

    Stu412

      Title

      Using Sub-Summary results on another record and layout

      Post

      Hi there

      I have a report which by use of sorting and sub summaries gives me a figure of £X per client, per year.  A filter uses a global value to remove years I don't want to see.

      On the same row as £X, it has a field which returns the number of staff (S).

      I then need a completely different report to show unique rows per member of staff with £X showing on the same record.  For example, if on report one I had a customer with 5 staff, I need 5 records creating.  

      I've created a script to count staff, create (S) number of records and then loop back for the next customer.

      Now, I need to get the sub summary figure of £X to appear on each instance of the staff before that too loops back around for the next customer.

      I appreciate this sounds nuts but the particular demands of this task require either a table populated with new data, or an export to Excel and copy/paste work there.  It'd look so cool to keep everything in FM!

      So, the question is either:

      1) How can I get summary data to a brand new record?  I've tried GetSummary and $variables and neither has worked.  I understand the former only works on the same table though.

      2) I then thought 'Oh, can I create muliple sub summaries based on staff quantity?' but I think sub summaries always rely on the data being there first of all.

      Apologies if this sounds mixed up, this task defies the laws of physics!

      Thanks

       

        • 1. Re: Using Sub-Summary results on another record and layout
          philmodjunk

          You could use Getsummary to compute the sub total, copy it to a variable and then create the record.

          Also, a relationship from the other table's new record could, in theory be set up to match back to the records in your original table such that the records for a given employee only match to the records that were used to compute that original subtotal and then it's possible to refer to the existing summary field in the other table to get the same sub total.

          BTW, if I am remembering your original post, I did (and do) find this odd, but I also asked why you need to do this for another reason. Understanding what end goal is needed can open the door to alternative approaches that might achieve the same result through simpler methods. Unfortunately, the press of limited time and many other posts were such that I never got back to that thread to see your answer.

          • 2. Re: Using Sub-Summary results on another record and layout
            Stu412

            Hi Phil

            Really appreciate your time and help so far.

            The endgame is to have a report which allows analysis of upper and lower quartiles of profit shared per person. Let's say there are two customers, one with profits of £40,000 and 4 staff, the other with profits of £50,000 and 2 staff.  I'd need to share the profit first, so for business one I'd get 4 shares of £10,000 and for business two I'd get 2 shares of £25,000.  

            I then need to work out the upper and lower limits of the total range of shared profits, hence:

            DataID | Customer | Staff member | Profit share

            1          | 2             | 1                   | 25000

            2          | 2             | 2                   | 25000

            3          | 1             | 1                   | 10000

            4          | 1             | 2                   | 10000

            5          | 1             | 3                   | 10000

            6          | 1             | 4                   | 10000

            Average top range = (10000+25000)/2 = 17500.  Average bottom range = (10000+10000)/2 = 10000.  Mode = 10000 etc

            Hopefully this explains the rather apparently crazy need to duplicate the summary results by the number of staff :)  I figured the best place to dump this would be in a second table which can itself be either reported on or printed as final to PDF.

            I did look at the variable set up you suggested but I'm having trouble (I think) with the filtering.  There are many year's worth of data sets to pick from, for many customers.  In my fragile mind the variable is almost $var = summary of profit where customer = x and year = GF_Year.  I know that's not how it's written but that's how it sounds in my head!

            In SQL I'd do something like Select Sum from data where client = "1010" and year = GF_Year.  I tried that but it couldn't parse (hence the question marks in the results set).

            Thanks in advance :)

             

             

             

             

            • 3. Re: Using Sub-Summary results on another record and layout
              philmodjunk

              Yet this doesn't sound like a system where you need to create new records from a summary report. You would need a table of staff, with one record for each staff member and then match fields that limit which records in your other table link to it in order to show the necessary computed totals.

              Are the "staff" employees of the customer? If not, could a given staff member be assigned to serve more than one customer?

              I did look at the variable set up you suggested but I'm having trouble (I think) with the filtering.  There are many year's worth of data sets to pick from, for many customers.  In my fragile mind the variable is almost $var = summary of profit where customer = x and year = GF_Year.  I know that's not how it's written but that's how it sounds in my head!

              Once you have produced the original summary report, a script can loop through the records in that report--which have already been filtered the way you want them and getSummary will then compute the total that you want. The tricky part of this approach is to avoid creating multiple copies of the same record as you loop through the records of such a summary report.

              I tried that but it couldn't parse (hence the question marks in the results set).

              A question mark result means that you had a syntax error. That's not very informative of the function  and frustrating, but there are number of common errors that frequently result in a ? result. I'd need to see your query text before I might spot such an error.

              Resources for working with ExecuteSQL:

              SeedCode's SQLExplorer--it can write the ExecuteSQL expression for you: http://www.seedcode.com/cp-app/ste_cat/sqlxfree

              FileMaker's SQL reference document (a good place to find working SQL examples in correct Syntax for FileMaker SQL expressions): https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

              • 4. Re: Using Sub-Summary results on another record and layout
                Stu412

                Creating the staff table is the route I'm looking at, although I must say these staff are purely for the numbers as opposed to fully detailed records.  I'm looking to match details on this table to the source data table, in this case year end and customer id, then will sort it the same way.

                How do I copy a GetSummary to a variable, should it be normal or global?

                Thanks