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).
#AcumaticaTnT Tips and Tricks - Finding missing data in Acumatica using a Generic Inquiry
On the recent AUGForums.com Live Broadcast Reporting Party (click here), @TimRodman, @Ryanxbyte, @arline-welty and I discussed how to create a Generic Inquiry that looks for missing data. If you missed the podcast live, you should definitely check it out!
On the podcast I promised to write more about a question that we faced with one of our customers.
The challenge was to find production orders meeting this criteria:
- At least one labor transaction has been entered on the production order
- There are no production transactions on the Part Inspection operation (0010)
For those with a SQL background, working within the Acumatica framework sometimes requires a change of mindset and a little creativity to get the job done. If we were going to write this SQL directly, it could look something like this:
WITH op10(ProdOrdID, OrderType) AS SELECT AMMTran.ProdOrdID, AMMTran.OrderType FROM AMMTran LEFT JOIN AMMTran op10 ON AMMTran.ProdOrdID = op10.ProdOrdID AND AMMTran.OrderType = op10.OrderType LEFT JOIN AMProdOper ON op10.ProdOrdID = AMProdOper.ProdOrdID AND op10.OrderType = AMProdOper.OrderType AND op10.OperationID = AMProdOper.OperationID AND AMProdOper.OperationCD = '0010' WHERE AMMTran.DocType = 'L' GROUP BY AMMTran.ProdOrdID, AMMTran.OrderType HAVING MAX(AMProdOper.OperationCD) IS NULL
In Acumatica, you can use a LEFT JOIN to emulate this same behavior. The join relations in Acumatica allow you to use formulas and reference other fields that may not necessarily be part of the relation. Once you have the GI, it's really easy to create the Exception dashboard that @TimRodman suggested!
I wrote up the details here: https://www.linkedin.com/pulse/acumaticatnt-how-find-things-should-never-happen-laura-jaffe