Published at last! Developing Microsoft Dynamics GP Business Applications

 

If anyone has noticed my absence on the newsgroup, this is why. This book was published on Christmas Eve! – too late for Santa, but just in time for Valentine’s Day!

You can try out seven different development tools in this book. There’s a small project using each of the following:

• Dexterity
• VS Tools
• Modifier
• VBA
• SmartList Builder
• Extender
• Drill-down builder

If you’ve ever wanted to dabble, now’s your chance to work a little bit with each tool. There’s no other publication like it that includes a little about so many different tools; I hope you enjoy it.

I want to thank my technical reviewers, Mohammad R. Daoud, Jivtesh Singh, Vaidhyanathan Mohan, and Frank Hamelly, for their valuable time, ideas, and insights. This is a much better, more complete book because of them and their willingness to help and advise me. How could I miss with that kind of talent backing me up?

The book was only supposed to be 300 pages, but 300 pages just wasn’t enough. Even at 600 pages, there’s so much more I wanted to add. Please let me know if you have any suggestions on how I can make the book better next time.

I look forward to getting back with the Community!

Kind regards,

Leslie

Connection was refused by the server

Calling all Developers! Get ready for GP 2013 - Dexterity Training in St. Lake City! December 17th–21st, 2012

This is short notice, but if you can come, it'll be worth it! All of you VS Tools and VBA programmers, you really need to learn this stuff!

I’ll be doing a one-week class that will incorporate two weeks of training material. If you ever wanted to drink Dexterity through a fire hose, then this class is for you.

We’ll go non-stop for one week passing through very basic scripts, up to cross-dictionary work. No previous experience in Dexterity is required except going through the Quick Start manual that is included with the Dexterity documentation.

You’ll start writing integrating sanSript the first day of class!

This class was architected by T Hoecherl, a senior developer with Premier Computing, Inc. T is the man with all of the answers when it comes to logistics and cost. He can be reached at the following numbers:

Office: 801-487-8400, ext. 130
Cell: 801-259-7786

T tells me his classroom can hold eight people and he has a couple of spots left that he would like to fill. So, come to Salt Lake City on December 17th and get that training you need before GP 2013 hits the shelves!

The one-week Dexterity Basics class is still scheduled to go in Orlando in February of 2013, but here’s an earlier offering if February just seems too far away.

I hope to see you in Salt Lake City!

Kind regards,

Leslie Vail 972-814-8550

Dexterity Basics training February 11–15, 2013 in Orlando

Looking for Dexterity training? Come to Orlando!

I will be teaching the Dexterity Basics class at Integrated Business Group (IBG). The training will take place in Orlando, Florida from February 11th to February 15th, and will provide the basic foundations any Dexterity programmer must have.

This is an opportunity for all of you VSTools programmers to learn how to build your user interface in Dexterity instead of WinForms so that your application will run with the new GP 2013 Web Client. Your existing code will hook right up to the Dexterity events,  you may even like the Dexterity forms designer even better than VS Tools!

For more information, contact the training coordinator, Roxanna Alvarez at ralvarez@ibgnet.com or at her direct line +1 (407) 965-9299.

Hope to see you  in Orlando!

**ERROR: Batch level errors were encountered.

 

If you have ever received the above error on a posting journal or edit list, check to make sure the fiscal period for the batch you are trying to post is open. The posting journal/edit list should have another clue about what was causing the error at the top of the report.

The image below shows the error and the hint.

Until next post!

Leslie

When things go Wrong - I need to delete a printed SOP document

Dynamics GP presumes that any document that you print becomes a critical document in the business cycle and typically, does not let you delete it. But sometimes, you must delete it.

What to do?

The SOP module keeps track of the number of times you have printed a document. If you print it once, and printing to screen counts as printing, you cannot delete it. Therefore, you need to set the system back to believe it has never been printed in order to delete it.

Of course, this is not a supported solution, so use it at your own risk. The first step is to back up your System (DYNAMICS) and company databases. The name of your company database is displayed on the Company Setup window.

USE YourCompanyDatabase
GO

UPDATE SOP10100
SET TIMESPRT = 0
WHERE SOPNUMBE = 'your_SOP_Doc_Number'

When things go Wrong - Error registering table GL_Account_MSTR

Here's what happened.

 

You've just installed the workstation, you launch it and are greeted with this:

What to do?

You'll be delighted to know that this is an easy fix. The problem comes up when folks copy the Dynamics.set file to one worksation from another before they synchronize the existing Dynamics.dic to the systems account framework.

All you need to do is change the dex.ini file so that the following entry exists:

Synchronize=TRUE

Most likely, yours says FALSE

You need to make sure you only have one of these entries in your Dex.ini file. If you have more than one, the first one rules.

After you change the Dex.ini setting (in the Data folder of your application folder), you'll need to launch Dynamics Utilities in order to complete the synchronization process.

Once the synchronization process is complete, the error should go away, and Dynamics GP should launch.

Don't ya' love all of the 'should's' in that dialog?

Live the Dream!

Leslie

Thanks to David Musgrave's keen eye I changed this post to the correct setting. I had it backward originally. Unforgivable for the dex.ini queen.

When things go wrong - Batches cannot be posted

Here's the scene. You're trying to post a batch of GL entries and you get the following error:

"Batches cannot be posted while the year-end close is in progress"

But, of course, the year-end closing process is not happening. What to do?

You need to change the system's mind about that closing process by running the following scripts:

First, and always, back up your system and company databases, and then:

USE DYNAMICS
GO
DELETE SY00800
DELETE SY00801

USE TEMPDB
GO
DELETE DEX_SESSION
DELETE DEX_LOCK

UPDATE:
Here's a post from David Musgrave that allows you to clear up the batch error with everyone still logged into the system:

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/12/03/releasing-stuck-batches-and-transactions-without-exiting-all-users.aspx


Hope this clears it up for you.

Live the Dream!

Leslie

When things go wrong - Another user closing the year

You're trying to close the year and you get an error message:

"Sorry, another user is closing the year"

However, no other user is trying to close the year. What to do?

     Make sure everyone is logged out of Dyanamics GP

     Backup your company Database

     Run the scripts

     Use 'YourCompanyDatabase'
   Go
 
   Delete GL50100

When things go wrong - This batch is being posted by another user

You get this message even though nobody is logged in to the database so that it's impossible for the batch to be being posted by somebody else. What to do?

1.  Back up the DYNAMICS and company databases.
2. Change the batch status so that it no longer thinks it's being posted.

USE YourCompanyDatabaseName
GO

UPDATE SY00500 set BTCHSTTUS = 0 WHERE BACHNUMB = 'YourBatchNumber'

Depreciation - When things go Wrong

Sometimes we set up depreciation and the CPA firm decides it should have been done a different way. Of course, that information comes to us at least two years after we set it up. It's the nature of the beast; they're accountants, they can't help it.

The following scenarios will be covered over the course of four posts:

• Wrong 1st yr Convention
• Basis Reduction Required
• Wrong Depreciation Method - fully depreciated as of previous year end
• Wrong Depreciation Method - not fully depreciated as of previous year end

Stay tuned.

Until next post!

Leslie

Use PSTL without being 'sa'

https://connect.microsoft.com/dynamicssuggestions/feedback/details/762593/run-pstl-as-a-non-sa-user-create-a-pstl-user

Wouldn't it be nice to be able to perform PSTL activities without having to log in as 'sa'?

Vote for the above post if you agree. Now that we all have this tool, it's becoming more of an issue. With security being such an issue this day and age, casually asking for the 'sa' password so that we can change a Vendor ID seems rather extreme.

It's time to give the Controller the right to control the accounting system's details without the ability (and liability) to drop all of the company's databases.

This must be 'hard-coded' into the Dexterity script, let's vote to get that coding removed!

Live the Dream!
Leslie

Be sure to Invoice Match every Receipt

https://connect.microsoft.com/dynamicssuggestions/feedback/details/669719/gp-never-forget-to-match-that-purchase-order-again

I'm on a roll tonight. I love version 2013, but still there are some basic items I think need to be addressed. If you think so too, cast your vote by using the link above.

Are you tired of your Accrued Purchases account not matching your Received but not Invoiced report? How many times has a vendor payable been entered without matching it to the receipt?

Wouldn't it be nice if Dynamics GP warned you if you were entering a payables transaction for a vendor that has an outstanding PO or Uninvoiced receipt?

Too often a vendor invoice comes into the company and it's entered into A/P without going through the invoice matching procress in POP. We need a method for the user to be alerted if a vendor's invoice is entered to the Payables transaction entry window while there is an outstanding uninvoiced receipt.

The risk is that we end up with uninvoiced POs or worse, the vendor is paid twice by mistake.

Let me know what you think, or if you have devised a non-policy workaround.

Until next post!

Leslie

Perform a 'Credit Card' run like a Check Run

https://connect.microsoft.com/dynamicssuggestions/feedback/details/763004/perform-a-credit-card-run-just-like-a-check-run

If you want to pay your vendor invoices with a credit card in a batch AND be able to reconcile the credit card statement like you do a bank statement, please vote for the above product enhancement by clicking on the shortcut above.

Paying vendors with a credit card is the standard for many companies. You can get downloads from the credit card companies and import vendor payables. We need to keep track of the actual vendors for better expense management.

The problem is that the only way we can pay vendor invoices with a credit card is to enter a manual payment. A manual payment is not a viable solution for a company with more than just a few credit card transactions.

We need to be able to use a credit card as the payment source for a 'check run'. As part of that process, we need to be able to reconcile the credit card source with the credit card bill much like we do with bank statements and checkbooks.

If you agree, please vote for the enhancement using the link above.

Thanks!

Until next post!

Leslie

Product Suggestion. Add item to all sites

Multi-site users Unite!

Dynamics GP allows you to assign all items to a single site, but it does not let you assign an item to ALL sites.

I think it's time we had a window where we could pick an item, or a group of items and assign them to the site(s) of our choice with the click of a button. If you think so too, vote for the suggestion using the link below.

https://connect.microsoft.com/dynamicssuggestions/feedback/details/763003/assign-an-item-to-all-sites#tabs

Let's get these details cleaned up Microsoft, we have the best product - let's prove it.

Until next post

Leslie

The Resurgence of Dexterity - - Dexterity Lives!!

Learn it while you still can!


Here at the Technical Airlift, VSTools programmers and VBA programmers alike have learned that support for these two products will not be included in the phase 1 release of the 2013 Web Client.

So what’s a guy to do??? For VSTools, the answer is simple. You need to create your user interface in Dexterity; all of your .NET code should work just fine. Well, you will also need to modify your code to point to the new events, but that should be a piece of cake for a .NET programmer.

For VBA, it’s a little more challenging; but just a little. You need only to transform your VBA code into sanScript. Nothing more than a little syntax change. If  you have any VBA forms, you need to fashion them into Dexterity forms. You’ll find Dexterity is even easier to work with than VBA; and it looks better too.

What you need to do now, is learn Dexterity. But where? How? These are the important questions.

You’re in luck!

There is a Dexterity Basics class running in Orlando, Florida the week of October 8th. Just think, you could have all of your code converted before the Holidays. Just in time for the 2013 release! There are still some spots in the class available for you.

The Dex I class is being held at the facility of Integrated Business Group, they are a Certified Learning Resources partner and have the best classrooms I have ever seen. Comfortable chairs and lots of room.

Spend the week with me in Orlando and you’ll learn everything described in the synopsis below.

For more information and to register for the class, call Roxanna Alvarez @ 407-677-0370 or e-mail her directly ralvarez@ibgnet.com.

Date and Location:

• When: October 8th – October 12th
• Where: Orlando Florida

This class is very hands-on and starts at the beginning; no previous Dexterity experience is necessary.
You can download a synopsis of the class Here:

http://www.4shared.com/document/gK3muz4n/Dexterity_I_Synopsis_.html

I have pasted the synopsis below if the download doesn't work.

I hope to see you there!

Until Next Post!

Leslie Vail

Dexterity I – Product Fundamentals
Synopsis:
Dexterity Product Fundamentals will teach you about the development environment used to build the Microsoft Dynamics GP family of products. This course will provide you with the foundation you need to build integrated, database-independent applications. In addition, you will learn how to provide powerful application functionality for Microsoft Dynamics GP.  By actively participating in these classes, you should learn:  Basic programming techniques used by Microsoft Dynamics GP to develop their core modules.

• How to modify existing Dynamics GP forms and reports in order to make customizations for customers.
  
• How to develop add-on applications that will interact with the Dynamics GP products to meet specific requests by customers.
  
• How to package and ship your dictionary allowing Microsoft Dynamics GP to make updates to Dynamics GP without affecting the developer's dictionary.
  
• How to register your product with Microsoft Dynamics GP using a unique Product ID to ensure no duplication of add-on applications with the same name.
• How to develop add-on applications that will "look and feel" like Dynamics GP and provide seamless interface between the applications to ensure a professional finished product.
• How to use object triggers, which are scripts in your applications that are invoked by events in Dynamics GP, allowing several developers to deliver enhancements to the same Dynamics GP form.

General Prerequisites Some knowledge of basic programming concepts.

Microsoft Dynamics GP 2013 Beta release available for download

Let me throw my hat in the ring and announce the GP 2013 beta code has been released for us to download.

I know everyone will rush in and install the GP 2013 client and the Web Client, but let’s not forget about all of the other goodies on that DVD. Install the SDK, and Dexterity too!
You can download the code from HERE

https://mbs.microsoft.com/partnersource/support/selfsupport/productreleases/MDGP2013_TAPReleaseDownload

Enjoy!  Let us know how much you enjoyed installing the Web client

Until next Post!

Leslie

Installing the GP 2013 Web Client–roll up your sleeves!

On Monday, I went to the Jump Start training class where we learned about the new Web Client for 2013. In the afternoon, we actually installed the client. This post consolidates the 20 page installation process into 13 easy steps.

Before you get started, you need adequate hardware.

Server Requirements

• Windows Server 2008R2 – 64 bit only (Server 2008 is not good enough)
• All machines (including clients) must have valid certificates (SSL)
• You need a Web Server that is IIS enabled (does not need to be on any other machine that the web server)
• RAM – 16 G
• CPU – TBD
• NICs – TBD

Network Requirements

• Open Ports for inter-machine services communication
• Windows Firewall exceptions [Installer should open the required ports]
• Router changes for external traffic into DMZ
• DNS changes to support internal and external name resolution (so, you need two)

Client Requirements

• Internet Explorer 8 or 9, 32 bit or 64 bit
• Silverlight plugin installed
• Windows 7 or higher. No to XP, don’t know about VISTA. But, be reasonable, who’s running Vista?
• Office 2007 or better
• Changes are needed to the Registry so that the Silverlight App to interact with the file system. (It needs to make a command and execute shell call)

Now that you have your equipment in place, let’s install the Web Client.

Installing the Dynamics GP 2013 web client 

1. Install Dynamics

• Run the same setup file as if you were installing the rich client.
• When you get to the features window scroll down and mark the Web Client Runtime feature
• Go through the normal installation process, launch utilities to synchronize the dictionary, and then open Fabrikam. Close Dynamics after verifying that you can log in successfully.

2. Create a New Digital Certificate Template

• You need a new one because the default template doesn’t allow you to export private keys

3. Issue the new Template you just created

• You need to issue the template so new certificates can make use of it.

4. Generate a certificate from the new template

5. Export the Server Certificate you just generated

• Once you have created the certificate, you need to export it to a file that can then be copied to each member server that needs the certificate installed.

6. Import the Server Certificate that you just exported.

• The IIS site(s) where the Web Client and Web Management Console will be installed need to have an SSL certificate binding
  
• Import the certificate created by Active Directory Certification Services

7. Create an IIS web site for the GP 2013 Web Client

• The GP 2013 Web Client resides on a web site in IIS. You need to manually create this site before you can install the Web Client

8. Register .NET Framework 4.0 to the new IIS site

9. Create SSL Binding on GBWEB IIS Site

• Now, you need to use the digital certificate you imported to bind an SSL port to the GPWEB site in IIS Manager

10. Create Domain Users and Groups

• Even though you may be logging in to the server using a domain administrator account, you will want to create service accounts that run the Web Application Pools. Windows Service accounts should NOT be administrator accounts.

11. Install Dynamics GP 2013 Web Client Components

• Use the same installation media as you do to install the full workstation client. You have already installed the full workstation client, therefore, this step will involve adding the Web Client Components to the existing client.
  
• Click on the same setup.exe file you use for the full desktop client.
  
• Chose to install the Web Client from the list of Additional Products.
  
• 
  
• 
  
• 
  
• Select the Custom installation option


• Leave all of the features selected and accept the default installation path.


• Enter the Domain Group(s) that you created to access the Web Client and the Web Management Console


• Accept the default settings on the GP Configuration window


• Click the drop-down next to the Web Site and choose the web sit you created for the web client installation.


• Enter the domain account that was created for the identity of the web client web site


• Enter the name of the SQL Server where the Web Management Console database will be created.


• In the Web Management Cole Database window, provide the connection information to the database for the web management console; accept the default database name.


• In the Session Central Service window, provide the configuration information for the Session Central Service.


• In the Session Service window, provide the configuration information for the Session Service.


• In the Runtime Service URL window, provide the information for accessing the Runtime Service.


• Finally! Click the Install button to install the Web Client components. Exit when the installation completes.

12. Open your browser

13. Go to the URL of your Web client, and then log in.

Be amazed at the new GP 2013 Web Client!

• 

Piece of cake! (Eeek!)

 

Until next post!

 

Leslie

