Solved

How to create a summary field that takes ALL filtering into account for a regular Inquiry screen?


Userlevel 3
Badge

This seems like a simple task, but I haven’t been able to find instructions that have worked.

I have an inquiry screen with a simple Filter DAC for the header and a Details DAC for the grid.
The grid contains a column “Commission”, and I need a summary field to display “Total Commission” either in the header or on the grid footer.

I’ve found an article Calculating Grid Totals on an Inquiry Form - Acumatica Developers Blog and followed the last (most advanced) example and I’m still getting inconsistent results when I have a mixture of header filters, shared filter tabs, and ad-hoc user filters applied.
I’ve been working on this for a few days and trying many different things, but always with mixed results.
If nothing else works, I’m willing to consider adding an action button that has to be clicked to refresh the total.

In a regular Generic Inquiry, a summary field can be defined for the footer, and the column total will reflect the total for all records in the grid view, regardless of how filters are applied to it. When paging is enabled, it will still show the total for all pages, not only records on the current page.

I want to apply this same type of calculation on my custom screen so that the total displays the sum for ALL records in the currently filtered data set. 
 

Is there no object I can access that holds all the records in the filtered data set? The counter at the bottom displays total number of records and if paging is turned on, it displays for example, (1-200 of 12345 records). So I know the system knows which records are in the data set.

This screen also has a number of actions that can be performed on multiple selected records, which is why we’re not using a Generic Inquiry screen.

 

icon

Best answer by andriitkachenko 12 July 2024, 15:15

View original

12 replies

Userlevel 2
Badge

Have you considered using an event handler, something like FilterDAC RowUpdated, so whenever the filter changes, it uses some logic to loop through the records that are now visible to set your field in the header equal to the sum of the Commission column?

Userlevel 3
Badge

@josiahl64 Yes, but unfortunately this will not help me because the event will not fire when a user switches to a different shared filter on the grid, or adds an ad-hoc column filter.

Badge +12

In a regular Generic Inquiry, a summary field can be defined for the footer, and the column total will reflect the total for all records in the grid view, regardless of how filters are applied to it.

I’ve wanted to know how to do this with a custom grid for a very long time.

Here’s what I know so far:

  1. Enable the grid footer:

     

  2. Enable the column footer type:

     

  3. Sigh / cry because although the footer shows and has sections for each column, nothing displays. I’ve tried change multiple grid settings to cause the update but no joy yet.
Userlevel 4
Badge +1

We’ve done something similar by calculating and setting the header field value in the details Dataview delegate method - while also using an additional action button that simply calls Details.View.RequestRefresh();.

 

I believe there’s also a call to the HeaderFilter.View.RequestRefresh() in the grid Row Selected event

 

Sometimes the header value gets set in code, but does not always update in the UI - so the action button updates the total field. It’s not the most ideal solution, but it seems to work well with quick filters in the grid.

 

Curious to see if anyone has a better method of handling this.

Userlevel 3
Badge

@mbridges00 The majority of my time was spent on attempting to implement this via a calculation in the Details Dataview delegate method like you mentioned. 
It makes logical sense, that since the delegate method gets called when the view is refreshed, I should be able to do some calculation with the “results” object right before it’s returned. 
 

I really wish we could get some answers on this from someone on the Acumatica development team. 

Badge +12

@Nayan Mansinha 

Userlevel 4
Badge

Hi @MichaelShirk 

I’ve created an example of the inquiry screen with some crude DACs. Seems that it matches your description:

Here’s the code behind the example:

using PX.Data;
using PX.Data.BQL;

namespace Sprinterra.CommunityRnd.InquiryTotals
{
public class MSDetail : IBqlTable
{
#region ID
[PXDBIdentity(IsKey = true)]
public int? ID { get; set; }
public abstract class iD : BqlInt.Field<iD> { }
#endregion

#region Name
[PXDBString(30)]
[PXUIField(DisplayName = "Name")]
public string Name { get; set; }
public abstract class name : BqlString.Field<name> { }
#endregion

#region Commission
[PXDBDecimal]
[PXUIField(DisplayName = "Commission")]
public decimal? Commission { get; set; }
public abstract class commission : BqlDecimal.Field<commission> { }
#endregion

#region EvenOrOdd
[PXDBString]
[PXUIField(DisplayName = "Even or Odd")]
public string EvenOrOdd { get; set; }
public abstract class evenOrOdd : BqlString.Field<evenOrOdd> { }
#endregion
}
}
using PX.Data;
using PX.Data.BQL;
using System;

namespace Sprinterra.CommunityRnd.InquiryTotals
{
public class MSFilter : IBqlTable
{
#region TotalCommission
[PXDecimal]
[PXDefault(TypeCode.Decimal, "0.0")]
[PXUIField(DisplayName = "Total Commission", Enabled = false)]
public decimal? TotalCommission { get; set; }
public abstract class totalCommission : BqlDecimal.Field<totalCommission> { }
#endregion
}
}
using PX.Data;
using System.Collections;

