problem with summery invoice portal

[Mike Frog]

I have a common type of database that handles customer information and invoices using the following linked tables.

Clients –< Invoices –< Line Items >- Products

What I am trying to achieve on the CLIENT LAYOUT is a portal that lists the products that customer has ever ordered and the volume to date with a valuation based on the latest price indicated in the products table

i.e. Product Quantity Latest Price Valuation

Product A 10,000 $5.00 $50,000

Product B 12,000 $2.00 $24,000

This assumes the client ordered both products, so if they had only previously ordered Product B it would show just. I think Filemaker does this automatically.

Product Quantity Latest Price Valuation

Product B 12,000 $2.00 $24,000

I am new to Filemaker and am using FM Pro 10 advanced. The result I am getting is like 1 global value across all records, not individual for that client. I am sure that I am making a error in the way my T.O.'s and/or relationships and calculated fields are set up.

You help is greatly appreciated.

[comment]

This is not a simple task. You can easily see from the Clients table which products the client has bought (just place a portal to Products on the layout of Clients) - but not the quantities. From the Products table's point-of-view, each product can sum up the quantities from the related records in LineItems - but these are totals of sales to ALL clients.

There are ways to group the related LineItems' quantities by product (search the forums for "Ugo's method"), but if you're just starting with Filemaker I'd suggest you use the traditional approach - i.e. produce a report from the LineItems table, summarized by client and by product.

[Lee Smith]

Hi comment

Is this the "Ugo's method" you are referring to?

Link

Lee

[comment]

That is the original thread. There are quite a few others where the same principle has been applied and expanded.

[Lee Smith]

Doing a search for "Ugo's method" only returns two hits. The one above, and this one Link where Søren pointed me to it.

Lee

[comment]

Doing a search for "Ugo's method" only returns two hits.

I get 83.