Solved

sum records of a grid and show the total in a header value


Userlevel 1
Hello Goodnight. How can I put de sum of the “Vendor # 2 Amount” column (grid) in the Total field ( Vendor # 2).
the result in the “Vendor #2” field should be: 180.

 

I need to update the total value (Vendor #2 field) for each record the user adds in the grid (the user does not necessarily have to save the record to reflect the update in the “Vendor #2” field) too.

 

Additional: is a master detail form

 

could someone help me please.
icon

Best answer by eddiedaco 24 June 2021, 05:01

@Naveen B  yes, i activate that property but the results are the same.

 

@ddunn  yes, i used formula but i have the same result.

 

i solved it this way.

 

  • in the rowUpdated Event of the detail grid. i add the code that @Naveen B  shared with me.
// use this code to get the current data of Master table
CEBidLeveling item = viewCEBidLeveling.Current;

//Calculate the sum of values from the column InternalTotalAmount (detailTable)
// viewCEBidLeveling is my detail table
decimal? Vendor1Total = this.viewCEBidLevelingItem.Select().FirstTableItems.ToList().Select(x => x.Vendor_1TotalAmount).Sum();

//put the value of the sum in Master table column (viewCEBidLeveling)
item.Vendor_1Total = Vendor1Total;
change the values ​​of the columns (commit changes = true or AutoCallBack=true) that you want to trigger the changes (in my case the Vendor1_TotalAmount column) and that's it. When you enter real-time values, whether or not they are saved in the database, the column will be automatically recalculated.

 

thank you very much for your help. I hope my solution can help others.
View original

12 replies

Userlevel 6
Badge +8

Hi @eddiedaco,

Below is the sample code to display the sum of grid field records in header field.

protected virtual void SOOrder_DACFieldName_FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e, PXFieldSelecting InvokeBaseHandler)
{
InvokeBaseHandler?.Invoke(sender, e);
SOOrder row = (SOOrder)e.Row;
if (row == null) return;
decimal? DiscTotal = Base.Transactions.Select().FirstTableItems.ToList().Select(x => x.CuryDiscAmt).Sum();
e.ReturnValue = DiscTotal;
}

Userlevel 1

@Naveen B  tanks for your help. I got this error. (Sorry i´m new)

I show my code and I am putting it in my graph (I don't know if it should be put there). Aditional: I created a new customization.

 

 

  protected virtual void CEBidLeveling_Vendor_1Total_FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e, PXFieldSelecting InvokeBaseHandler)
    {
        InvokeBaseHandler?.Invoke(sender, e);
        CEBidLeveling row = (CEBidLeveling)e.Row;
        if (row == null) return;
        decimal? DiscTotal = Base.CEBidLevelingItem.Select().FirstTableItems.ToList().Select(x => x.Vendor_1TotalAmount).Sum();
        e.ReturnValue = DiscTotal;
    }


      

Userlevel 6
Badge +8

Hi @eddiedaco Since you are working on the new customization screen, please replace Base with this

We will use Base for only extended graphs, to access Views/Buttons and etc...which are defined in Base graph code.

Userlevel 1

Hi, @Naveen B thanks for your aclaration!.

I changed Base to this.

using System;
using PX.Data;
using PX.Data.BQL.Fluent; //se agrega esta linea
using System.Linq;

namespace Estimating
{
public class BidLevelingMaint : PXGraph<BidLevelingMaint, CEBidLeveling>
{
protected virtual void CEBidLeveling_Vendor_1Total_FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e, PXFieldSelecting InvokeBaseHandler)
{
InvokeBaseHandler?.Invoke(sender, e);
CEBidLeveling row = (CEBidLeveling)e.Row;
if (row == null) return;
decimal? DiscTotal = this.CEBidLevelingItem.Select().FirstTableItems.ToList().Select(x => x.Vendor_1TotalAmount).Sum();
e.ReturnValue = DiscTotal;
}

 

But i have this error.

\App_RuntimeCode\BidLevelingMaint.cs(15): error CS1061: 'BidLevelingMaint' does not contain a definition for 'CEBidLevelingItem' and no accessible extension method 'CEBidLevelingItem' accepting a first argument of type 'BidLevelingMaint' could be found (are you missing a using directive or an assembly reference?)
\App_RuntimeCode\BidLevelingMaint.cs(15): error CS1061: 'BidLevelingMaint' does not contain a definition for 'CEBidLevelingItem' and no accessible extension method 'CEBidLevelingItem' accepting a first argument of type 'BidLevelingMaint' could be found (are you missing a using directive or an assembly reference?)

 

