accountingBot Blog

To most new-comers to the Dynamics GP world, the table structure that gets created in SQL when GP is deployed can appear somewhat daunting. For one thing, the naming convention appears to be a bunch of meaningless letters and numbers. In fact, these letters and numbers do have meaning. I hope to make it clear in this article.

If you navigate to a GP company database in SQL using the SQL Management Studio and look at the tables, you will see something like:

Why did Microsoft name these tables this way?  Well, the answer is quite simple.  Dynamics GP was actually originally developed for Btrieve and C-tree years before Microsoft purchased the company.  Each table was actually a file in DOS.  Since in those days, DOS and Windows 3.1 were 16-bit operating systems, the length of the table names could not be more than 8 characters.  So, years later, the physical table names were inherited by the most current versions of Dynamics GP which is why we still have these table names.

Ok, that’s nice, but how do we find out what RM00101 means?  The answer is quite simple.  All of the tables used in Dynamics GP are actually stored in what’s called a dexterity dictionary.  This dictionary contains all of the code, tables, reports, etc that represents what you see when you log into Dynamics GP.  To find the description for RM00101, you need only log into GP and navigate as follows:  Microsoft Dynamics GP -> Tools -> Resource Descriptions -> Tables.  You should see this window:

Click on the ellipses button (…) to the right of the Table field and a list of tables will be displayed.  By default, only the tables in the main Dynamics GP dictionary for the series ‘Financial’ will be displayed.  You can change the product to another 3rd party dictionary or change the series to see the revelevant set of tables.  For now, let’s assume that you want to know what RM00101 is.  Well, we know that RM stands for ‘Receivables Management’ and that Receivables are related to Sales.  So, ‘Sales’ is the series you’d be interested in.  Then, to make it easier to find RM00101, change the ‘View by: ‘ sort to by ‘Table Physical Name’.  Then, scroll down until you see RM00101.  Once you find it, you’ll notice that it refers to a display name of ‘RM Customer MSTR’.

If you want to see additional information about this table, click OK.  You should then see the following window:

The Physical Name column represents the fields that you would see in SQL for the RM00101 table.  The Field column represents the logical name stored in the dexterity dictionary.

Now would be a good time to go over the different types of names that are stored within Dynamics GP.  They are as follows:

• Display Name
• Technical Name
• Physical Name

The Display Name refers to the caption that would appear to a user.  It’s kind of like the ‘pretty name’ without hyphens or any computer type characters.

The Technical Name is really more useful for programmers that are interfacing with GP either through Dexterity or some other language.  The technical name should be unique for the type of object in the dictionary.  I.e. there should not be more than one ‘RM_Customer_MSTR’ table in the dictionary.

Finally, the physical name is what would appear in SQL.

An extra bit of information to help you understand the table structure a little better is that the numbers in the physical table names actually do mean something.  As an example, in the RM series, here’s what the numbers represent:

RM00100 – RM00999 – represents the master types of data (i.e. Customer Master, Customer Address Master, Customer Summary, Salesperson Master, etc.)

RM10000 – RM19999 – represents the unposted transaction data (i.e. unposted sales transactions).

RM20000 – RM29999 – represents the open posted transaction data (i.e. RM Open transactions).

RM30000 – RM39999 – represents the historical posted transaction data (i.e. RM History transactions).

RM40000 – RM49999 – represents the receivable setup tables.

Now there are exceptions in some cases but in general, this is how tables are structure across most of the modules.

In conclusion, the table structure in GP isn’t as difficult as it may first seem.  There are a lot of resources out there to make it easier to identify the meaning of the data.  The ‘Table Descriptions’ form from GP is just one of these methods.

About the Author:

I’m Dan Sionov, founder of accountingBot. I’ve been developing software for Dynamics GP since 1995 and haven’t grown tired of it. We make eftEmailer, a flexible yet simple to use application for Dynamics GP that allows you to send e-mails to vendors receiving EFT direct deposits.

Ever wonder what Dynamics GP is really doing when you press a button on a form?  Most Dynamics GP users are already familiar with the Dex SQL Log where you can see the SQL statements that get sent to the SQL Server.  But Dynamics GP also offers the ability to log the scripts that are being processed within the Dexterity run-time environment.  This article will show you how to use this powerful feature.

To use the ‘Log Scripts’ feature from Dynamics GP, you must turn on the Script Debugger in the Dex.ini file.  You will find the Dex.ini in ‘\Program Files\Microsoft Dynamics\GP$..\Data’ folder if you’re using Dynamics GP 10 and in ‘\Program Files\Microsoft Dynamics\GP’ folder if you’re using previous versions.  Edit the Dex.ini file using notepad.  Add the statement ‘ScriptDebugger=TRUE’ if it doesn’t already exist under the section header [General] as shown in the screen shot below.

