Solved

get the sum of column in a grid and add to the header level

  • 4 April 2023
  • 3 replies
  • 302 views
O
Rank
Userlevel 3
Badge

Hi,

I have a custom screen , I need to get the total amount of the header level based on the sum of line amount of the grid level. 

as per above screenshot, I have taken the multiplication of A and B to the C column. Likewise I have to get the value in C column for each row which were added in the grid, Then I need to pass the sum of C column to the D field in the header level. for that I have added below code snippet in rowinserting event handler but it’s not working, only the C value is calculated. 

decimal? amount = this.APProformaItems.Select().FirstTableItems.ToList().Select(x => x.Amount * x.ProFormaQty).Sum();

// Acuminator disable once PX1048 RowChangesInEventHandlersAllowedForArgsOnly [Justification]

item.DetailTotal = amount;
 decimal? LineAmount = row.ProFormaQty * row.Amount;
 row.TotalAmount = LineAmount;

ApproformaItems = view for the grid

 

Can someone suggest a way to achieve this?

Thank you.

icon

Best answer by Dioris Aguilar 4 April 2023, 20:25

View original

3 replies

Dioris Aguilar
Rank
Userlevel 5
Badge +2

@oshadarodrigo64 You can accomplish that by using the PXFormulaAttribute:

[PXFormula(typeof(Mult<fieldA, fieldB>), typeof(SumCalc<Header.fieldD>))]

Put it as a DAC attribute for Field C.

It will calculate the multiplication of fieldA and fieldB of the current row and put the result in the current field where the attribute was placed (Field C) and it will calculate the sum aggregation (considering all detail rows) in the header field specified (Field D)

O
Rank
Userlevel 3
Badge

@Dioris Aguilar 

Thanks for the response, your solution worked. But sometimes I need to change this A field value by own instead of the default value, after changing the value, other columns also need to change accordingly. For that I used fieldUpdated event for the A field and added the below code.

//calculate total amount in header and details level

decimal? amount = this.APProformaItems.Select().FirstTableItems.ToList().Select(x => x.Amount * x.ProFormaQty).Sum();

item.DetailTotal = amount;

decimal? LineAmount = row.ProFormaQty * row.Amount;
row.TotalAmount = LineAmount;

This time line level is okay but the header value total amount (D) is not changed accordingly. I need D filed to change into 0 just after editing the A and update with the new total.

Can you help me to accomplish this scenario as well?

Thank you.

Dioris Aguilar
Rank
Userlevel 5
Badge +2

@oshadarodrigo64 That scenario should be handled by the same PXFormula attribute. Please, confirm that field C is defined after field A & field B in the DAC class and field A and field B have commitchanges=true in the aspx.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved