Dynamics GP Database Structure – Finding Fields

There is a lot of data stored in Dynamics GP, but when users try to write reports they don’t know where the data is. Understanding the underlying database in Dynamics GP will help users build better reports. During this recorded webinar, we share various techniques to find table and field information in Dynamics GP.

Topics discussed during this session include:

• Data Structures
• Field Names
• Windows
• Using SQL Management Studio
• Third Party Assistance

The intended audience for this video is existing Dynamics GP users.

Contact us if you have any questions about finding fields in Dynamics GP.

Transcript below:

Tess: Hello, everyone. And thank you for joining us today for our “Dynamics GP Coffee Break: Database Structure-Finding Fields.” My name is Tess, and I’m the facilitator today. If you have any questions during the presentation, please type them into the questions area in the control panel on the right-hand side of your screen and we’ll answer questions at the end. This session will be recorded and posted on our blog as well as shared with you later this week. Now I’d like to introduce our presenter today, Don McNulty, who is a solutions architect on our Dynamics GP team here at Encore. Don, I’ll pass it over to you.

Don: Thanks, Tess. So, today we’re gonna talk about finding fields. And one of the issues that we have with GP is we have all this data that we store inside great plans, and then we try and write reports and we have no idea where a lot of this stuff is stored. So, we have to kind of figure out what we’re going to do with it. So, what we’ve got is I gotta explain how the structures are done, something about the field names, a little bit about the windows of where you put the data in so that you can find what kind of thing you’re putting in where in the store. And then I’ll talk about using SQL Server Management Studio, as well as a little bit on SmartList Designer, or SmartList Builder so that you can look at some of the data in its raw format. And then there’s a couple of small third parties that are available to us that we can purchase that make this a lot easier.

So, structures. So, the types of tables that we have inside GP are all based on specific numbering system. The numbering system in GP tables, which is things like RN00100 and EN2000 and things like that are all traced back to the old novel system back in the 1990s when they named all these tables. They haven’t changed the name since then. So, the location of these tables, where are they? They’re all stored underneath SQL server. They’re all stored inside the company database. The company database has approximately somewhere between 700 and 1,000 tables, depending on how many third parties you have, and inside each of those tables is somewhere between 10 and, you know, 100 different fields. We also have views inside SQL server. Those views are things that are used for things like SmartList and for some of the other processes that you learn. These views are SQL views. They are basically queries inside SQL which link a bunch of tables together and say this is kind of the data that you need. So when you look at a payables transaction entry or payables transaction in SmartList, for instance, it’s looking at a view inside SQL in order to generate that data.

Now, physical names. So, physical versus GP table names. GP table names are things like PM_transaction work. The physical name is PM20000. So, when you’re writing reports using SQL Reporting Services, or even to a certain degree using Excel reports, you need to know the PM20000 table. How do you find that? How do we know which table is which? There are some resources built into GP, which I’ll show in a second as to how that works. Now, I did wanna mention a couple of things about this. The tables are, while they seem to be cryptic with PM or for purchasing manager or RM for receivables managements or GL for general ledger followed by five digits, if the first two digits, or if the first digit is a zero, it’s a master file, i.e., chart of account, vendors, customer. If the first digit is a one, it’s a work file. And in a work file, we have work open and history files inside GP.

Work files are files that we enter into and can change. Open Files are transactions that are posted, but not yet fully cleared, i.e., outstanding accounts payable, outstanding accounts receivable. The 3000s, the name that starts with a three is history. Once it’s been fully paid, say, for instance, in payable, only if you write a check for it, it automatically moves it to the 3000, the 30,000 series. The 4,000s are set up files. The 5,000s are what they call permanent temp file. Do not base any report based on a 5xxx table because it may or may not contain the data that you expect at the time that you run the report. Those tables are empty and refilled based on what people are doing within GP. So, you cannot rely on them for reporting.

Don: Okay. So, the next are the sixes, the sevens, and anything that’s in the eights or the nines. These are just miscellaneous fields that didn’t fit in any of the other ones before. Now, that’s the standard GP structure. Some of the newer third parties and some of the newer tables that Microsoft has added in the last 10 years have got more intelligent names. So, things that…well, actually, you can look at the field and go, “Oh, it says work in progress” or it says, “Inventory something.” And those tables are also possible. They’re not that many of them. Most of them fall into that 001000.