The Fix is In! Bring back your SmartList Reminders.

The problem with SmartList Reminders crashing GP has been fixed! I initially described the problem brought about by GP 2010 SP 3:

SP 3 will Crash GP

Microsoft has just released HotFix KB2731285 to correct this issue. You can download the fix at:

https://community.dynamics.com/product/gp/gptechnical/b/dynamicsgp/archive/2012/09/07/the-smartlist-builder-reminders-fix-is-now-available.aspx

Application of this HotFix does not change the build number of Dynamics GP, SmartList, or SmartList Builder. To install the HotFix,

1. Extract the .zip file


2. Copy the SLBldr.cnk file and paste in into the application folder for Dynamics GP. The default is %path%\Microsoft Dynamics\GP2010.


3. Launch Microsoft Dynamics GP.


4. Click YES if prompted to install new code.


5. Repeat steps 2-4 at all client workstations.

Many thanks to Nicole Albertson at Microsoft for staying on top of this and letting me know when the problem was resolved. You’re a Rock star, Nicole! Until next post

Leslie

Restarting a File Transfer

Today I started several file transfers and then I needed to suspend them and take my computer away. I turned off my computer and off I went.

When I returned, I wanted to resume the file transfers. Hmm, how do you re-start the transfer manager?

I tried downloading a few files hoping the transfer manager would open and then I could just resume from there. But, alas, that did not work.

After flailing around a little bit, I discovered the answer.
To restart the transfer manager, execute this:

%windir%\Downloaded Program Files\TransferMgr.exe

Works like a champ!

Until next post!
Leslie

Dexterity Basics class is scheduled for October 8th–12th 2012

Come to beautiful Orlando and spend 5 days in the plush training center of Integrated Business Group (IBG). IBG is a Certified partner for Learning Resources and have the best classrooms I have ever seen. Comfortable chairs and lots of room to spread out!

The Dexterity basics class takes you from opening the book for the first time to creating a small integrating application with Dynamics GP.

It’s that time of year for another fun-filled week immersed in the world of Dexterity.
For more information and to register call Roxanna Alvarez @ 407-677-0370 or e-mail her directly ralvarez@ibgnet.com.

Date and Location:

• When: October 8th – October 12th, 2012
• Where: Orlando Florida
This class is very hands-on and starts at the beginning; no previous Dexterity experience is necessary.
 

Who should attend?

Customers or Partners who will be customizing or building add-on or standalone applications to integrate with the Microsoft Dynamics GP products. VSTools programmers who will need to design their windows using Dexterity instead of WinForms will also benefit from this training. You will learn how to create windows and also how to create Dexterity tables.

A synopsis of the class is below this post.

I hope to see you there!

Until Next Post!

Leslie Vail

The basics class covers the following topics:
Dexterity I – Product Fundamentals
Synopsis:
Dexterity Product Fundamentals will teach you about the development environment used to build the Microsoft Dynamics GP family of products. This course will provide you with the foundation you need to build integrated, database-independent applications. In addition, you will learn how to provide powerful application functionality for Microsoft Dynamics GP.
By actively participating in these classes, you should learn:

Basic programming techniques used by Microsoft Dynamics GP to develop their core modules.

• How to modify existing Dynamics GP forms and reports in order to make customizations for customers.
• How to develop add-on applications that will interact with the Dynamics GP products to meet specific requests by customers.
• How to package and ship your dictionary allowing Microsoft Dynamics GP to make updates to Dynamics GP without affecting the developer's dictionary.
• How to register your product with Microsoft Dynamics GP using a unique Product ID to ensure no duplication of add-on applications with the same name.
• How to develop add-on applications that will "look and feel" like Dynamics GP and provide seamless interface between the applications to ensure a professional finished product.
• How to use object triggers, which are scripts in your applications that are invoked by events in Dynamics GP, allowing several developers to deliver enhancements to the same Dynamics GP form.

 

Warning! Install GP2010 SP 3 and lose your SmartList reminders!

This issue has been fixed, look at The-fix-is-in-bring-back-your-smartlist.

If you do not want to run the risk of losing some of your SmartList reminders, then do not install SP 3. To my chagrin, I learned yesterday that there is a bug in SP3. In my world, that bug is a train wreck to many of my clients. Microsoft tech support acknowledged the existence of the bug and does not have any clue as to when it might be corrected.
Here’s what happens. Let’s say you create a SmartList object using SmartList Builder. Your users fashion favorites based on the created SmartList and then use those favorites in Reminders. After you install SP 3, the users with reminders based on objects built in SmartList Builder will not be able to log in to the system. That’s it, the system crashes, and crashes hard!
If you remove the reminders based on the SmartList Builder objects, your users will be able to log in just fine. So the “work around” is to delete the reminders. What! Yes, that’s the work around, you cannot use the feature. I don’t know how it affects you all, but that bit of news is a crushing blow in my world.
This problem has been a difficult one for Microsoft to isolate. They told me that they didn’t know if the problem was being caused by SQL code, Dexterity scripts, or the VSTools addins. All three are in play with SmartList Builder.
The error is easy to duplicate, and it fails just as the system begins to draw the home page window. If you are already logged in when you created the linked reminder, as soon as you refresh your home page you will get the error below, and GP will fail.

The next time you try to log in to GP, right after the navigation pane comes to your window, the system will crash. The partial screenshot below shows you what I mean.

To identify the SmartList reminders you have at risk, you can query the DYNAMICS database. Type the statement below in the query window of Management Studio.

SELECT * FROM SY01404 WHERE DICTID = 3830

SmartList builder’s product ID is 3830.
This problem does NOT require you to disable SmartList Builder itself, you just cannot build any Reminders off of the SmartLists you create. You can:

• Create and use Reminders that are not based on a Favorite belonging to an object created in SmartList Builder.
• Create Favorites based on an object created in SmartList Builder.
• Create and use Reminders based on a SmartList object that came with GP.

The bottom line is that you still have access to the custom Favorite, you just can’t create a reminder based on it.
While there are many ways to present the information that was captured by the Reminders, I haven’t been able to come up with an alternative that is as elegant and user-friendly as the SmartList Reminders.
I welcome anyone’s ideas on how to easily replace the SmartList Reminders until the time we get a fix from Microsoft.
Go forward with caution! Consider yourself warned.
Until next post!
Leslie Vail
Dallas, TX

Demystifying GP Tables and Transaction Flows

    

Table Names and Transaction Flow

WORK to OPEN to HISTORY

Update of http://dynamicsconfessions.blogspot.com/2009/11/moving-from-work-to-open-to-history.html

This article started (as so many do) from a question posted on the newsgroup about how data flows through the system and how you can figure out what the tables contain. I did a little research and concluded there wasn’t any summary of data flow and table conventions available. This post is a result of that research.

Tables – what’s in a name?

First, a short explanation of table names is in order. Each table has three names. Sometimes they are the same, but most of the time they are different. The Dexterity programmer determines the names for the tables. The three different table names are:

1. Technical Name
2. Display Name
3. Physical Name

Technical Name

The Technical Name is the name used by the Dexterity programmers in Dexterity code. You will often see the Technical Name in error messages such as:

A Get/Change Operation on table 'UPR_Year_End_WORK_HDR'failed accessing SQL data.

In the above error message, 'UPR_Year_End_WORK_HDR' is the Technical Name for the Payroll Year End Header table.

Display Name

The Display Name is also known as the ‘friendly name’. Display Names use whole words with spaces between them. Display Names protect the user from needing to translate Technical Names or Physical Names into real words. You see Display Names when you are working with a tool that is normally used by the end user. Tools such as SmartList Builder, Excel Report Builder and Report Writer use Display Names.

The screenshot below shows a list of Display Names on the Add Table window of SmartList Builder:

 

Physical Name

The Physical Name is the name seen by the database. Any type of error at the SQL level, for instance, will use the Physical Name. The error message below uses the Physical Name:

 

Also, if you launch the Microsoft SQL Server Management Studio application you will see the Physical Names of the tables. The screen shot below shows you how the table names appear in the Microsoft SQL Server Management Studio.
 

Most of your independent reporting tools, like Crystal Reports, use Physical Names.

 

Tables –naming convention for physical tables

A client recently told me that they were very confused by the table names of the Dynamics GP tables. I’m so used to those legacy table names that I don’t appreciate what a wonder it must be for a new user trying to write reports or follow the data flow.

In fact, any class I train that involves pulling data from tables (Extender, SmartList Builder, Excel ReportBuilder, ListBuilder, Report Writer, SSRS, Crystal Reports, etc.) sooner or later results in the question “How do we know which table to use?”. This query spawned the on-line class I developed for GPUG (Great Plains User Group) entitled “Finding the Dynamics GP Data you Need”

So, what do the table names mean? The old table names were limited to eight characters; remember it was the late 1980’s when this was being developed. The field names were also limited to eight characters.
 

In order to bring some sanity to that eight-character limitation, a pretty straightforward naming convention was created. This convention was followed religiously for the modules actually written by Great Plains vs. those that were acquired from other developers.
 

I actually think it’s a very good naming convention for the Dynamics GP data tables. This is only a convention, however. It is followed by the Great Plains programmers pretty diligently, but not so much by 3rd party developers.
 

Here are the basics, the first 2 or 3 characters will indicate the module name, and the numbers indicate the type of table.
 

Some of the more popular module abbreviations are in the table below.

Prefix	Module
GL	General Ledger
AF	Advanced Financial Analysis
PM	Payables Management
RM	Receivables Management
SOP	Sales Order Processing
POP	Purchase Order Processing
IV	Inventory
IVC	Invoicing (NOT SOP)
UPR	US Payroll
CM	Cash Management (Bank Rec)
LK	Linked Transactions
ME	EFT
PA	Project Accounting
FA	Fixed Assets
AA	Analytical Accounting
DTA	Multi-dimensional Analysis *
SY	System or Company
AHR	Advanced HR
HR	Human Resources
BM	Bill of Materials
DD	Direct Deposit
EXT	Extender
MC	Multicurrency
SVC	Field Service
ASI	SmartList Favorites
ERB	Excel Report Builder
EXT	Extender
SLB	SmartList Builder
CPY	Canadian Payroll **
PDK	Personal Data Keeper **
*	Thanks to Victoria Yudin
**	Thanks to Beat Bucher

 

After the module abbreviation is a five character (normally 5 characters) number that indicates the table type.
 

Knowing what these numbers stand for will help you zero in on the correct table. The table below sets out the numbering convention for the table types.

Table Number	Table Type
00000	Master Tables
10000	Work Tables
20000	Open Tables
30000	History Tables
40000	Setup Tables
50000	Temp Tables
60000	Relation Tables
70000	Report Options Tables
80000	Posting Journal Reprint Tables
90000	Mixed bag – no standard type

 

Master Tables (00000) are mostly what you find under the ‘Cards’ area. These are your Customers, Vendors, Inventory Items, GL Accounts and the like. For instance, the information you see on the Customer Maintenance window is stored in the RM Customer MSTR or RM00101 table.

 

Work, Open and History (10000, 20000 and 30000) These tables are explained in the next section of this document.

 

Setup Tables (40000) include choices you have made to initiate a module. For instance, the information entered on the Payables Management Setup window is stored in the PM Setup File or PM40100 table.
 

Temp Tables (50000) are tables that are used temporarily by the system and the records in those tables can normally be deleted without issue. For instance, the Net Profit Temporary table or AF50000 is used by the Advanced Financial Analysis module to hold the Net Profit amount that will be used on one of the statements. Once the statement has been printed, the number is irrelevant.
 

Relation or Cross Reference Tables (60000) are tables that are used to store information that spans more than one module. For instance, the SOP/POP Link table (SOP60100) holds the information about POP documents linked to SOP documents.
 

Another example is the Sales Customer Item Cross Reference (SOP60300). This table reflects the information as to how customer item numbers relate to regular item numbers. There are not that many tables in the 60000 range.
 

Report Options Tables (70000) contain all of the information you enter in any of the report options windows. The image below comes from the Payables Trial Balance Report Options window (PM70500).

 

Posting Journal Reprint Tables (80000) contain all of the information you need to reprint your posting journals. So don’t feel like you MUST print all of those reports. They are here waiting for you should you ever need to reprint them.
 

Miscellaneous Tables (90000) this group is a mixed bag of information. The table below contains the Display Names of several 90000 tables.

 

Physical Name	Display Name
CM90000	CM Transmission Log
CM90001	Checkbook EFT Log
CN90000	Collections - User Preferences
ERB90100	Data Connection Products
ERB90200	Data Connection Series
ERB90300	Data Connections
ERB90400	Data Connection Restrictions
ERB90450	Data Connection Restriction Values
ERB90500	Data Connection Security
EXT90101	Product List
EXT90101	Resource Cache - Product List
EXT90102	Product Series List
EXT90102	Resource Cache - Product Series List
EXT90103	Form List
EXT90103	Resource Cache - Form List
EXT90104	Window List
EXT90105	Report List
EXT90106	Resource Cache - Table List
EXT90106	Table List
PA91301	PA Contract Segment Override Header
PA91304	PA Contract Segment Override Detail
PA92301	PA Contract Template Seg Override Header
PA92304	PA Contract Template Seg Override Detail
PDK90003	PDK File Error Log
PDK90100	PDK Security
PP900000	Deferral Opened Periods
PTO90000	PTO Pending Master Conversion
SE90001	Account Rollups Account List Accelerator
SE988977	Account Rollups Options Columns
SLB90000	Third Party GoTo Types
SY90000	SY_User_Object_Store (Dynamics User Object Store)
SY90100	Default Chart of Accounts

 

There are, of course exceptions even within the Dynamics.dic dictionary. Additionally, the DYNAMICS database has its own naming scheme. So now you know; if you were looking for the customer master table, you would start with the RM00*** table and go from there.

Victoria Yudin has some fantastic information on the popular tables from each module. If you are creating reports, or crafting SQL queries, it is information you can use http://victoriayudin.com/gp-tables/.

Victoria also has some very helpful SQL queries available for you to download.

Let’s move on to our WORK/OPEN/HISTORY discussion

 

Data Flow (WORK, OPEN and HISTORY progression)

I put together this document attempting to address the progression of transaction data for the following modules:

·         GL (Financial – general ledger journal entries)

·         CM  (Bank Transactions)

·         RM (Receivables Management – A/R transactions)

·         PM (Payables Management – A/P transactions)

·         SOP  (Sales Order Processing)

·         POP (Purchase Order Processing)

·         Invoicing (Basic invoicing, nothing to do with SOP)

·         Inventory
 

Dynamics GP transactions typically have three states:

1. Unposted
2. Posted
3. Historical
   These different states determine, to a large degree, whether the data is in the WORK, OPEN, or HISTORY table.
    

What causes the information to move through these tables varies. The progression is not consistent across modules and it’s easy to get bewildered trying to figure it out.
 

Below you will find a short summary of how the data moves through these tables. Also included under each section is a list of the more common tables for that module’s WORK, OPEN and HISTORY tables. The lists are not comprehensive, but they’ve captured the major tables.

GL (financial– general ledger)

WORK–

Unposted GL Transactions

 

Table Number	Description
GL10000	Transaction WORK
GL10001	Transaction Amounts WORK
GL10002	Transaction Clearing Amounts WORK
GL10100	Quick Journal WORK
GL10101	Quick Journal Amounts WORK
GL10300	General Ledger Tax WORK
GL12000	Budget Transaction WORK
GL12001	Budget Transaction Amounts WORK

A transaction lives in the Transaction WORK table before it has been committed to the ledger. You can still edit or delete a transaction when it is in the WORK tables.

 

OPEN–

Posted GL Transactions

Table Number	Description
GL20000	Year-to-Date Transaction OPEN

Once a transaction is posted, it is committed to the ledger. The header information is added to each line such that each leg of the journal entry is a complete record. The posted transaction moves to the transaction OPEN table. For GL, each record in the OPEN table represents a single leg of the journal entry.

 

HISTORY–

Transactions from a closed year

Table Number	Description
GL30000	Account Transaction HISTORY
GL32000	Budget Transaction HISTORY

Until the year closes all of the GL transactions are in the Year-to-Date Transaction OPEN table (GL20000).  I don’t mean when the year ends, but rather after the year-end closing routine is done. After the closing routine, the posted transactions move to the transaction HISTORY table (GL30000). You cannot move them back to the OPEN table after the year end closes. In addition, you cannot ‘unpost’ a transaction and move it back into the WORK table.
 

As with all rules, there are exceptions. Here's one. Using the Fiscal Periods Modifier tool in the Professional Services Tools Library you can indeed open a previously closed year. However, the data does not move from the Account Transaction HISTORY table (GL30000) over to the Year-to-Date Transaction OPEN table (GL20000).

CM (Bank Transactions)

WORK–

Unposted Deposit Transactions

Table Number	Description
CM10100	Deposit header information
CM10101	Each item to be deposited
ECM10003	ECM_Committed_Detail
ECM10002	ECM_CPO_Inquiry
ECM10001	ECM_Inquiry
ECM10110	ECM_Line_Ctrl
ECM10005	ECM_ReqItemList

 OPEN–

Posted Bank Transactions

Posted Deposit Transactions

Reconciliations

Table Number	Description
CM20203	Checkbook EFT Transaction Batch
CM20202	Checkbook Transaction Electronic Funds Transfer
CM20400	CM Distribution
CM20100	CM Journal
CM20300	CM Receipt
CM20501	CM Reconcile Adjustments
CM20500	CM Reconcile Header
CM20200	CM Transaction
CM20201	CM Transaction Totals
CM20600	CM Transfer

 

HISTORY–

