Skip to main content
Answer

Adding a group by clause to PXProcessingJoin

  • January 29, 2024
  • 2 replies
  • 78 views

Forum|alt.badge.img

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

Best answer by Naveen Boga

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

2 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • Answer
  • January 29, 2024

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


Forum|alt.badge.img
  • Author
  • Varsity I
  • February 1, 2024

Hi Naveen,

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

Kind regards,

Andrew