In my recent post on the Change Entry Log, I referenced several reports I’d written.
Thinking about it later, it occurred to me that everyone has different formatting option preferences.
So, in this post, I’m going to show you how you can create a SQL Query to get the information you need to build your report.
The key challenge with the Change Entry Log is that it references tables, fields and field options by NUMBER, not by name.
In an earlier post, I showed you how to get this data into your SQL Reporting Database. (You can find the code in my GitHub.)
So, the query I’m going to walk through now takes the Change Log and uses those tables to provide usable data.
In this example, I’ve created a query to track vendor changes (scroll to view all of it):
When I run the above query, here’s the data I get:
Let’s walk through it so you can see how I converted numbers into Names.
The query itself may be found in my GitHub.
The query name is 200 – Query NAV Change Log for Vendor.sql.
As I noted earlier, this query uses multicompany views that I build for almost all my customers.
Again, you can find the code to do this on GitHub.