  • “BidLevelingItem” is my detail table (and my DAC) and from where I need to extract the value of "Vendor_1TotalAmount" to display in my master table “BidLeveling” (Vendor_1Total).
  • I changed BidLevelingItem to viewBidLevelingItem (my view) and I had no errors but the code in preview mode doesnt refresh de Total_1Vendor (Master table: BidLeveling)
using System;
using PX.Data;
using PX.Data.BQL.Fluent; //se agrega esta linea
using System.Linq;

namespace Estimating
{
public class BidLevelingMaint : PXGraph<BidLevelingMaint, CEBidLeveling>
{
protected virtual void CEBidLeveling_Vendor_1Total_FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e, PXFieldSelecting InvokeBaseHandler)
{
InvokeBaseHandler?.Invoke(sender, e);
CEBidLeveling row = (CEBidLeveling)e.Row;
if (row == null) return;
decimal? DiscTotal = this.viewCEBidLevelingItem.Select().FirstTableItems.ToList().Select(x => x.Vendor_1TotalAmount).Sum();
e.ReturnValue = DiscTotal;
}

 

  • I dont kown how to obtain de value of cache data and do sum.
  • the objective is: when the value of the rows of the grid "Total # 1 Amount" (Detail) is updated (this value is saved in the database or not) automatically reflect the sum in the field "Vendor1_Total" (Master)

 

 

thank you very much for your help
Userlevel 6
Badge +8

Hi @eddiedaco  You need to remove the base code, since you are working with the new screen. please find the code below.

 


using System;
using PX.Data;
using PX.Data.BQL.Fluent; //se agrega esta linea
using System.Linq;

namespace Estimating
{
public class BidLevelingMaint : PXGraph<BidLevelingMaint, CEBidLeveling>
{
protected virtual void CEBidLeveling_Vendor_1Total_FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e)
{
CEBidLeveling row = (CEBidLeveling)e.Row;
if (row == null) return;
decimal? Vendor1Total = this.CEBidLevelingItem.Select().FirstTableItems.ToList().Select(x => x.Vendor_1TotalAmount).Sum();
e.ReturnValue = Vendor1Total;
}

}
}

 

Userlevel 1

Hi @Naveen B 

 

I removed the code but i have the same error.

\App_RuntimeCode\BidLevelingMaint.cs(15): error CS1061: 'BidLevelingMaint' does not contain a definition for 'CEBidLevelingItem' and no accessible extension method 'CEBidLevelingItem' accepting a first argument of type 'BidLevelingMaint' could be found (are you missing a using directive or an assembly reference?)

 

using System;
using PX.Data;
using PX.Data.BQL.Fluent; //se agrega esta linea
using System.Linq;

namespace Estimating
{
public class BidLevelingMaint : PXGraph<BidLevelingMaint, CEBidLeveling>
{
protected virtual void CEBidLeveling_Vendor_1Total_FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e)
//protected virtual void _(Events.FieldUpdating<CEBidLeveling, CEBidLeveling.vendor_1Total> e)
{
CEBidLeveling row = (CEBidLeveling)e.Row;
if (row == null) return;
decimal? Vendor1Total = this.CEBidLevelingItem.Select().FirstTableItems.ToList().Select(x => x.Vendor_1TotalAmount).Sum();
e.ReturnValue = Vendor1Total ;
}

I change the event declaration to:

protected virtual void _(Events.FieldUpdating<CEBidLeveling, CEBidLeveling.vendor_1Total> e)

 

but i have the same error.

Do you know what could be happening?, thanks!
Userlevel 6
Badge +8

What is the VIEW name for the grid ?

I’m assuming this is DAC Name→ CEBidLevelingItem but NOT the VIEW 

If this is DAC, please replace with VIEW name and check

 

Userlevel 1

@Naveen B  Its correct. CEBidLeveling and CEBidLevelingItem are DAC. viewCEBidLeveling and viewCEBidLeveling are the views.

 

I made the change (CEBidLeveling → viewCEBidLeveling) and an error does not appear, but nothing is calculated.

 

I modified de Event FieldSelecting with the new declaration and put the valor of the Vendor1Total = 79. (only for test). And i see the value in the header.

using System;
using PX.Data;
using PX.Data.BQL.Fluent; //se agrega esta linea
using System.Linq;

namespace Estimating
{
public class BidLevelingMaint : PXGraph<BidLevelingMaint, CEBidLeveling>
{
//protected virtual void CEBidLeveling_Vendor_1Total_FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e)
protected virtual void _(Events.FieldUpdating<CEBidLeveling, CEBidLeveling.vendor_1Total> e)
{
CEBidLeveling row = (CEBidLeveling)e.Row;
if (row == null) return;
decimal? Vendor1Total = this.viewCEBidLevelingItem.Select().FirstTableItems.ToList().Select(x => x.Vendor_1TotalAmount).Sum();
if(Vendor1Total==0)
{
Vendor1Total = 79;
}
//e.ReturnValue = Vendor1Total ;
row.Vendor_1Total = Vendor1Total;
}

 

I idid the same with the old event declaration and nothing happens.

 

