1 Reply Latest reply on Jan 24, 2014 10:53 AM by philmodjunk

    Help with creating totals in a complicated table



      Help with creating totals in a complicated table


           I created a solution that tracks clients, employees and hours spent by each employee on their assigned accounts.

           I've been tasked with creating a report, which would be exported to Excel, which would have the clients in rows and employees in columns, show in each cell how many hours each employee spent on each client.

           Since both clients and employees are ever-changing, and the data is pulled from separate tables, the only way I could think to do this was by creating looping through the relationship-found-set of employees and getting their names and then hours to populate the cells for each row/client. Maybe there's an easier way (definitely open to it), but I got this to work.

           So basically I have generic fields, numbered 1-100 (we currently have < 50 employees). My script changes the relationship based on a variable counter to populate each field (user01, user02, user03, ...) using Set Field By Name in a loop to traverse across the "user" fields.

           The problem is, for the Excel export, the totals need to show up as the last row, after the last client, with the totals populating those same generic User01 fields.

           I've set up a loop and tried to use the same process, but it's not working. I've tried using the Sum function, along with the GetFieldName function (so that I can use the same variable to get the sum for each field), but it won't allow an "expression." I've tried setting that same with a matching summary field (the summary field has the right number), but I just get a question mark. Any thoughts? Am I doing this fundamentally wrong?

        • 1. Re: Help with creating totals in a complicated table

               You'll definitely need to use a different method for getting the totals. But you may not need to do as much data copying as you are currently doing and the change that I am about to suggest can simplify how to get your totals.

               Instead of copying the data, link to it. Each of the fields that form your columns of data, one column for each employee can be calculation fields that compute a total from the table where this data is recorded. The calculation fields can each use a different set of match fields to match to the correct set of records such that Sum ( RelatedTable::Field ) will compute the needed total, or (if you are using FileMaker 12 or newer) you can use ExecuteSQL to access that info. With either method, you'd have two pairs of fields for each such column of data. In one field you select the EmployeeID of the employee whose data will appear in that column. The other field is the calculation field that computes the needed total. The table where you set this up would then need to have one record for each client in order to set up your "One row for each client" format. In any given record, each of your employee fields uses the employeeID specified for that column and the clientID specified for that record to access the needed group of related records used to produce your total.

               Your summary totals can now be computed from a set of summary fields, One Summary field for each column of Employee totals.