Solved: Aggregate references in 1 line

weblike

Hi,

Sounds simple and I now it is, but I am blocked ....

I have this scenario:

Order1 -> Value=10USD
Order2 -> Value=20USD
Order3-> Value=30USD

I want to create 1 invoice with 1 line with selected orders cumulated in 1 line.

So I have this process:
PICK ONE OR MORE FROM 'Orders_qry'
INSERT BO_Orders IN BO_Invoices.LinkedOrders

In BO_Invoices I have this rule:
If BO_Orders WAS ADDED TO BO_Invoices.LinkedOrders Then
CREATE Invoice_Line WITH Invoice_Line.Invoice=BO_Invoices,Invoice_Line.ServiceOrProduct='some text for invoice description',Invoice_Line.Quantity=1,Invoice_Line.Price=SUM AddedBO_Orders.Amount WHERE (BO_Orders IN BO_Invoices.LinkedOrders)

The result is:

IF I pick 2 orders -> 2 lines are created in invoice
- IF I pick 3 orders -> 3 lines are created in invoice
Question: "CREATE bo FOR EACH ...." should behave like this, or not?

I want to create only 1 line with cumulated values, no mater how much orders I choose.

I know this is easy for some of you and I'll appreciate if you give an idea....

Thx,

BenHayat

George, I think you're approaching it in a wrong way...

a) Create a new attribute in LoggedInUser, and call it TotalOrderAmount.
b) Do a Find for those orders.
c) Get a SUM for all the order amounts.
d) Store it in LoggedInUser.TotalOrderAmount.
e) Create a new record in your Invoice table, with your description and LoggedInUser.TotalOrderAmount.

It's much cleaner approach fro Aware's POV system.

weblike

Thx Ben,

Very good point. I will try this approach...

Cheers,

BLOMASKY

Why not put a calculated field in the customer (or whatever the parent record is) for these orders
called, mmm.errr..mmmm.. TotalOnOrder
and with a rule: Customers.TotalOrder = SUM Orders.amount WHERE Orders in Customers.om_Orders)

then, can print this from the Customers BO

(since you said print invoice, figure you do need name and address, etc.)

Bruce

BenHayat

BLOMASKY wrote
and with a rule: Customers.TotalOrder = SUM Orders.amount WHERE Orders in Customers.om_Orders)
Bruce

This should not be in a rule, because every time customer object gets changed, it runs this SUM action that can cost performance.
That rule should be part of the print invoice process.

BLOMASKY

well, not sure I agree with that. SInce you would prolly be changing one record at a time, the cost for updating the customer should be minimal. Now, if you were batch entering orders, or had about 1000000000 users entering at the same time, perhaps...

bruce

weblike

Hi,

Here is how I solved it....

Process:
//this picks the Orders and inserts in BO_Invoices
PICK ONE OR MORE FROM 'Orders_qry'
INSERT BO_Orders IN BO_Invoices.LinkedOrders

//*this updates the summarization attribute created in BO_Invoice
FIND BO_Orders WHERE BO_Orders IN BO_Invoices.LinkedOrder
BO_Invoices.TotalAmountOfThisOrders=SUM BO_Orders.Amount WHERE (BO_Orders IN BO_Invoices.LinkedOrder)

//*this finds if exists lines of this Invoice. If not, then creates the line with indicated parameters. If exists a line then only updates the Value (good if a order is add/deleted from BO_Invoices.LinkedOrders reference
FIND BO_Invoice_Lines WHERE BO_Invoice_Lines.Invoice=BO_Invoices
If SEARCH_COUNT=0 Then
CREATE BO_Invoice_Lines WITH BO_Invoice_Lines.Invoice=BO_Invoices,BO_Invoice_Lines.ServiceOrProduct='Taskuri facturate conform raport',BO_Invoice_Lines.Quantity=1,BO_Invoice_Lines.Price=BO_Invoices.TotalAmountForThisOrders
ELSE
BO_Invoice_Lines.Price=BO_Invoices.TotalAmountForThisOrders

this works for me...
Not tested for performance yet....
cheers