  • i removed the 79 test value. but the Vendor # 1 Total (header) values ​​are not recalculated when I enter new data in the grid.

 

using System;
using PX.Data;
using PX.Data.BQL.Fluent; //se agrega esta linea
using System.Linq;

namespace Estimating
{
public class BidLevelingMaint : PXGraph<BidLevelingMaint, CEBidLeveling>
{
//protected virtual void CEBidLeveling_Vendor_1Total_FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e)
protected virtual void _(Events.FieldUpdating<CEBidLeveling, CEBidLeveling.vendor_1Total> e)
{
CEBidLeveling row = (CEBidLeveling)e.Row;
if (row == null) return;
decimal? Vendor1Total = this.viewCEBidLevelingItem.Select().FirstTableItems.ToList().Select(x => x.Vendor_1TotalAmount).Sum();
/*if(Vendor1Total==0)
{
Vendor1Total = 79;
}*/
//e.ReturnValue = Vendor1Total ;
row.Vendor_1Total = Vendor1Total;
}

 

Userlevel 6
Badge +8

Okay.. that is Great  :) 

BTW… Have you given the “CommitChanges = True” for the header field in .aspx file ?

Userlevel 1
Badge

Another option might be to look at using the PXFormula() attribute within the DAC’s fields to have the system automatically update the header field values instead of writing code to update it.

Userlevel 1

@Naveen B  yes, i activate that property but the results are the same.

 

@ddunn  yes, i used formula but i have the same result.

 

i solved it this way.

 

  • in the rowUpdated Event of the detail grid. i add the code that @Naveen B  shared with me.
// use this code to get the current data of Master table
CEBidLeveling item = viewCEBidLeveling.Current;

//Calculate the sum of values from the column InternalTotalAmount (detailTable)
// viewCEBidLeveling is my detail table
decimal? Vendor1Total = this.viewCEBidLevelingItem.Select().FirstTableItems.ToList().Select(x => x.Vendor_1TotalAmount).Sum();

//put the value of the sum in Master table column (viewCEBidLeveling)
item.Vendor_1Total = Vendor1Total;
change the values ​​of the columns (commit changes = true or AutoCallBack=true) that you want to trigger the changes (in my case the Vendor1_TotalAmount column) and that's it. When you enter real-time values, whether or not they are saved in the database, the column will be automatically recalculated.

 

thank you very much for your help. I hope my solution can help others.
Userlevel 6
Badge +8

Yes this will help others as well at some point. Thanks for sharing this.

Reply


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 — 2020  Acumatica, Inc. All rights reserved