#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