What is the Wyatt.ERP Technique for Generic Inquiries in Acumatica?
I call it the Wyatt.ERP Technique because Wyatt.ERP (click here) is the person on AUGForums.com that I learned this technique from. His username is a clever play on the famous Wyatt Earp lawman in the Old American West.
WANTED DEAD OR ALIVE: Tricky problems that you don’t think can be solved with Generic Inquiries.
We can use the Wyatt.ERP Technique whenever we need to combine unrelated datasets together.
In this post, I’m going to show you the end result of something that is possible with this technique.
If you’d like to learn how to build this example, note that there is a lesson my Building Generic Inquiries & Pivot Tables course (click here) which walks you through the steps.
Using the Wyatt.ERP Technique, we can combine Purchase Order Lines and Sales Order Lines together in one Generic Inquiry.
This allows us to get a total quantity for each Item across both Purchase Order Lines and Sales Order Lines to see if there is a net total supply (which would be positive) or a net total demand (which would be negative).
As you can see, this Generic Inquiry shows Purchase Order Lines on the first page of results, with positive numbers in the Qty column:
We can see that we have 4 columns of information: Order # shows us the Purchase Order number, Whse shows us the Warehouse, Item shows us the Item, and Qty shows us the quantity on order.
Normally, if you wanted to combine Purchase Order Lines together with Sales Order Lines, you would probably export this Purchase Order Lines data to Excel, then go to another Generic Inquiry that has these same 4 columns, but for Sales Order Lines, then export that Sales Order Lines Generic Inquiry to Excel. Then you could copy and paste the data in Excel to put it together into one table.
But, wouldn’t it be cool if we could do all of that work in one Generic Inquiry?
The Generic Inquiry in the previous screenshot is doing just that. Let’s go to the last page to see what I mean:
Isn’t this cool?
On the last page, we see Sales Order Lines in the same Generic Inquiry!
Because we have all of this in one Generic Inquiry, we can build a Pivot Table that gives us a net total quantity, for each Item, across both Purchase Order Lines and Sales Order Lines.
Let’s click the PIVOT tab to see that:
Isn’t this also pretty cool? We can see that there is a net total demand for AACOMPUT01 of 2,177 because the Total quantity is negative and there is a net total supply for AALEGO500 of 2,280 because the Total quantity is positive.
We can click on the Total column to sort and quickly see the items that are the most negative. These are the items that are likely causing the biggest challenges in our supply chain.
We can also click on the + icon to expand an Item and see how it’s doing by Warehouse:
We can see in the previous screenshot that the RETAIL warehouse has a net total supply (positive), but the WHOLESALE warehouse is driving the overall negative net total demand.
Pretty cool right?
This is just one example of how you can apply the Wyatt.ERP Technique to a real world situation, combining Purchase Order Lines and Sales Order Lines together in one Generic Inquiry.
Seeing this example is cool, but what’s more powerful is fully understanding the core of the Wyatt.ERP Technique so you can put this technique on your toolbelt and use it in real-world situations.
For simple tips and tricks in the Building Generic Inquiries & Pivot Tables course (click here), I like showing the end result first, then walking through the tabs of the already-built Generic Inquiry so you can build it on your own.
But, because the Wyatt.ERP Technique is more involved, I take multiple video training lessons in the course to show how to build this from scratch and look at the results throughout the process, after layering in each piece step by step, because I think this is the best way to fully understand the technique.
Now, originally, the Wyatt.ERP Technique involved having to create an Attribute and add possible Combo Box values to that Attribute. That was a cumbersome step and it also made it challenging to move the finished Generic Inquiry to another Acumatica environment because you had to re-create the Attribute in the new environment.
That’s where Laura Jaffe (click here), another awesome person on AUGForums.com, came up with a way to do the Wyatt.ERP Technique without needing to create an Attribute. To honor Laura’s significant enhancement, I thought about renaming this to the “Wyatt.ERP Laura Jaffe Technique”, but that name seemed too long.
I do though give a “nod” to Laura in the video training lesson when naming one of the tables in the Generic Inquiry. It’s the table that Laura contributed to the Wyatt.ERP Technique.
Want to learn how to build this?
You’ll learn how to build the example above.
More importantly, you’ll learn the Wyatt.ERP Technique which will give you the skills to apply this technique to many other real-world situations using Generic Inquiries in Acumatica.
Here is an excerpt from this lesson in the Course: