SUM Detail Master

Hi Panagiotis,
To calculate totals you can create read-only properties in your objects, marked with the PersistentAlias attribute, and specify the total expression in it. For example:

Visual Basic
Public Class DocumentLine  
    Inherits XPBaseObject  
                ...  
    <PersistentAlias("Quantity*Price")> _  
    Public ReadOnly Property Amount() As Decimal  
        Get  
            Return Convert.ToDecimal(EvaluateAlias("Amount"))  
        End Get  
    End Property  
End Class  

Visual Basic
Public Class Document  
    Inherits XPBaseObject  
    ...  
    <PersistentAlias("DocumentLines.Sum(Amount)")> _  
    Public ReadOnly Property Amount() As Decimal  
        Get  
            Return Convert.ToDecimal(EvaluateAlias("Amount"))  
        End Get  
    End Property  
End Class  

Use similar code in the Item and Customer classes.
Please let us know if this solution meets your requirements.
Thanks,
Michael.

Hi Michael
I have modify my classes according to yours suggestion. There is new example with demo data( only 10 Customers, and 9 Items). Please try edit Document (Invoice) and add new row to DocumentLines or change a customer.
The speed of LookupEditors for Item or Customer is very slow !!!.
If you can suggest solution.
Please down not point to example E1477 or E305 I have see them.

Hi Panagiotis,
Please accept our apologies for the delay in responding. We couldn't find an immediate answer or resolution. Please bear with us. We will inform you as soon as an answer is found.
Thanks,
Michael.

Hi Panagiotis,
Bad performance is caused by the fact that XPO cannot generate a single SQL query for nested calculated properties. So, it retrieves all referenced DocumentLine objects, and calculates the aggregate function on the client side. I have registered a suggestion to support this scenario in the future: Optimize SQL query generation for nested persistent alias properties. Please track it to be notified when its status is changed.
Currently, you can improve performance, only by executing an explicit SQL query, like this:

SQL
SELECT Sum(dl.Price*dl.Quantity) AS s  
FROM (Customer AS c INNER JOIN Document AS d ON c.Oid = d.Customer) INNER JOIN DocumentLine AS dl ON d.Oid = dl.Document  
WHERE c.Oid = @p;  

See also:
Direct SQL Queries.
Thanks,
Michael.

Hi Michael
Thank you for your answer.
I have modify my classes according using example E305. (How to: Calculate a Property Value Based on Values from a Detail Collection.) So the speed of LookupEditors is solved, but there is small problem, Please try edit Document and add new row to DocumentLines choose Item and move to field Quantity. The speed is very slow. How to resolve this. I want mention that this after choosing the item there are other jobs to be done (as find last price of the customer or assign price from agreement, select vat category and etc). Please suggest solution.
And one small suggestion. The XPO object should create index for field the customer in the table Document and for field Document and Item in table the DocumentLines
(I have running applications but I am afraid as data are growing and I want prevent bottleneck in the future so this is simplified sample.)
Thank you in advance.