When you’re done, save the file and launch Dynamics GP.  After you have logged into GP, you will notice a new menu item called ‘Debug’ either to the left or right of your regular menu items.  You will notice a bunch of new menu items within Debug but for our purposes, we will only be concerned with ‘Log Scripts’.

Now lets say you want to see what the Dexterity run-time is doing when you click ‘Save’ on the ‘Customer Maintenance’ window.  You can first open the ‘Customer Maintenance’ window by going to Cards -> Sales -> Customer.  Then add a new customer by just putting in the required fields (i.e. Customer ID and Name).  Then, before clicking on ‘Save’, click on the ‘Debug’ menu and ‘Log Scripts’.  A dialog will appear asking you where to save a file ‘Script.log’.  Find a place to save the file and click the ‘Save’ button.  Then click on the ‘Save’ button on the ‘Customer Maintenance’ form.  Finally, when GP is done saving the customer card, go back to the ‘Debug’ menu and click on ‘Log Scripts’ again.  This will turn off log scripting.

To see the output of the log, find the Script.log file in the path that you had originally specified.  Then just open it with notepad.  It should look something like this:

To many people, this may be a bunch of non-sense.  But, for a developer, this can be very useful.  In particular, you can see what types of Dexterity global procedures are being called and at what point.  With this information, you can create Dexterity triggers to add functionality at the right time.  This is particularly useful when you’re working with many dictionaries (i.e. a 3rd party dictionary).

In conclusion, the ‘Script log’ feature is a powerful feature that gives you a better understanding of what Dynamics GP is doing under the covers.

About the Author:

I’m Dan Sionov, founder of accountingBot. I’ve been developing software for Dynamics GP since 1995 and haven’t grown tired of it. We make eftEmailer, a flexible yet simple to use application for Dynamics GP that allows you to send e-mails to vendors receiving EFT direct deposits.

There are various competing products out there that offer this functionality in different ways. As my company (accountingBot) offers the eftEmailer, I will describe how to send remittance advices from that standpoint.

Though the eftEmailer product was designed to make it easy for companies to install, I’m only going to cover how to set it up (i.e. format the e-mail message and attachment) and send the remittances. If you want to get more information on this product, you can download the free user guide, flash demo, or evaluation edition of eftEmailer from http://www.accountingbot.com/cart.

Before I start, I’m assuming that your Dynamics GP environment has been set up for EFT.  That means that your checkbook has been configured correctly with the bank information and that you have various vendors set up correctly to be able to create EFT payments for them.  I’m also assuming that eftEmailer has already been installed.  If you want to follow along with this article but don’t have eftEmailer, you can download the evaluation edition of eftEmailer as described in the last paragraph.

Setting Up eftEmailer:

To set up eftEmailer, navigate to Microsoft Dynamics GP -> Tools -> Setup -> Purchasing -> eftEmailer Setup.

You will get the following window except yours will be empty:

Your next step is to select the checkbook you have set up for EFT and fill in the information on this form.  The following is an explanation of each item:

Sender E-mail Address:  This is the e-mail address you would like to appear to the vendors when they receive the remittances.  Some organizations put something like ‘donotreply@yourdomain.com’.  Or you can just put your e-mail address if you want vendors to be able to reply to you.

Display Name:  This is the name you would like to appear to the vendors when they they receive the e-mail.

Attachment File Name:  This is the name of the attachment file you would like the vendors to see.  The attachment file contains the actual remittance information such as which vouchers are being paid and the amounts.

Server Name:  This is the name of your smtp server.  If it’s not clear already, you will require an smtp server in order to send e-mails.  Most organizations already have it.  If you don’t know what yours is, just ask your network administrator and he/she should be able to provide that.

Port:  The port number is basically the channel used to communicate with the smtp server.  If you ever had a walkie-talkie, the port is similar to the channel.  If you and your partner don’t set your radios to the same channel, you won’t be able to talk to one another.  Most smtp servers set their port to 25.

The server requires a secure connection (SSL):  Check this box if that is the case.  In most cases it will probably not be checked.

SMTP Authentication:  Choose which authentication your smtp server requires.  In most cases, it will be ‘Basic Authentication’ which requires you to specify a userid and password.  You should be able to get this from your network administrator.

Once you finished entering all this information, click on ‘Save’.  Then, navigate back to the record you just created and continue with the rest of this article.

Message Template:

When you are ready to configure your message template, click on the ‘Message Template’ button.  You will get this window:

 

The window you see here is what you would get by default if you are setting up this checkbook for the first time.  You can change anything here to customize it to your organization.

E-mail Subject:  What you enter here will appear in the subject line on the e-mail to the vendor.

Message Template:  What appears in this box is what will be displayed to the vendor in the body of the e-mail message.  The fields surrounded with << and >> will be substituted with the actual values as it pertains to the specific vendor or transaction involved in the EFT transaction.  You can get fields from the Vendor Master, Vendor Address Master and Payment tables.  Just select the appropriate field from the list box on the left of the window and click on the ‘Select’ button.  The field will be placed where the cursor is currently located.

When you are done making your changes, click on the ‘Save’ button.

Attachment Template:

The next setup task is to define your attachment template.  Click on the ‘Attachment Template’ button on the ‘EFT E-mail Setup’ form.  You will get the following window:

 

In the Header and Detail Info sections, select all the fields you would like to appear in the header and detail portions of the attachment respectively.  If you wish to change the captions, just type over the default Caption names.  When complete, click OK.

Send Test E-mail:

Finally, you can test to ensure your settings were correct by sending a test e-mail.  Click on the ‘Send Test E-mail’ button of the ‘EFT E-mail Setup’ window.  You will get a dialog box asking for the e-mail address to where you wish to send this e-mail.  Enter the address and click ‘OK’.  If all goes well, you’ll get a message ‘The test message was sent successfully’.  You can then check your inbox to see what you got.  You should get an e-mail and attachment that looks similar to this:

Sending Remittances:

Now that you set up eftEmailer, you can start using it. 

First, you need to set the e-mail addresses for the vendors.  The eftEmailer picks up the e-mail from the address code set in the ‘Remit To’ field of the Vendor Maintenance window.  See the following windows:

 

First, select and generate your EFT payments as you would normally (refer to Dynamics GP documentation to learn how to do this).  After you have posted the transactions, navigate to Microsoft Dynamics GP -> Transactions -> Purchasing -> eftEmailer Remittances.  You should get a window that looks similar to after you select the appropriate checkbook id (except perhaps that you may have many more records):

 Select the records you wish to generate e-mails for by checking the ‘Select’ box.  Or select all of them with the ‘Mark-All’ button.  When ready, click ‘Send E-mails’.  If all went well, you’ll get a message to confirm that.  If something failed (perhaps because an e-mail address was not properly formatted), the culprit record will be indicated with a large ‘X’.  You can then zoom into the log by selecting the failed record and clicking on the ‘->’ arrow button next to ‘Document Number’ to see what might have failed.

If the problem was indeed that an e-mail address was incorrect, you can correct the e-mail on the vendor card and re-send the failed message through the ‘Generate EFT E-mails’ window.

The e-mails that the vendor will get will look something like this:

 

Conclusion:

Well, this concludes my article.  As you can see, it’s fairly easy to send vendor remittance e-mails with the eftEmailer.

I’m Dan Sionov, founder of accountingBot. I’ve been developing software for Dynamics GP since 1995 and haven’t grown tired of it. We make eftEmailer, a flexible yet simple to use application for Dynamics GP that allows you to send e-mails to vendors receiving EFT direct deposits.

A few years ago, Microsoft Dynamics offered a product called Continuum.  This product was essentially a wizard that automatically generated Visual Basic code that could control or react to events in GP.  Currently, Microsoft still offers Continuum but more for backward compatability.

Because of this feature, you can essentially control Dynamics GP with a simple program written in C# (or any .NET language for that matter).  The purpose of this article is to demonstrate how to do that.

The steps to create a simple interface with GP are very easy.  Here they are:

1. Create a new windows project in C#.
2. Add a new button to a basic windows form.
3. Add the Interop.Dynamics.dll  reference to your program.
4. Double click on the button and place the following code within the scope of the button1_click event:

try
{
   Dynamics.Application gpApp = (Dynamics.Application)Activator.CreateInstance(Type.GetTypeFromProgID("Dynamics.Application"));

   if (gpApp == null)
      MessageBox.Show("Failed to initialize gpApp");

   string code;
   string compile_err;

   code = "warning \"It Worked!!\";";

   gpApp.ExecuteSanscript(code, out compile_err);

}
catch
{
   MessageBox.Show("Failed to initialize gpApp");
}

 
Now, just log in to Dynamics GP and then run the C# code.  You should get a message from Dynamics if Customization Site Enabler is enabled in GP:


You'll notice that the code is very simple.  Really, all we're doing is instantiating the Dynamics.Application object and calling a method to send GP some 'sanscript' code.  For those that don't know, 'sanscript' is the language dialect that Dexterity uses and Dexterity is what Dynamics GP was written in.  So, you can essentially send Dynamics GP commands in sanscript to make it do what ever you want.

For more information on sanscript commands, I encourage you to look at the Dexterity documentation included in your Dynamics GP product CD.

I’m Dan Sionov, founder of accountingBot. I’ve been developing software for Dynamics GP since 1995 and haven’t grown tired of it. We make eftEmailer, a flexible yet simple to use application for Dynamics GP that allows you to send e-mails to vendors receiving EFT direct deposits.

Note to Reader

This document assumes an intermediate to advanced understanding of Great Plains VBA and Continuum.

Overview

This is a tip for Great Plains Continuum for VB developers.

Continuum is a good product that allows the VB developer to access the OLE layer of Great Plains to pass commands through the application runtime.  It gives you a virtually limitless amount of flexibility especially in working with multiple dictionaries.

Despite the power and flexibility that Continuum provides, it does have its nuisances.  One nuisance for which a solution will be provided is the difficulty with deployment.

Currently, to launch the VB ActiveX .EXE, the developer must include a statement in the DEX.INI file to point to the path of the .EXE.  Also, the Launcher.cnk file must be placed in the eEnterprise or Dynamics folder on each workstation.  Further, the developer must include code to trigger when Great Plains exits to end the .EXE correctly.  If this does not happen, the application will continue to run even after Great Plains has exited.  Finally, the developer must deploy all the DLL's that come with the VB program to each workstation. 

If it sounds like a lot of work, that's because it is.

The solution is to use VBA and place declarations in the code to get the same access to the OLE layer as VB with Continuum.  By doing this, the need to consider items mentioned above is eliminated.

There are two problems with the VBA approach however.  Modifier with VBA must be registered.  The other problem is that you cannot use the Continuum wizard.  That means all coding.  This however eliminates the need for purchasing Continuum.  The Customization Site Enabler must be purchased instead.

Code

Although the code seems complicated at first, it is actually quite simple.  It involves 4 sections as follows:

1.  A .bas module in the eEnterprise/Dynamics product containing the declarations to access the Application object of Great Plains.  This also includes the trigger registration.

2.  A class module in the eEnterprise/Dynamics product containing the code that will run when specific events are triggered (as registered in the .bas module).  This is referred to as the CallBack Class.

3.  Some code to be run when triggered by a VBA event to launch the main sub in the .bas module.

4.  A class module in the eEnterprise/Dynamics product containing the ParamHandler section.  This is optional and allows the developer to pass data back from a PassThrough Sanscript script.

.Bas Module

To keep consistent with the code that the Continuum Wizard produces with VB, the .bas module was named TemplateMain.  As a matter of fact, the code listed below was pretty much pasted from the code generated by the Continuum Wizard.

Most of it contains declarations of constants.  But it also creates the Application object of Great Plains.  It is through this object that all communication takes place.

Once all of these things have been created, the developer can than proceed to register triggers to fire.  To better understand Great Plains triggers, please refer to the Programmer's Guide and Sanscript Supplement that comes with Continuum for VB.

Option Explicit

