3 Replies Latest reply on Aug 11, 2010 10:24 AM by philmodjunk

    How to use calculations with a subset of records in a table



      How to use calculations with a subset of records in a table


      I'm attempting an application to track, among other things, work assignments of a company's employees.  I am designing an Employee table which will have much of the typical employee info including the number of hours each employee works (both full and part-time employees).  I plan on using summary fields to display number of employees, average salaries, etc.

      I also plan on having a Work Assignment table which contains the assigments for each employee, using their EmployeeID from the Employee table.   Employees generally will be assigned multiple assignments and each assignment will specify the number of hours allotted to the assignment and those hours could be inside a day's worth of hours or span several days.  From this information I need to create a Layout which shows each employee and the % of time each employee is being utilized over a given period of time, say the next quarter for this particular Layout.  Since I need to use a calculation against subsets of employee assignment records in the table I do not see how summary fields or sub-summary sections will help me.

      Is there a way I can calculate each employee's  % utilization and store it in the Work Assignment table?  If so how would I do that, and if not what should I do?  It would also be very helpful as I am making work assignments to have an employee's utilization % updated dynamically.  How could that be accomplished?

      Suggestions welcome.


        • 1. Re: How to use calculations with a subset of records in a table

          Do you want utilization based on work assigned or work actually performed? (You assign Joe to work 24 hours on project x--only ot have him come down with the Flue and you have to assigne Mary to do the work in his place...)

          Generally speaking, you should be able to create a summary report based on the table where you record worked or assigned and put fields from the related employee table in sub-summary parts. You can peform finds and sorts to control which records are included in the report (all records in the first quarter of this year for example) and how they are grouped. The trick is not to try this type of report in the employee table.

          There can also be ways to get totals shown on a layout based on your employee table when you use aggregate functions such as Sum(), average(), count(), etc. in calculation fields defined in your employee table.

          Here's a tutorial on summary reports that may help. It's based on a simplified invoicing system, but note how it bases a report on line items, but pulls data from the invoice table in sub summary parts.

          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: How to use calculations with a subset of records in a table

            First of all thanks for the information.  I will take a look at the suggested tutorial.

            To answer your question this is for planning work assignments only so I don't need to track planned work assigned against work actually performed.

            Finally it sounds as though I should be able to use calculations (e.g., calculate % utilization of each employeebased on work assigned) within sub-summary parts.  Is that correct?


            • 3. Re: How to use calculations with a subset of records in a table

              Yes, but use care in defining them to avoid unexpected results.

              a calculation such as A + B where A and B are simple number fields in the table referred to in Layout Setup... will be the value of one record in your database--no the entire groupl

              If you do A/sTotal where sTotal is a summary field. sTotal will be the total over all the records in the found set--not the current subsummary group. To get the subsummary's subtotal, use GetSummary ( sTotal ; BreakField ) where BreakField should be the same field specified as the "sorted by" field in the subsummary part.