In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Questions Report Sales Orders with duplicate "Customer Order NBR."
I need to write a report (or GI) that allows me to report if there are more than 1 sales order in Acumatica with the same “Customer Order Nbr.”
I have done similar things a million times in other reporting tools, but I do not know of a method to accomplish this with Acumatica’s report writer.
So I need to run through the SO’s and report Acumatica sales orders and their associated “Customer Order Nbr”, but only if multiple sales orders have the same “Customer Order Nbr.”.
Anyone have any ideas?
Not a report, but you could do a version of this by:
1. Grouping results by "Customer Order Nbr."
2. Set the "Order Nbr." to aggregate "Count"
3. Create a Side Panel for the SO "PL" screen using the Customer Order Nbr. as a filter
To achieve something like this:
Hope this helps. Best of luck!
Thank you so much for taking the time to respond and for the detailed explanation with screen shots!
This will certainly come in handy in the future.
However, for this requirement, we will have hundreds of Sales Orders and cannot manually run through each looking at a side panel to see if there are duplicates.
I really need the ability to just show/report if there is more than one Acumatica Sales Orders with the same "Customer Order Nbr".
Again, I really appreciate your advice and time. I just don't think this will give them what they need.
Thank again for taking the time to reply!
I really appreciate you!
I need to identify either the "Customer Order Nbr" that is used multiple times so they can search on the Acumatica Sales Orders based on that and delete the duplicate orders.
Or the list of Acumatica Sales Orders with duplicate "Customer Order Nbr" AND report the associated "Customer Order Nbr"
The goal here is to easily identify Acumatica Sales Orders with the same "Customer Order Nbr" so the duplicate orders can be deleted.
@ltussing Oh, I understand. If it were me, I would use the GI that I described before and then create a Shared Filter where the count of the "OrderNbr" field is greater than 1. This will only show you SOs that share a Customer Order Nbr. I'd delete the duplicates using the Side Panel. For monitoring, I'd probably drop a KPI Card on a dashboard that shows the count of results in that GI.
^ Shared Filter ^
^ Dashboard Card ^
^ Dashboard Card Settings ^
If you wanted to get fancy with it, you could only show duplicates of "Open" SOs by replacing the SOOrder.OrderNbr field with the formula:
=IIf ([SOOrder.Status] = 'N', [SOOrder.OrderNbr] , null )
Best of luck!
Andrew, I haven't had time to try this yet but feel confident since you provided such excellent documentation.
I will reply to let you know when I have been successful.
I just didn't want to let this sit without responding since you were so great about responding so quickly to me.
I was able to get this working today and it is exactly what I needed!!!
THANK YOU FOR TAKING THE TIME TO ANSWER!!!!