namespace Sprinterra.CommunityRnd.InquiryTotals
{
public class MSInquiry : PXGraph<MSInquiry, MSDetail>
{
public PXFilter<MSFilter> Filter;
protected virtual IEnumerable filter()
{
var filter = Filter.Current;
if (filter == null)
{
yield return filter;
yield break;
}

filter.TotalCommission = 0;
var startRow = 0;
var totalRows = 0;

foreach (MSDetail row in Records.View.Select(new[] { filter }, null, null, null, null, Records.View.GetExternalFilters(), ref startRow, 0, ref totalRows))
{
filter.TotalCommission += row.Commission;
}

yield return filter;
}


public PXSelect<MSDetail> Records;
}
}
<%@ Page Language="C#" MasterPageFile="~/MasterPages/FormDetail.master" AutoEventWireup="true"
ValidateRequest="false" CodeFile="MS123456.aspx.cs" Inherits="Page_MS123456" Title="Test Inquiry" %>

<%@ MasterType VirtualPath="~/MasterPages/FormDetail.master" %>
<asp:Content ID="cont1" ContentPlaceHolderID="phDS" runat="Server">
<px:PXDataSource ID="ds" Width="100%" runat="server" Visible="True" PrimaryView="Filter" TypeName="Sprinterra.CommunityRnd.InquiryTotals.MSInquiry" >
<CallbackCommands>
</CallbackCommands>
</px:PXDataSource>
</asp:Content>
<asp:Content ID="cont2" ContentPlaceHolderID="phF" runat="Server">
<px:PXFormView ID="form" runat="server" DataSourceID="ds" Style="z-index: 100" Width="100%" DataMember="Filter">
<Template>
<px:PXNumberEdit ID="edTotalCommission" runat="server" DataField="TotalCommission" />
</Template>
<AutoSize Container="Window" Enabled="True"/>
</px:PXFormView>
</asp:Content>
<asp:Content ID="cont3" ContentPlaceHolderID="phG" runat="Server">
<px:PXGrid ID="grid" runat="server" DataSourceID="ds" Width="100%" Height="150px" SkinID="PrimaryInquire" AdjustPageSize="Auto" >
<CallbackCommands>
<Refresh RepaintControlsIDs="form" />
</CallbackCommands>
<Levels>
<px:PXGridLevel DataMember="Records">
<Columns>
<px:PXGridColumn DataField="Name" TextAlign="Left" Width="100" />
<px:PXGridColumn DataField="Commission" TextAlign="Left" Width="100" />
<px:PXGridColumn DataField="EvenOrOdd" TextAlign="Left" Width="100" />
</Columns>
</px:PXGridLevel>
</Levels>
<AutoSize Container="Window" Enabled="True" MinHeight="400" />
<AutoCallBack Command="Refresh" Target="gridOption" ActiveBehavior="true">
<Behavior RepaintControlsIDs="form" />
</AutoCallBack>
</px:PXGrid>
</asp:Content>

As it was made quickly just for proof-of-concept purposes, it lacks more controls expected for inquiry screen, but should be enough to get things going.  

Let me know if I’m missing something and this is not the result you’re expecting.

Userlevel 3
Badge

@andriitkachenko Thank you for the detailed example! I see a few things in here that are different from my attempts. I will try this today and let you know the results!

Userlevel 3
Badge

@andriitkachenko Thanks again for the example!

Your C# code was almost identical to one of the things I had tried, but the auto-callback in your ASPX was a bit different from the example I was following from the article I linked in my question. I’m guessing that was the difference, but either way, it is working now!

This will work for our use case, but one thing for others to take note of is that after removing a quick filter, the calculated value doesn’t update until the user performs some other action on the screen (even clicking on the grid). 
There’s probably a way also trigger a refresh after the filter is removed, but I don’t have time to dig into it now.

Badge +12

...after removing a quick filter, the calculated value doesn’t update until the user performs some other action on the screen...

Are you sure it isn’t the action of leaving the field that causes the refresh? That would be the standard Acumatica trigger for FieldUpdat[ing][ed].

 

Edit: Sorry, I think you’re talking about the column filters. I thought you meant header filters.

Userlevel 3
Badge

...after removing a quick filter, the calculated value doesn’t update until the user performs some other action on the screen...

Are you sure it isn’t the action of leaving the field that causes the refresh? 

 

Yes, I went back a double checked. I have to click elsewhere on the grid before the total recalculates.

Userlevel 4
Badge

Hi Mike!

One thing that I have found to be useful is the StatusField property of the grid and tying it to an unbound/calculated field in the DAC. This allows you some formatting/flexibility in what/how you display the data but isn’t exactly what you asked for. It also seems to respect the “filtering” options that may be applied.

Mike

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