Public eEnterpriseApp As Object
Public eEnterpriseCB As New eEnterpriseCB
Public ParamHandler As New ParamHandler
Public ValueCollection As Collection
Public Const TRIGGER_FOCUS_PRE = 0
Public Const TRIGGER_FOCUS_CHANGE = 1
Public Const TRIGGER_FOCUS_POST = 2
Public Const TRIGGER_FOCUS_PRINT = 3
Public Const TRIGGER_FOCUS_ACTIVATE = 4
Public Const TRIGGER_FOCUS_FILL = 5
Public Const TRIGGER_FOCUS_INSERT = 6
Public Const TRIGGER_FOCUS_DELETE = 7
Public Const TRIGGER_BEFORE_ORIGINAL = 1
Public Const TRIGGER_AFTER_ORIGINAL = 2
Public Const REG_ERR_NOERR = 0
Public Const REG_ERR_UNKNOWN = 1
Public Const REG_ERR_SCRIPT = 2
Public Const REG_ERR_FOCUS = 3
Public Const REG_ERR_OBJECT = 4
Public Const REG_MSG_UNKNOWN = "A problem of unknown origin  occured."
Public Const REG_MSG_SCRIPT = "The Callback routine was not found."
Public Const REG_MSG_FOCUS = "The event is not valid for this object."
Public Const REG_MSG_OBJECT = "The object does not exist."
Public Sub WatchRegistration(obj As String, proc As String)
CheckRegistration eEnterpriseApp.RegisterWatchTrigger(obj, eEnterpriseCB, proc), obj, "change"
End Sub
Public Sub FocusRegistration(obj As String, proc As String, focus As Integer, attach As Integer)
CheckRegistration eEnterpriseApp.RegisterFocusTrigger(obj, focus, attach, eEnterpriseCB, proc), obj, "focus"
End Sub
Public Sub FormRegistration(obj As String, proc As String, item As String, accel As String)
CheckRegistration eEnterpriseApp.RegisterFormTrigger(obj, item, accel, eEnterpriseCB, proc), obj, "form"
End Sub
Public Sub ShutdownRegistration(proc As String)
CheckRegistration eEnterpriseApp.RegisterShutdownRoutine(eEnterpriseCB, proc), "the eEnterprise application", "shutdown"
End Sub
Private Sub CheckRegistration(error As Integer, obj As String, reg As String)
If error <> REG_ERR_NOERR Then
MsgBox "Failed to register a " & reg & " notification on " & obj & ". " & RegistrationErrStr(error)
Set eEnterpriseApp = Nothing
End
End If
End Sub
Private Function RegistrationErrStr(error As Integer) As String
Select Case error
Case REG_ERR_NOERR
RegistrationErrStr = ""
Case REG_ERR_UNKNOWN
RegistrationErrStr = REG_MSG_UNKNOWN
Case REG_ERR_SCRIPT
RegistrationErrStr = REG_MSG_SCRIPT
Case REG_ERR_FOCUS
RegistrationErrStr = REG_MSG_FOCUS
Case REG_ERR_OBJECT
RegistrationErrStr = REG_MSG_OBJECT
End Select
End Function
Public Sub Main()
Dim szSavedProduct As String
Dim ErrVal As Integer, error As String
Dim strDatasource As String
Dim strUser As String
Dim strPassword As String
Dim strSQLServer As String

On Error Resume Next
Set eEnterpriseApp = CreateObject("Dynamics.Application")
ErrVal = eEnterpriseApp.setparamhandler(ParamHandler)
On Error GoTo 0
If eEnterpriseApp Is Nothing Then
MsgBox "Failed to create a Dynamics.Application object"
End
End If

eEnterpriseApp.CurrentProduct = "eEnterprise"

' Sample Triggers Registered
 ErrVal = DynamicsApp.RegisterDatabaseTrigger( _
 "table SVC_Serial_MSTR", "form SVC_Serial_Maintenance", 4, DynamicsCB, "SVCSerialAdd")
 If ErrVal <> 0 Then
 MsgBox "Unable to register the database notification."
 End If

 ErrVal = DynamicsApp.RegisterDatabaseTrigger( _
 "table SVC_Serial_MSTR", "form SVC_Serial_Maintenance", 8, DynamicsCB, "SVCSerialAdd")
 If ErrVal <> 0 Then
 MsgBox "Unable to register the database notification."
 End If

End Sub

Calling the Main Sub

The Main Sub is what is called to create the Application object and register appropriate triggers.

The Main Sub should be called when a user has logged into Great Plains.  A good place to add an event that does this is in the Toolbar Window on the Company Name field.  The developer should therefore add the Toolbar Window to the VBA IDE as well as the Company Name field.

This event would look something like this:

Private Sub CompanyName_Changed()
        TemplateMain.Main
End Sub

The CallBack Object

The developer should create a CallBack object if he or she wishes to capture events triggered by the Application object.

To keep consistent with the code that the Continuum Wizard produces, it can be named eEnterpriseCB or DynamicsCB as appropriate.

In this class, public methods will be created that will have the code for the events.  The triggers registered in the Main Sub will refer to the methods in this class.

The ParamHandler Object

The ParamHandler class should be created when the developer needs to pass data from Sansript code.  The Continuum documentation should be referred to for further information about the ParamHandler class.

Conclusion

There are numerous reasons why you would use the approach described above.  Deployment is one reason.  Another reason is that it gives a lot of power to VBA.  Passthrough Sanscript code will do what VBA cannot.

About the Author:

I’m Dan Sionov, founder of accountingBot. I’ve been developing software for Dynamics GP since 1995 and haven’t grown tired of it. We make eftEmailer, a flexible yet simple to use application for Dynamics GP that allows you to send e-mails to vendors receiving EFT direct deposits.