Field names. Inside GP, we have a field name that you see on the screen. There’s also a physical name. Due to the limitations back when they first wrote GP, the limitation was eight characters. So, as a result, the field names are very cryptic in a lot of cases. You’ll find things like, instead of debit amount spelled out, you get DBTAMT or AMMT or something like that. So, you get pretty good at looking at those and say, “Well, that’s what they are.” There are some references that we’re gonna look at. And then the last piece is the windows. So, what I’m going to do now… So, then we’ve got some third-party assistance. And I’ll come back to these, but these are the two main ones that we’ve got. All right. So, let me flip over to my screen and we’ll start to look at GP so that you can investigate how you can find these different pieces of information like that.

So, at this point, we’re running great plains. And I’m gonna just select a table of, you know, just any table that I really care about. So, I’m gonna pick the Payables Work File or the Payables OPEN File. That’s where after I have put my batch of transactions and I have posted it. It’s sitting in the inquiry. So, when I was…to give you an idea. When you look inside here, I go to Transactions by Vendor, and I bring up ACE Travel. I’m looking for these transactions. These are the transactions that I’m looking for. So, I’m looking for the open transactions, unpaid account payable. They’ve been posted, they’ve been approved, they’ve been posted, but they have not yet been fully paid. And as a result, they are sitting inside a table, and we know that these are sitting inside a table. So, we go, “Well, where are they?” So, you can get them from tools here, underneath tools there, or you can go up to the top here and go to tools here. Underneath there, most of your systems, if you don’t have any third parties added stop right here. That’s the last one on the list. Everything that is below this line is a third-party. Table Finder comes from eOne. The stuff that you see below that all come from Winthrop.

So, underneath there, we now have three things inside here, Tables, Fields, and Windows. So, when I look at tables, and this is the GP product. When I click on the ellipses right here to look this up, I say, “What product is it?” These are all the different products I have loaded. Okay. As you can see, quite a few. I’m gonna stick with GP. And this happens to be a purchasing account. So, there we are here. So, down here, it’s doing it by displaying them. So, these are the names that you see in there. And if I scroll down on this, I’ll be able to find the payables transaction work. Okay. So, you know, and you kind of go, “Where is it?” So, I can sort this by group technical, I can sort it by physical or by technical. So, I’m gonna click on this. So, as I said, anything that’s the 00 in here that you see in the middle there, you can see they’re all vendor’s master, vendor master files, and things like that.

Once I hit the 1,000s, there’s the transaction work file. This file is where I enter my data before I post it. Most of us don’t wanna run reports on unposted transactions. So, I’m not looking for those. So, if I scroll down there, you see these are the transaction file. And then once I get to the 2,000s, the 20,000s, I now have my PM transaction OPEN File, some applied ones as well as some other transactions. And then you’ll notice as I come down to here, I have a paid transaction history file. So, if I wanted to see what I paid in the past, fully paid transactions, I would use the PM30200. Whereas what I’m gonna be looking at today is the PM OPEN File. So, when I double-click this, it opens up a list of all the fields. And this is the list of all the fields that we have.

Now, these are the names that you’re used to looking at, Vendor ID, Voucher Number. You’ll see in the middle here you’ve got your eight-digit character and they’ve got voucher numbers VCHRNMBR. So, you get, as I said, they do make some sense, but you do have to kind of look at them and say, “Okay. What is this? This is the vendor ID, document type, document date, document number. Not a problem.” Some of them get a little at this one account, discount dollar amount. Like, “I’m sorry. I don’t remember that all the time. So, if I’m looking at a table, I need to look this up to make sure that I’ve got this.” If I go into certain types and then I see things like this, you have to look at your different storage types as to what they are. If you see a document type, your document type would be invoice, credit note, payment, debit note. You see that it’s actually stored as an integer. And the reason it’s stored as an integer is that it saves space. If I click on Field Info here, it may tell us the static values that they are, like what version is what. So, a number of different places that you can find this information.

One of the best sources that we have is a lady by the name of Victoria Yudin. And she has a website. And when you go out there, she has a list of all of these tables. And this insight here, there’s my payables tables. And she does a lot of this. So, you can see on the screen, there’s an open/posted transactions, and then there’s the doctypes. Number one is an invoice, two is a finance charge. I use this extensively. And thankfully, she maintains it and keeps it up to date. So, we don’t have to worry about it all the time. But these are all of the different possible combinations. So, these are types of things that you may need to know inside there. So, that’s kind of inside GP. These table descriptions is how you find the different pieces of information.