There is no HISTORY table

PM (Payables Management – A/P transactions)

WORK–

Unposted PM Transactions

Table Number	Description
PM10100	PM Distribution WORK OPEN
PM10200	PM Apply To WORK OPEN File
PM10100	PM Distribution WORK OPEN
PM10400	PM Manual Payment WORK File
PM10201	PM Payment Apply To WORK File
PM10300	PM Payment WORK
PM10500	PM Tax WORK File
PM10000	PM Transaction WORK File

OPEN–

Posted PM Transactions

Posted Purchasing Invoices

 

Table Number	Description
PM10100	PM Distribution WORK OPEN
PM10200	PM Apply To WORK OPEN File
PM10100	PM Distribution WORK OPEN
PM20401	PM Scheduled Payment LINE
PM20400	PM Scheduled Payments Header
PM20000	PM Transaction OPEN File

Posted transactions stay in the OPEN table until they have been fully applied. OPEN means there is still an amount outstanding. If you write a check that partially pays an invoice, that invoice would remain in the OPEN table. The check, however, if its entire balance was applied, would move to the HISTORY table.

                

HISTORY–

Posted, Fully Applied, or Voided PM Transactions

Table Number	Description
PM30300	PM Apply To HISTORY File
PM30600	PM Distribution HISTORY File
PM30200	PM Paid Transaction HISTORY File
PM30401	PM Scheduled Payments Header HISTORY
PM30700	PM Tax HISTORY File
PM30800	PM Tax Invoices

Transactions automatically move to HISTORY when the document becomes fully applied (a transaction is a check, credit memo, invoice, etc.) For a check or credit memo, fully applied means the entire balance of the check or credit memo has been matched to an invoice. For an invoice, it is when the balance is fully paid off (or written off), or a credit memo takes the balance to zero. Voiding a document in the OPEN table will automatically move the voided document to HISTORY.

Documents in HISTORY can also be voided thereby moving them back to the OPEN table. Documents cannot be 'unposted' and moved back to the WORK table. Likewise, documents cannot be “unvoided” and moved back to the OPEN table.

RM (Receivables Management - A/R Transactions)

WORK–

Unposted RM Transactions

Table Number	Description
RM10201	RM Cash Receipts WORK File
RM10501	RM Commission WORK File
RM10101	RM Distribution WORK File
RM10301	RM Sales WORK File
RM10601	RM Tax WORK File

 

OPEN–

Posted RM Transactions

Posted SOP Invoices

Posted SOP Cash Receipts

Voided RM Transactions

Table Number	Description
RM10101	RM Distribution WORK File
RM20201	RM Apply OPEN File
RM20101	RM OPEN File
RM20400	RM Scheduled Payment Header
RM20401	RM Scheduled Payment Line

 

HISTORY–

Posted and Fully Applied Receivables Transactions

Table Number	Description
RM30501	Commission HISTORY
RM30301	Receivables GL Distribution HISTORY
RM30201	RM Apply HISTORY File
RM30502	RM Batch HISTORY
RM30101	RM HISTORY File
RM30401	RM Scheduled Payment Header HISTORY
RM30601	RM Tax HISTORY File

Unlike PM transactions, RM transactions do NOT automatically move to HISTORY. You need to run the 'Paid Transaction Removal' routine in order to move the fully applied transactions to HISTORY. Nothing automatically moves to HISTORY, not voids, not write-offs, not payoffs, nothing. Only 'fully applied' documents will be moved via the 'Paid Transaction Removal' routine.

Therefore, if you still have a balance on an invoice, it stays in the OPEN table. If a credit memo or receipt has not been matched up to an invoice, it will stay in the OPEN table. To get it out of the OPEN table and move it to HISTORY, run the 'Paid Transaction Removal' routine. The fact that the customer's net balance is zero does not impact whether or not the document moves to HISTORY.

When you run the Paid Trx Removal you specify a cutoff date that applies to:

     ·         NSF checks

     ·         Voided documents

     ·         Waived (finance charges)

     ·         Paid Transactions

You specify a separate date for:

     ·         Checks

Once RM transactions are moved to HISTORY - they cannot be voided thereby moving them back to OPEN. This is why the checks have a separate date - what if they bounce!
 

Documents can be 'unapplied' only while they are in the OPEN table, not after they have been moved to HISTORY. For instance, if you applied a cash receipt to the wrong invoice, you can change it to the correct invoice only if it has not been moved to HISTORY. Documents can be voided only while they are in the OPEN table. Voided documents stay in the OPEN table until they are moved to history via the 'Paid Transaction Removal' routine. You cannot void a document in the HISTORY table.
 

But here’s one HUGE exception.  Using the Professional Services Tools Library (PSTL) you can indeed “unapply” an amount from a document in HISTORY.  If, for example, an invoice had been completely paid off and moved to history and then you realize the cash was applied to the wrong invoice. Using the (PSTL) tool you can ‘unapply’ the cash thereby moving both the cash receipt and the invoice back into the OPEN table.
 

Once you have the documents back in the OPEN table you can apply the check to the correct invoice, void the document, write off the document or do anything else you can ordinarily do with an OPEN document.

SOP (Sales Order Processing)

WORK–

Unposted SOP documents

Table Number	Description
SOP10100	Sales Transaction WORK (SOP Header)
SOP10200	Sales Transaction Amounts WORK (SOP Line Items)
SOP10102	Sales Distribution WORK and HISTORY (Debits and Credits)
SOP10202	Sales Line Comment WORK and HISTORY (Comments on Line Items)
SOP10101	Sales Commissions WORK and HISTORY
SOP10203	Sales Order Bin Quantities WORK and HISTORY
SOP10103	Sales Payment WORK and HISTORY
SOP10201	Sales Serial/Lot WORK and HISTORY
SOP10105	Sales Taxes WORK and HISTORY
SOP10106	Sales User-Defined WORK HISTORY

An unposted document includes not only Invoices and Returns, but also Quotes, Orders and Backorders.
 

