I was honored to be a guest on the “Bring your Reporting Questions and Stump the Panel” episode of the awesome AUGForums.com Live show. If you missed the live show, you can watch it and see the related links here. On this broadcast Tim Rodman, Ryan Brown, Arline Welty and myself discussed a variety of topics, including how to create a Generic Inquiry in Acumatica to search for missing data. The specific 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)
Below is a sample production order that has three operations.
Drilling down into the Production Transactions for this order we can see an example of the exception situation we are trying to identify. There’s a labor transaction for OperationID 0020, but nothing has been reported on 0010.
For those of us 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:
Notice the left join from AMMTran to op10. If there are one or more matching transactions, the query will return the matching records. Importantly, because this is a left join, when there is no match found in op10 then the parent record will still be returned with the result field (op10.ProdOrdID) having a value of NULL. We can look for this condition to identify the exceptions.
Below is a screenshot showing the tables in the equivalent Acumatica GI. Note that we have the AMMTran table twice: the first instance is so that we can find the existing labor transaction, and the second instance to look for the missing transaction. We also have to link in the AMProdOper class so we can filter on the OperationCD field rather than the numeric OperationID field.
On the conditions tab we specify the DocType and OperationCD to identify the production orders that we are interested in reviewing.
On the left join relation between op0010, notice that on the 4th line that we’re not limited to only fields from the parent or child table. Because it’s a left join, records that don’t have any matches will also be included in the results.
Next we will group on the prodOrdID field in case there are multiple labor transactions so that we’ll only see each production order once.
Once we have grouped on the ProdOrdID, we can use the aggregate function MAX to display the maximum OperationCD that meets our criteria in the op0010 table
When we run this query, it will return each production order that has a labor transaction. If at least one 0010 transaction is found then the maximum value will be 0010, otherwise the field will be empty.
Depending the type of missing data you are looking for, it may be possible to add criteria directly in the Conditions to look for the empty rows. In this case, because of the query structure, we opted to use a shared filter which emulates the HAVING clause in the original SQL query.
A nice enhancement is to use a side panel to display the details on the list. That way the user can easily verify the results of the query.
You can adapt this “left join” technique to look for all sorts of missing data. On the podcast, Tim suggested creating an Exception dashboard (I think he called it “things that should never happen”). I love that idea! The end product would look something like this:
Another option for users who are on the go would be to use Acumatica Business Events to send email or text alerts.
Happy report writing!
For more Acumatica Tips and Tricks visit our blog often.
