So it’s been a week since I temporarily abandoned the Acumatica Data Access Classes (click here to see what I mean). This is just a temporary thing and I’m sure that there is a better way to learn this, but I decided to take the blunt approach by hunting around the Acumatica database to see if I can get comfortable with how things are related. That’s right, no Acumatica University (click here), no help pages, no glossy manual, just me out in the cold alone with the Acumatica database.
Starting With Baby Steps
In order to warm myself back up again, I decided to start by trying to take some baby steps. My first goal was to come up with a list of vendors that equals the list in the Vendor Summary (AP401000) screen. The first thing I did was to uncheck the following boxes to ensure that I only get one row per vendor in the Vendor Summary (AP401000) screen:
Then I compared my list of vendors to the Vendor Summary (AP401000) screen list of vendors and realized that my list was quite a bit longer. So, why was mine longer? Well, it looks like the Vendor Summary (AP401000) screen only shows vendors with some posted history. Once I changed my query to only include vendors with posted history, things looked to be in sync.
Once I was able to write a query, I needed a way to display my query in a report. So, I dumped my query into Microsoft Excel using Power Query to give myself a nice little report. It’s pretty boring work to be honest, but it is satisfying to know that I can begin to reproduce a regular Acumatica report using a method that I am already familiar with.
Well, I think I’ll stop for now and call it a (small) success. I was able to get a list of vendors that is the same as the list in the Vendor Summary (AP401000) screen. The end result looks like this:
As a comparison, here is the same sample list of vendors displayed using the Export to Excel function on the Vendor Summary (AP401000) screen.
As you can see, both versions are able to be viewed in Microsoft Excel, but my version uses standard Excel tables which allow you to easily sort and filter using the upside-down triangles.