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

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;
        }

@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;
    }

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.

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

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;
    }

   }
}

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!

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

@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;
    }

Okay.. that is Great  :) 

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

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.

@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.

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

@Naveen Boga 

Is there a way to accomplish this without coding?  Maybe by using Layout Properties in customization projects?

-David

Hi @dgross  It requires coding as per my knowledge.