February 21, 201015 yr Newbies 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.
February 21, 201015 yr 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.
February 21, 201015 yr That is the original thread. There are quite a few others where the same principle has been applied and expanded.
February 21, 201015 yr 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
Create an account or sign in to comment