3 Replies Latest reply on Nov 19, 2013 9:15 AM by philmodjunk

    Printing portal records with accurate master table summaries

    BrianWyszynski

      Title

      Printing portal records with accurate master table summaries

      Post

           Ok PMJ...

           Lets see if you can get this one.

           If the Work Record Totals are Stored in the Master Table... How do I create a non-portal report that summarizes
           Work Record Items by (Sort Groups) Work Record Month / Work Record Client / Work Record No / Work Record Items and
           accurately sums all Work Record Total Time / Client Total Time / Monthly Total Time if the Work Record Total Time is
           replicated for EVERY record in the Work Record Items list.  The totals are calculating as Sum (Work Record Total Time X Number of Items per Work Record)per client.

           So a Work Record with 3 items and a total time of 3:00 is calculating 9:00 for that Work Record etc...

           Ive created a calc field in the Work Record Items table: Work Record Per (Work Record Total / Work Record Item Count )to get a more accurate count, but a Summary Field of the Work Record Per makes no Mathematical sense.

           i.e. Work Record Hours Per (3:00 / 3 items) = 1:00 each
                 Work Record Hours Per (2:00 / 2 items) = 1:00 each
                 Work Record Hours Per (0:45 / 2 Items) = 0:22:30 Each
                 Work Record Hours Per (3:30 / 2 Items) = 1:15 Each
           and
                 Client Total (Total of Work Rec Hours Per when sorted by CliCod) = 14:52:30?

           I need to evenly divide the total time for the work order across the number of work order items
           when sorted by month / client / work order no.

           Help please.

           Brian M W
            

            

        • 1. Re: Printing portal records with accurate master table summaries
          philmodjunk

               So you have this relationship?

               Clients----<WorkRecords

               and does this example:

               Work Record Hours Per (3:00 / 3 items) = 1:00 each

               represent 3 work records or one?

               Can you post a bit of a "mock up" of how you want this report to look?

               This looks like something that can be produced with  a summary field in combination with a calculation field that uses GetSummary(), but I'm still piecing together the key details here.

          • 2. Re: Printing portal records with accurate master table summaries
            BrianWyszynski

                 Yes, you are correct.
                 Client Info (client code, ...) =< Work Records (work rec no, total hours, total product, mileage, travel)=< Work Record Items (equip id, desc, prod code, prod cost)

                 Need to print out all Work Record Items with summary fields for Monthly Totals, Client Totals, and Work Record Totals

                 Work Record Items has Product Code : Product Cost, which is being summarized as Product Total in Work Record.

                 Work Record Items is Portal in Work Record Edit and can have any number of Items, each with a product attached (optional)

                 Work Record Also has Start Time, Finish Time, and Total Time (finish - start), Mileage In, Mileage Out & Travel In, Travel Out, and
                 Calc Fields Combined Mileage and Combined Travel.

                 The point of the report / print is All Work Record Items Grouped by Month, Client and Date and including Total Work Order Hours,
                 Total Work Order Product, Total Work Order Mileage and Total Work Order Travel Time.

                 Because Hours, Product Total Mileage and Travel are attached to Work Orders and not Work Order Items, using Calc Lookup to
                 pull the info back into a the Work Order Items table applies the same number to each matching record in Work Order Items.

                 I have created a self join of Work Order Items based on Work Order No.

                 So the end report is based on Work Record Items:
                 Header/Title header
                 -Month Summary (month name)
                 --Client Summary (client Name)
                 ---Work Order Summary (work order no)
                 ----Body
                 ---Work Order Summary (total hours, product, mileage, travel)
                 --Client Summary (total hours, product, mileage, travel)
                 -Month Summary (total hours, product, mileage, travel)
                 Footer

                 Thanks in advance for the help.

                 :-D
                  

                  

            • 3. Re: Printing portal records with accurate master table summaries
              philmodjunk

                   The details can be maddening at times, but I still have questions:

                   

                        Need to print out all Work Record Items with summary fields for Monthly Totals, Client Totals, and Work Record Totals

                   Totals? or Averages? Both? your previous example was producing an average value by dividing a subtotal by a count.

                   I see that we have three tables:

                   Client----<WorkRecords---<WorkRecordItems

                   Your report outline refers to "WorkOrder". Does one work order mean one record in WorkRecords?

                   Your report item includes a row labeled "Body" but doesn't specify what you want shown in the body of this report...

                   If WorkOrder = WorkRecord, then I'm not really seeing where WorkRecordItems fits in here in terms of data shown on your report. It, in fact, looks like a very typical summary report with sub summary layout parts, summary fields and some sorting to correctly group the records for the report.