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

# Printing portal records with accurate master table summaries

### 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.

Brian M W

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

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

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:
-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

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.