Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum
elbosty

Grouping in reports

Recommended Posts

elbosty

i have a problem with summary report.

i am making a database for a print house where each customer has his own printed products.First i create a job order for a specific customer with JobOrder Line item for each product he wants with the quantity he wants. Next Each JobOrder Line Item will be delivered in one or more Delivery Order(s). Each Delivery Order can have Products from one or more Job Order, so the same product can be in one Delivery Order but from one or more JobOrder.

 

Delivery Order 1 has the following Delivery Order Lines:

DOLPK-----ProductFK-----JobOrderFK-----Quantity-----InvoiceFK

 

DOL01-----Prd01-----------Job01------------100------------Invoice01

DOL02-----Prd02-----------Job01------------100------------Invoice01

DOL03-----Prd01-----------Job02------------50-------------Invoice01

DOL04-----Prd02-----------Job02------------20-------------Invoice02

 

Delivery Order 2 has the following Delivery Order Lines:

 

DOLPK-----ProductFK-----JobOrderFK-----Quantity-----InvoiceFK

 

DOL05-----Prd01-----------Job01------------100------------Invoice01

DOL06-----Prd02-----------Job01------------100------------unInvoiced

 

 

Now what i want is to make Invoice01 Look like this

 

Invoice01

ProductFK-----TotalQuantity

Prd01-----------250

Prd02-----------100

 

i use a Calculation field in the Products table TotalQuantity = Sum ( DeliveryOrder Line Quantity )

but i always get the wrong sum, usually the sum of all quantities delivered for that product regardless of the Invoice Number.

Now how to fix that problem

 

i use Anchor Buoy method for relationships and i attached the part about the invoices.

i hope i gave a good description of the problem.

 

Invoices.jpg

Share this post


Link to post
Share on other sites
eos

Don't use a calc field in the parent table; as you have observed, it will always show you the aggregated values for all related records. What you want to see are the results for the current found set (i.e. all line items for an invoice); for this, there are summary fields.

 

So, create a summary field type TotalOf for Quantity, and a sub-summary part grouped by productFK. Place the summary field into the sub-summary part (and into the header to get the total), and sort by productFK.

 

If you don't want to display the line items (as in your example), you can delete the body part.

Share this post


Link to post
Share on other sites
elbosty

Thanks EOS

That Solved everything. :cheerful:

do i need to mark the thread is solved or anything like that?

Share this post


Link to post
Share on other sites
eos
do i need to mark the thread is solved or anything like that?

 

If so, I wouldn't know how … Good to hear you got it working.

Share this post


Link to post
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use