Now, let’s assume that I’m looking at a window and the window that I wanna look at is this one here, Payables Transaction Inquiry-Vendor. Okay? So, when I look at that and I wanna say what am I looking at, I can go into my Tools and into my Resource Descriptions, and I can go to Window. I can then click up on my Financial here and now I can go down here. Now, let me just bring that up so you can see it. And if I scroll down on here, I’m looking for Payables Transaction Inquiry-Vendor. There it is there. So, at the bottom here, there’s a list of all the fields that are on this, as well as a list of all the tables that are connected. So, there’s my payment work, there’s my transaction open. As you can see, I can say, “Oh, well, I wonder which table this is.” So, if I’m looking at this, now I’m looking at the work file, which I have nothing in. If I look at this, I’m now looking at the open file. And the paid transaction history would be as if I was looking at this list. If I’m looking at them all, I’m looking at three separate tables at the same time. And GP is putting it all together on this one screen. So, we’ve got this piece of information. So, as a result, we’ve got this payables open, but inside here, I now know the table. So, I can go back to the table to find the field. It is not easy to find some of these things a lot of the times, but this is the standard way of doing it inside there.

The other option is that we can look at, go up to here. We can go to Tools, Resource Prescription, and you also have Fields. So, inside here, I can now grab my purchasing fields and I can grab at the bottom. I’ll just grab Vendor ID. Vendor ID. When I click the Vendor ID, this is a list of every single table that the vendor ID is attached to. You will find down inside here that the PM Transaction WORK, Transaction OPEN File is right here. So, again, if I know the field name, I can look at all the tables that I can possibly look at inside GP in order to find this particular data.

So, you go, “Well, that helps a little. It doesn’t help a lot, but it helps a little.” So, how can I, when I’m looking for stuff like this, once I’ve got a pretty good idea of what I’m looking for, like I know I want the Transaction OPEN File, how can I find out what field is that I’ve actually keyed it into, what’s it called, and what populates it, and what makes it change? So, there’s two ways of doing that. The first way is using SQL server. So, inside here, we know that it’s the PM2000 file. So, I can run the Select Query and I can just go Execute and it gives me a list. This is a list of all the transactions that are inside there.

So, those ACE Travel ones that I just was looking at, there they are right there at the top of the list. And I can see the document type is number one, which I now know is an invoice because we look that one up. We’ve got a doc date, doc number, etc, and as we move across, these are all the transactional information. So, this is the data that I’m looking for. I go, “Well, now I know, and I can see the field name at the top and I can see some data underneath it.”

Now, most of us may or may not have access to SQL Reporting Services. So, how do I get this data in its raw format if I do not have SQL server? So, what you can do is that you can go into your SmartList. And I’ve got Smartlist Designer running today. So, inside here, what I did was, is I created a smart list based upon…so you go into your Tables inside here and we go into Purchasing and this will take a second while it opens up. And we know that it’s called the PM Transaction OPEN File because we’ve seen that before. So, that’s what we’re looking for. We’ve got a document amount with 30 transactions. I just picked a few fields. So, there it is. So, when I scroll down here and I scroll to my PM Transaction OPEN, there’s my transaction work, I can open this up and say, “Okay. These are all the fields that are on that piece.” And I can say, “Oh, I want the new long description. I want the PO number, and I want…” What else do I want inside here? The batch number. So, when I add that and I execute this query, you can see at the bottom, you can see the data. So, that’s a raw data. If I save this, which I have done to AP Transaction raw, I go OK. When I go into AP Transactions, it automatically picks this up.

Now, you’ll notice there’s 214 transactions there. And you’ll notice that down at the bottom right-hand corner down at the very far right-hand side of your SQL query, there’s 214 transactions there. So, I’m looking at exactly the same file. I’m just looking at it inside GP. I can say, ‘Oh, but what I have is I have the actual GP document name, not the actual physical name.” So, the voucher number, you have to say, “Oh, that’s gotta be the voucher number.” So, once I’ve got these, I now can…now, from here, I can now write my reports based on this. So, this is just a standard AP, and I can then add different things to it. So, the question always becomes, how do I know where it is and stuff like that? So, that’s kind of the first steps to doing this. So, that’s how we figure out what tables it is. So, that’s how I can look at any one of these given tables.

So, now when I go back to my GP, you’ll notice that underneath my tools, I’ve added a number of pieces at the bottom. So, what I’ve added is I added Table Finder comes with SmartList Builder. If you have SmartList Builder, you will have Table Finder on your list. When I click on Table Finder, I can come through here. I can grab GP, I can grab Purchasing, my PM Open Files, and it lists here all the open files. So, these are the ones, Work Open, some history ones, distributions. When I click on this, it gives me a list on this side of all the field names with both the field name that you see, which is what you saw on the SmartList and the SQL name, which is the doc number and the voucher number.

