Basics of Dynamics GP Tables and How to Use them in Dynamics GP SmartLists
Posted by Jen Bieker on March 18, 2015
Ever hear someone refer to SOP10100, SOP10200, or some other code like that when talking about Dynamics GP? Well, it’s not a secret tech language, it’s the names of the tables, behind the scenes in Dynamics GP, and they all work together giving you information like transactions, batches, reports, etc. Think of the tables as storage areas for every piece of data you enter into Dynamics GP. And then there is a lot of code back there, which makes the tables interact correctly with each other.
Why Should I Know This?
It’s not necessary to understand and know all the tables and how they all interact, unless you want to become a Dynamics GP developer. But for us end users, sometimes understanding where something is stored can help us to find something easier, be better able to speak with a support person about an issue you are having, or even being able to create new SmartLists and reports in GP that pull in exactly the data you want.
I don’t know all the tables and definitions either; however, the one thing I’ve learned over the years is where to look those up. I usually go to Victoria Yudin’s blog. She has simple and easy to find information.
For example, SOP Tables store all the data we enter in the Sales Order Processing screens. So if I needed to know where the information is stored for a sales transaction I just entered, I’d know to look in the table SOP10100 – Unposted/Work Transaction Header information and SOP10200 – for the Unposted/Work Transaction line items. Even though the header and line items are in different tables in the database, they are related and Dynamics GP know how they fit together.
When Would I Ever Use Table Names in Dynamics GP?
Do you ever use SmartLists in Dynamics GP? SmartLists provide an easy way to create ad-hoc reports and queries into the Dynamics GP data. These lists pull data from the tables in Dynamics GP, through an easier user interface; in other words, without having to know table names and more advanced reporting tools like SQL Reporting Services, Crystal Reports, or other popular tools.
There are many lists readily available that you can use and in Dynamics GP they give you the ability to add to these lists or change them to get just the right set of data you need, but there are limits on SmartLists.
It’s helpful to understand more advanced topics, like table names and definitions, when you run into limitations with SmartLists. By understanding the basics of the database behind Dynamics GP, you can pull in the data you really want in that list or report. One way to achieve this is to create or copy a SQL View, which is SQL code that pulls the data from the tables. Then you can create SmartLists from this new set of data.
You don’t need to write SQL to do this either. There are tons of people who have written SQL views that you can just use for your own advanced SmartLists. Again, take a look at Victoria Yudin’s blog. She has written quite a few SQL views for different purposes that you can copy and use for yourself.
For example, you might want to try a few of these reports:
• Sales Quantities by Customer by Line Item by Year – Total sales quantities (invoices minus returns) by customer by item by year, with an overall total column.
• Apply Information – shows invoices and what checks or credits were applied to them.
• Customer Yearly Totals – shows calendar year totals for all receivables transactions and the results are one row per customer with the years in columns.
• Total Payments to 1099 Vendors for the Prior Year – shows you total payments for each vendor for the purposes of confirming your 1099 report from Dynamics GP is correct.
Resources on Using SmartLists and SQL Views
If you haven’t worked with SmartLists, here is a simple tutorial on using SmartLists in Dynamics GP. I’d suggest getting familiar with SmartLists and working with them for a while, then move on to more advanced topics such as using SQL views.
Crestwood Clip – a short video on the Basics of Dynamics GP SmartLists
Watch a recorded webinar from Crestwood on SmartLists and using SQL Views
And remember, if it’s your first time working with SmartLists or SQL Views, they can be a little confusing; so we are here to help. Our friendly support staff can walk you through it. Just email them at Support@Crestwood.com