Get Syntax error when trying to do SUM Using "IN"

BenHayat

A hypothetical example to show my point.
Let's say Customer Bo has Invoice BO as Child and and Invoice BO has Item BO as Child. So basically, Item becomes a grand child of Customer.

I want to calculate a total _Purchase for a customer. This what I tried to do and get Syntax Error.
In here traversing from Customer towards Items
Customer.Total_Purchase = SUM Item.Price WHERE (Item IN Customer.Invoices.Items)

But If I do it as following works. Here I'm traversing from Item back to customer
Customer.Total_Purchase = SUM Item.Price WHERE (Item.ParentInvoice.ParentCustomer = Customer)

tford

I'm guessing the 2nd syntax is correct because Item.ParentInvoice.Customer is singular. Each Item can be found and evaluated by AwareIM in this scenerio.

With the first syntax (Customer.Invoices.Items) both Invoices and Items are multiple allowed so AwareIM can't handle that.

Good to know that the 2nd syntax works for future reference.

Alternatively, it seems like each Invoice should also have an Amount attribute, so the Customer BO could simply sum the Customer.Invoices??

BenHayat

tford wrote
With the first syntax (Customer.Invoices.Items) both Invoices and Items are multiple allowed so AwareIM can't handle that.

Thanks Tom;
If I were doing Customer.Invoices. like many places I have before, it works. But here I have collection in collection (Invoices.Items) and Aware doesn't like.

Alternatively, it seems like each Invoice should also have an Amount attribute, so the Customer BO could simply sum the Customer.Invoices??

Absolutely. But I just used customer, invoice as an example, in my real app, the middle object (i.e. invoice) doesn't need a total. Otherwise that would solve it with two "Total" rules.

Feedback: However, going with a second syntax, I also did some performance testing and came up with a faster solution that made a difference.
I created a relationship between Customer and Items and then used the IN operation. Item IN Customer.Items
This turned out to be much faster than going through each invoice to find it's items.