I have the following setup:

Four tables: Customers, Orders, Order_Line_Items and Items

They are related:

Customers::Customer_ID = Orders::Customer_ID

Orders::Order_ID = Order_Line_Items::Order_ID

Order_Line_Items::SKU = Items::SKU

I have a portal in my customers table which displays records from Items table.

That way I have a list of the items a customer purchased on the customer layout. If a customer purchased the same item across multiple orders the portal displays only one item as intended and that part works fine.

I run into an issue when I want to show how many of that item the customer purchased across multiple orders.

I got as far as getting total quantity for all of the items regardless of the item so what I now have in my portal is:

Item001 5pcs

Item002 5pcs

I should have

Item001 4pcs

Item002 1pcs

I believe I know why this is happening, my calculation is basically Sum(quantity) from Orders_Line_Items which sums quantity for all of the items across all orders, however I need it to sum all quantities across orders for a specific item - this is the part where I got stuck.

If someone has a tip to point me in the right direction, it is greatly appreciated.

Thank you.,

Hi,

I have been watching this post with some anticipation - hoping that somebody would chime in...

I do recall Soren posting a really useful link to one of JMO's (I think) videos from the early days of FP7 and how relationships/table occurrences work - unfortunately I can't find the post and was hoping that Soren would repost...

I wish I had something more constructive to add but am deeply interested in anything further on this post

Edited October 19, 200817 yr by Guest

Try this custom function:

http://www.briandunning.com/cf/894

Sum a numeric field specified by SumField across multiple records, for records where field specified as TypeField matches a value such as "Labor" or "Parts". If you enter "Total" as the type then you get a total for all records.