So, this is a very good one here. There is the SQL select script, which is exactly the same script that I ran here. So, we’ve got that. Oops, sorry. Wrong screen. So, there it is there. If I was to select the history file and I say, “What about this?” If I go into my history file, Paid Transaction History, it gives me a list of there’s the new table, and here’s all of the fields that are there as well. So, I can get those. So, I can see where the field is that I’m looking for. These keys that you see there means that the document, this plus this must be unique. Okay? In other words, the voucher number and the document type must be… So, what happens is these are key fields, hence the word, hence the keys in front so you know that those are index/key fields and must be unique in structure. Okay? Combination. So, that’s how this one works.

You can go by Window, I can go by Field. So, if I can go by field names. So, if I take my field name and I’m looking for voucher number. So, if I go Field Name, then I go to GP, then I go Voucher. Come on. It will search through all the tables and it should find me the voucher name. Okay? So, that’s because it’s looking for the DCH. Oh, it’s probably gonna… Yeah. It’s looking. You actually have to type in the full thing. So, I have to type in voucher number. But the idea behind it is is that it will then list what you require and then the field value.

Most of the time I use the windows to get the Field. So, there it is there. So, that’s the Table Finder. Now, that’s assuming that you have SmartList Builder. If you already have SmartList Builder, you already have that. It’s already loaded. You can use it at any time. And the reason that eOne wrote that was that people asked them a whole bunch of questions about, “When I’m writing SmartList, how do I know what table or what field to use?” So, they made it easier for you to find.

The other tool that we have for this is from Winthrop Consulting, and it’s called GP Power Tools. And it has Resource Information and Resource Finder. So, if I go to Resource Information inside here, it brings up a screen that looks like this. So, inside here, I can then…I can go and look up the different fields and things like that, or do whatever I need to in there. But the nice thing about this is, is that if I go to Transactions by Vendor like I was here and I go to my Tools here and I go to my Resource Information, it automatically fills in the inquiry screen. It tells you the window information and it tells you what fields you are on.

So, as I go through each of these, so if I scroll onto something like this or whatever it happens to be, I can now go take a look at the individual transactions. It does a very good job of finding each of these things inside here. So, the resource. And then the Resource Finder allows you to look for a field. So, inside here, where’s the key? PM Transaction Inquiry. So, on that field, it’s bringing up…these are all of the multicurrency transactions, there’s my PM Transaction Open, there’s a list of all the tables and fields that there are.

Again, different ways to get to the same information and there’s the long description, etc. And so from there, I can now see what the field is sort by and things like that. So, that was the field. I was on the field “Sort By.” That’s where my cursor was. If I click on this and go to the same place, you’ll see that it says “Origin.” So, it’s got a slightly different look because we’re in scrolling window, which is different than the header. So, these are the types of things inside there. If I hit the Preview Data, it then produces a status and there is the document itself. As you can see from there, that’s what the data looks like. So, it allows you to preview the data. These are all part of the GP Power Tools. GP Power Tools allows you to do that kind of thing. So, I know that that’s very quick and I think I’m just about out of time. How am I doing, Tess?

Tess: Yes. A few more minutes and then we’ll open it up for questions.

Don: So, I think what we’ll do is we’ll… I’ll flip back to my final point and go for questions at this point. And so, I can answer any specific questions people may have.

Tess: Awesome. Thanks, Don. No questions have come in yet, but please feel free to enter your questions into the side panel on the right-hand side.

Don: Yeah. So, my concern is the issue that people have is just how do we find this information and how do I get to it as fast as I can? So, I’ll just talk briefly about the Winthrop tools and the eOne software. So, Winthrop itself runs…for the single module is 500 to 1,400. They have three different modules. I have all three loaded here, depending on what you are trying to accomplish. Winthrop also looks after your security. So, if you’re struggling with security, it allows you to figure out very quickly why somebody can’t get access to a specific window.

So, on a security standpoint, the Power Tools is very, very good. It also allows you, if you’ve got multiple companies, it allows you to change the color of your company based on the company. So, you can have red for company one and blue for company two, so that when you’re in the company, you know what company you’re in without having to look down at the bottom and see fabric care.

So, there are some really nice things inside there. That is an annual fee of $500 to…$500 for the first module, $1,100, oh sorry. $950 for two, and then $1,400 for all three. Each one is $500. eOne SmartList Builder is $199 a month, and it’s a monthly subscription. Also gives you Popdock, which allows you to do things online as well as it gives you a license for SmartView. So, it’s quite good that way, but it does give you the Table Finder as well.