OPEN–

There is no OPEN file

When a SOP document is posted, the transaction moves to the RM OPEN table and the SOP document moves to the SOP HISTORY table. Only Invoices and Returns can be posted. The posted SOP document becomes an open receivable (or credit) in the RM OPEN table.
 

HISTORY–

Posted SOP Invoices or SOP Returns

Voided SOP Documents

Orders whose items have been fully transferred to other documents

Quotes that have had any item transferred to another document

Backorders that have been fully transferred to other documents

Table Number	Description
SOP30200	Sales Transaction HISTORY (Header information)
SOP30300	Sales Transaction Amounts HISTORY (Line item information)
SOP30201	Sales Deposit HISTORY
SOP10102	Sales Distribution WORK and HISTORY (Debits and Credits)
SOP10202	Sales Line Comment WORK and HISTORY (Comments on Line Items)
SOP10101	Sales Commissions WORK and HISTORY
SOP10203	Sales Order Bin Quantities WORK and HISTORY
SOP10103	Sales Payment WORK and HISTORY
SOP10201	Sales Serial/Lot WORK and HISTORY
SOP10105	Sales Taxes WORK and HISTORY
SOP10106	Sales User-Defined WORK HISTORY

SOP invoices and returns automatically move to HISTORY as soon as they are posted. For documents that do not automatically transfer to HISTORY, run the 'Reconcile-Remove Sales Documents' utility to move them.
 

The following documents cannot be posted:

·         Quotes

·         Orders

·        Backorders

POP (Purchase Order Processing)

WORK–

Any Purchase Order document that has not been moved to history

Table Number	Description
POP10100	Purchase Order WORK
POP10110	Purchase Order Line
POP10300	Purchasing Receipt WORK
POP10310	Purchasing Receipt Line
POP10150	Purchase Order Comment
POP10160	Purchase Order Tax
POP10340	Purchasing Bin Quantities WORK
POP10550	Purchasing Comment
POP10390	Purchasing Distribution WORK
POP10700	Purchasing Landed Cost
POP10140	Purchasing Manufacturer Numbers
POP10500	Purchasing Receipt Line Quantities
POP10306	Purchasing Receipt User-Defined
POP10330	Purchasing Serial Lot WORK
POP10600	Purchasing Shipment Invoice Apply
POP10360	Purchasing Tax

OPEN–

There is no open file
 

HISTORY–

Posted Receipts automatically move to history

Posted Purchasing Invoices automatically move to history

Voided Purchasing Documents automatically move to history

Closed Purchase Orders

Cancelled Purchase Orders

Table Number	Description
POP30100	Purchase Order HISTORY (PO Header)
POP30110	Purchase Order Line HISTORY (PO Line Items)
POP30300	Purchasing Receipt HISTORY (Receipt Header)
POP30310	Purchasing Receipt Line HISTORY (Receipt Line Items)
POP30160	Purchase Order Tax HISTORY
POP30000	Purchasing Batch HISTORY
POP30340	Purchasing Bin Quantities HISTORY
POP30390	Purchasing Distribution HISTORY
POP30700	Purchasing Landed Cost HISTORY
POP30330	Purchasing Serial Lot HISTORY
POP30360	Purchasing Tax HISTORY

Posted Receipts, Posted Purchasing Invoices and Voided Purchasing Documents automatically move to history. You must run the Purchasing Routine 'Remove Completed Purchase Orders' to move all of the closed or canceled POs to HISTORY. If a PO is not closed or canceled it will not be moved, so there is no danger of moving a PO that doesn't qualify. You can set restrictions on which POP documents are examined for removal. It is not an ‘all or nothing’ kind of routine.

IVC (Basic Invoicing – not SOP)

WORK

Unposted Documents

Table Number	Description
IVC10400	Invoicing Commissions
IVC10300	Invoicing Distributions
IVC10103	Invoicing Line Comments
IVC10200	Invoicing Payments WORK
IVC10102	Invoicing Serial and Lot Number WORK
IVC10500	Invoicing Taxes
IVC10101	Invoicing Transaction Amounts WORK
IVC10100	Invoicing Transaction WORK

 

OPEN

There is no open file (posted transactions update receivables, like SOP)
 

HISTORY

Posted Invoices

Posted Returns

Table Number	Description
IVC30102	Invoicing Transaction Amounts HISTORY
IVC30101	Invoicing Transaction HISTORY
IVC10400	Invoicing Commissions
IVC10300	Invoicing Distributions
IVC10500	Invoicing Taxes
IVC10103	Invoicing Line Comments
IVC10102	Invoicing Serial and Lot Number WORK

Invoices and Returns automatically move to the HISTORY table when posted. Like SOP, when and invoice or return is posted, the customer transaction record moves into the RM OPEN table. The transaction, therefore becomes an open receivable or credit.

IV (Inventory)

WORK

Unposted Adjustments

Unposted Variances

Unposted Stock Counts

Unposted Transfers

 

Table Number	Description
IV10004	Inventory Bin Quantity Transfer
IV10201	Inventory Purchase Receipts Detail
IV10200	Inventory Purchase Receipts WORK
IV10002	Inventory Serial and Lot Number WORK
IV10001	Inventory Transaction Amounts WORK
IV10003	Inventory Transaction Bin Quantities WORK
IV10000	Inventory Transaction WORK

OPEN

There is no open file
 

HISTORY

Posted Transactions

 

Table Number	Description
IV30500	Inventory Distribution HISTORY
IV30300	Inventory Transaction Amounts HISTORY
IV30100	Inventory Transaction Batch HISTORY
IV30302	Inventory Transaction Bin Quantities HISTORY
IV30301	Inventory Transaction Detail HISTORY
IV30200	Inventory Transaction HISTORY
IV30600	Item Lot Attribute HISTORY
IV30400	Item Serial and Lot Number HISTORY

 

Inventory transactions are automatically moved to history when posted.

That's it! Please let me know of any adjustments you think I need to make to this.
 

Until next post!
 

Leslie