Solved

Adding a group by clause to PXProcessingJoin

  • 29 January 2024
  • 2 replies
  • 23 views

Userlevel 4
Badge

Hi,
 

I am creating a new processing screen and have created the DataView below to grab the data I need for the processing grid.
 

public PXProcessingJoin<ARInvoice,
InnerJoin<Customer, On<ARInvoice.customerID, Equal<Customer.bAccountID>>>,
Where<ARInvoice.customerID, IsNotNull>,
OrderBy<Asc<ARInvoice.customerID>>>
Invoices;


At the moment it is returning multiple rows for the same customer, as it is returning each invoice, but I would like the query to group on ARInvoice.CustomerID so that I can see which customers have overdue invoices.

The reason for this is I want the processing screen to send email reminders for overdue invoices, and I only want one email sent per customer, rather than an email per invoice.

Is there a way I can include this clause into the above query?

 

Let me know if you need any other information.

 

Kind regards,

Andrew

icon

Best answer by Naveen Boga 29 January 2024, 05:25

View original

2 replies

Userlevel 7
Badge +17

Hi @AndrewA  You may not write the GROUP BY clause for the PXProcess View. You can write a view delegate for this View, in this view delegate you can write PXSelectJoinGroupBY clause and accordingly return the unique values in the grid. 

 

Below is the sample example for View Delegate, you can also write the same for PXProcessingJoin view as well.

 

https://asiablog.acumatica.com/2016/06/using-pxview-in-dataview-delegate.html

Userlevel 4
Badge

Hi Naveen,

Thanks for this, I will give it a try and let you know how I go.

Kind regards,

Andrew

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