There’s a lot of people that have the Table Finder loaded because they have SmartList and they’ve had SmartList Builder for a number of years. So, that’s why I wanted to show it today. If you were looking just for this kind of information, I would definitely go for the less expensive $500 for it, which gives you the resource descriptions because that’s an annual fee rather than $2,400 a year. But if you’ve got other reasons for SmartList Builder or Popdock, then that would be worthwhile looking into. So, those are the two third-party assistance. GP itself does not give up its information that easily. We can use those pieces of information once we know what tables and things like that to link things together in both SmartLists, Excel reports, and SQL Reporting Services. Okay?

Tess: So, Don, there’s one question that’s come in.

Don: Okay.

Tess: They’re developing… I’m developing for automating batch posting in GP, how can I understand what is going on in the tables when the batch posted so that I can replicate it?

Don: So, you can replicate the batch posting? Personally, I wouldn’t replicate it. If you wanna automate it, there’s a piece of software called Smart Posts that will do it for you. It will automate your posting. That’s what I would do instead because it’s already written and it’s…you’re getting into the stored procedures and into…it calls different stored procedures. Each of those buttons that you see on the top of a GP screen, when you press them, it creates actions. Those actions can then run stored procedures. The stored procedure is what runs the batch posting.

Personally, if I was looking at that, at automating batch posting, I would definitely look to something like Smart Posts. It’s not that expensive and it allows you to say, “Okay. Any batch that is this type of batch, we post automatically, or we post it every 20 minutes or whatever it happens to be.” You can do it on a timeframe so you can post it at night if it takes too long. So, you can automate it that way. It’s much safer to do that because it’s already been tested and supported by Microsoft. So, you don’t have any problems with not calling all the right stored procedures. In other words, it may not just be a single stored procedure, it might be four or five of them that are actually being called in sequence. And you need to make sure it’s done. That kind of information is stored primarily in dexterity, which if you own the customization pack, you have a copy of, and you could then look at what it is doing and you can intercept those accordingly and put timers and things on them if necessary.

So, for instance, you know, one of the things that they’ve done inside this and monitor. So, for instance, what Power Tools does, for instance, is that when I’m at the bottom here, you have an exit. I have an exit after processes. So, I can start something up that pauses the process monitoring to fill up, run a big report, post a whole bunch of transactions. But then I need to exit when I’m done. So, I have to wait to be there. It allows you with the…Power Tools allows you to hit the Exit after process. So, you can post all your GL transactions, post all your AP, post all your transactions, and print off a report. And then once everything is been done, it’ll shut down GP for you so that your backup can run at midnight. And that might take a couple of hours, but you can start that at 5:00 and just hit Start and then just leave your machine and it’ll automatically log out of GP.

So, those types of things are all done in dexterity. That’s where these things like this screen here are written in dexterity so that they can…and it pulls up from the form name and things like that. So, when I go into there, it gives me a list of our cards. So, there are a number of different places inside there, but I would…if I’m looking at…if you’re trying to automate posting, I would definitely look at Smart Posts instead of trying to do it yourself.

Tess: Great. Thanks, Don. They said thank you as well in the chat.

Don: Okay.

Tess: So, we’ll…do we wanna wrap up, Don? Go back to the slides?

Don: Yes. So, let me just pop back to there. So, we have some upcoming GP webinars. Tess tells me that the 18th, the October, and the December ones are available to be registered for at this point. The other two will be opening up soon. Don’t know when. AP and AR close, we’ll be going through what reports you need to run, how you need to run it, what do you do, how do we make sure that our AP control accounts and our AR control accounts are balanced, what numbers are we looking at to make sure that they are balanced, and things like that.

December 13th, I’ll be talking about fixed assets, kind of what it does, how to run it, and then how to compare each to the general ledger as well. And then the last one is the GL close, which I did in January. So, they’re nice and fresh in everyone’s mind for year-end close for those of us with annual year ends, that will be, you know, how do I close a year-end? What do I need to make sure I do before I close the year, as well as do I need to close things such as AR and AP at the end of EQ, which I’ll also discuss during those closes.

And then the 14th, I’ll be doing how to do a bank rec, which is just kind of a pretty simple one. What does the bank rec module do, and how does it operate? Some people…we have a lot of people that have it that don’t know how it operates. So, hopefully, we can see you all there. And from there, I think that was it. Yes.

Tess: That’s great. Thanks, Don.

Don: Okay.

Tess: Yes. The 2023 webinars will open up probably in November, but I’ve added the events page link, our Encore events page link in the chat if you’d like to register for the October or December webinar. I’ve also added a link where you can set your email preferences to opt-in to our monthly and quarterly emails so that you can stay in the know about other upcoming events and our latest blog articles. So, thank you, Don, for the information today, and thank you everyone for joining us today and have a great rest of your day.

Don: Thanks all.