Saturday, January 31, 2009

Why does my next document number change randomly?

This is a very common question among Dynamics GP users. In fact, it is a very common situation that can puzzle even seasoned consultants. So here is the mystery debunked.

Every Dynamics GP next document number in every module is generally gathered from a setup table. Usually, this table column is called Next Number. For example, GL will have a Next Journal Number; POP will have a Next PO Number and so on, depending on the module and the transaction. To focus on one thing and one thing only, I will use POP and the next purchase order document number.

In multi-user PO entry environments, for example, if user A is working on PO 10, user B is working PO 11, and user C is working on PO 12, but user B cancels, void, or delete their PO, when user A attempts to enter another PO, they will still end up with 13. It will appear to user A that the PO numbers are being skipped randomly. But this is clearly not the case we are discussing here, and rather the normal application's behavior.

In principle, the next purchase order document number should be the maximum numeric portion of the next purchase order number increased by 1. Initially, GP will read the value from the POP_Setup table, then it will attempt to reserve this value, this is, making sure the next PO number has not been used.

In it's attempt to reserve the value, GP will first search forward, up to MAX_DOCID_RETRIES (a constant in the application's source code with a value of 1000). If at the end of this attempt to reserve a number the system is still not successful, it will move backwards up to MAX_DOCID_RETRIES again.

This pehaps explains why certain users will see their PO Numbers jump from say 8,120 to 7,230. If the system finds and empty position, it "grabs" (as in reserves) that PO number, incrementing the next PO number to whatever value is next from the "empty" position it found.

Unfortunately, this new value could have already been used, hence it will repeat the check the next time someone enters a PO. This can certainly become an issue, because Dynamics GP cannot always find a value to reserve. This is why some users will experience a blank PO number field when the system exhausts it's attempts.

To correct this issue, it is necessary to find the "real" next PO number (or whatever document number for whatever transaction you are interested in).

1) Copy and execute the two SQL Server UDF from my article "How to split Dynamics GP's alphanumeric column values in SQL Server". These functions will be the starting point and will need to be executed against the company database.

2) Now, they can be used in the following statement, as follows:


-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied

SELECT @AlphaPart = dbo.fGetAlpha(PONUMBER)FROM POP40100;

SELECT @MaxPONumber = MAX(dbo.fGetNumber(PONUMBER)) + 1

@AlphaPart + LEFT(REPLICATE('0', LEN(PONUMBER) - LEN(@AlphaPart)), LEN(PONUMBER) - LEN(@AlphaPart) - LEN(CONVERT(VARCHAR(20), @MaxPONumber))) +
If you have noticed this issue in other modules with other transactions and you would like me to post a script to correct this issue, just post back with a request to do so, otherwise, stay tuned! I will update this article with scripts for other modules and other transactions.

Until next post!

Mariano Gomez, MIS, PMP
Maximum Global Business, LLC


02/02/2009 - Added code to fix the next journal entry number in GL.


-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied

DECLARE @MaxJournal INT;

SELECT @MaxJournal = MAX(JRNENTRY) + 1 FROM MyJrnl;

UPDATE GL40000 SET NJRNLENT = @MaxJournal;
The next journal entry number happens to be a numeric integer value, hence not requiring our two functions, simplifying the query a great deal.

07/23/2009 -- Added code to fix next voucher number in payables


-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied
DECLARE @MaxVoucherNumber INT;

SELECT @AlphaPart = dbo.fGetAlpha(NTVCHNUM)FROM PM40100;

SELECT @MaxVoucherNumber = MAX(dbo.fGetNumber(VCHRNMBR)) + 1
FROM MyVouchers;

UPDATE PM40100 SET NTVCHNUM = @AlphaPart + LEFT(REPLICATE('0', LEN(NTVCHNUM) - LEN(@AlphaPart)), LEN(NTVCHNUM) - LEN(@AlphaPart) - LEN(CONVERT(VARCHAR(20), @MaxVoucherNumber))) + CONVERT(VARCHAR(20), @MaxVoucherNumber);

Friday, January 30, 2009

Dynamics GP MVPs in the News

The Microsoft MVP Award Program has featured Dynamics GP MVPs Mark Polino over at DynamicAccounting and Mariano Gomez (that would be me!) on their blog page for their articles published on

On a personal note, this is truly encouraging and gratifying -- and I take the attribute to speak for Mark as well -- since Mark and I work very hard each day to bring you some of the best content you will ever find on all things Dynamics GP, that's our promise to you! However, doing it while wearing the Microsoft Most Valuable Professional (MVP) badge is even more special, because you can rest assured of the quality of the information you are receiving.

My thanks to the David Gumpert and Adam Berezin at MSDynamicsWorld for allowing us to publish on their site, but more so, thanks to all of you readers who made it possible in the first place.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Wednesday, January 28, 2009

"The following SQL statement produced an error" followed by "Invalid object name" when installing additional products


You attempt to install a Microsoft Dynamics GP add-on product and you receive one of the following messages:

"The following SQL statement produced an error: sql_statement";
"The following SQL instruction produced an error: sql_statement"

Followed by:

"ERROR [Microsoft][SQL Native Client][SQL Server]Invalid object name: object_name"
"ERROR [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name: object_name"


This error is due to an incorrect version number in your DYNAMICS.dbo.DB_Upgrade table for the add-on in question. Possibly, the add-on was installed and removed a few versions back and all SQL Server related objects dropped. When Dynamics Utilities attempts to upgrade the objects for the inexisting add-on, to your current product versions you will receive the errors indicated above.


If this is a new install of the add-on and it has not been used before, the best course of action would be to alter the version information within DB_UPGRADE table to force GP to reinstall the add-on on your current product version. Before doing this though, be sure to make a backup of the DYNAMICS database.

You can then update the corresponding add-on product information in the DB_UPGRADE table by running the following SQL statement:


-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied
WHERE PRODID = theProductID and db_name = 'YourDBName'

NOTE: Replace theProductID with the integer value corresponding to the add-on you are attempting to install, for example, for Fixed Assets use 309. Replace YourDBName with the database name of the company which you are trying to install the add-on.

You must also look for any tables in the DYNAMICS database corresponding to the product you are attempting to install and remove these by issuing a DROP TABLE tableName statement. Many add-ons store version information separately.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Monday, January 26, 2009

Calling SQL Server stored procedures from Microsoft Dexterity

This article will focus on techniques that can save your life when developing in Dexterity and especially when integrating with third party applications.

All too often, the only methods discussed for accessing third party data are the use of triggers and/or pass-through SQL in Dexterity. While these methods are very efficient, they rely heavily on the developer's ability to implement error handling at the application client side. Also, the amount of code needed perform these actions (to retrieve/save data) can be overwhealming at times.

One of my preferred methods is the use of SQL Server stored procedures. Stored procedure calls allow developers to separate things like data entry validation from actual business logic that deals with data saving and retrieval. It's fairly simple to fix a stored procedure that's already in a production environment versus the Dexterity code as a whole, since the latter involves redeploying chunk files. We all know too well how cumbersome this can become in large environments where downtime cannot be afforded.

The basics

In order to call stored procedures from Dexterity, they are 3 basic steps. In the process, we will consider the following example: say you are asked to create a form in which you will prompt the user to enter a service call number. In turn, you will retrieve the amount to be billed on the service call and estimate the gross revenue based on a formula that involves the duration of the service. With this in mind, let's take a look at how you can achieve this in 3 steps using Dexterity.

1) Write your stored procedure and grant access to the SQL Server DYNGRP role. This will make your stored procedure accessible from your Dexterity application, avoiding SQL Server permission issues. Our stored procedure will need to accept a service call number as a parameter, then return the total amount to be billed on the service call.


IF OBJECT_ID ( 'dbo.uspGetServiceCallTotal', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspGetServiceCallTotal

CREATE PROCEDURE dbo.uspGetServiceCallTotal
@IN_callnbr char(20) = NULL,
@IN_service_type smallint,
@INOUT_serviceTotal numeric(19,5) output

SELECT @INOUT_serviceTotal = TOTAL FROM dbo.SVC00200
WHERE CALLNBR = @IN_callnbr and SRVTYPE = @IN_service_type

GRANT EXECUTE ON dbo.uspGetServiceCallTotal TO DYNGRP

2) Create a prototype of the stored procedure in your Dexterity application. Since Dexterity cannot invoke a stored procedure directly, it is necessary to create a prototype of the stored procedure. Think of a prototype as a wrapper for your SQL Server stored procedure. This wrapper is Dexterity code that provides the compiler with a level of isolation from your SQL code.

Dex sproc uspGetServiceCallTotal

{ Created by Mariano Gomez, MVP.     }
{ global prototype procedure uspGetServiceCallTotal }
{ }
sproc returns long sproc_status;
in string IN_callnbr;
in integer IN_service_type;
inout currency INOUT_serviceTotal;

local long l_ReturnCode, timedelay;
local integer i, n_loopcount;

call sproc "uspGetServiceCallTotal",
if i <= n_loopcount then

timedelay = Timer_Sleep(200);
increment i;
restart try;
exit try;
end if;
exit try;
end try;

set sproc_status to l_ReturnCode;

NOTE: From the Dexterity help file, the try...end try statement is used to implement structured exception handling in Dexterity. The try...end try statement must contain at least one catch clause or an else clause. If none of the catch clauses catch the exception and there is no else clause, the exception is considered unhandled. A dialog box is automatically displayed describing the situation to the user. For information about structured exception handling and system exceptions see try...end try statement in the Dexterity help file.

NOTE: When creating the prototype global procedure in Dexterity, make sure to select the proper Series for the script. If your stored procedure will run in the DYNAMICS database, you will want to select System as the Series for the prototype procedure.

3) Now your calling code! Finally, you can have your own script that calls the Dexterity prototype procedure. This call is implemented like any call to any other global scripts.

Sample Dex calling script

{ this can be a call from a form script or field script within your code }
local long sproc_status;
local currency call_total;

call uspGetServiceCallTotal, sproc_status,
'Call Number' of window yourCustomWindow,
set 'Call Total' of window yourCustomWindow to call_total;

While the use of stored procedures is widely documented throughout the Dexterity manuals and help files, it is always useful to highlight the basic steps when creating and calling stored procedures. It does not take that much, and can be a good alternative to triggers and pass-through SQL whenever your code does not need to be interactive with the events in third party forms, windows, and tables.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Saturday, January 24, 2009

Developing for Dynamics GP weekly summary

Your Developing for Dynamics GP weekly summary is here! I have received possitive feedback from many of you on this weekly review because it presents some of the best articles available on the web on Dynamics GP development and integration techniques. This week's summary is no different! So let's get straight into it.

  1. Finding out how to call an existing report. In this article, David Musgrave reviews two techniques for calling and executing existing Dynamics GP reports from custom code. While I am not able to speak for Visual Studio Tools, these techniques are very useful when these calls are performed from Dexterity Sanscript. As an additional resource to his article, you may want to read my articles on Support Debugging Tool and DEX.INI.

  2. Sending Emails with Collaboration Data Objects (CDO) and Dexterity. It's always good when any of my articles get an entry on Developing for Dynamics GP. David takes a look at the technique I used on one of my projects to submit emails without the use of Outlook or Exchange.

  3. How can I identify the parameters of a procedure or function? This one is a wake up call for all Dexterity and why not, non-Dexterity developers out there. Microsoft offers a number of tools to trace existing code to establish how a Dex procedure or function call is made and parameters that are passed in in those calls. The key here: NO NEED FOR SOURCE CODE! In fact, the less you know about the source code, the better customizations you will develop, why? Because, not knowing what the original code does will force you to build more error handling and validation in your own code. Though, I must admit, working with source code has it's advantages. :-)

  4. Ever received an error "The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions"? David answers it here. Some of these pesky errors can be a bit difficult to troubleshoot. However, keep in mind that a DBMS error is a SQL Server related error condition in one of the many stored procedure operations executed in the backend, not a Dynamics GP error in itself. David shows a few troubleshooting techniques to get pass these issues.

  5. Chris Roehrich shows some nifty C# code for Passing data from a Business Portal Result Viewer Web Part to Dynamics GP Web Services. The specific example shows how you can remove a line item from a Sales Order using the UpdateSalesOrder method. The sales order number, line sequence number, and item number can be pulled off the Rich List Result Viewer web part and passed to the web services.
Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Friday, January 23, 2009

New article on MSDynamicsWorld: "Four Microsoft Dynamics GP Project Team Blunders and How I Become a Hero"

For all of you who follow me on, I have released a new article on Implementation Project Team blunders. Let's face it! Most of us Dynamics GP consultants become heroes with our customers by remediating failed implementations. This is how I progressed from being a developer to a consultant. In this article, I categorize project teams based on some companies' views of how a project should be run.

Until next post!

Mariano Gomez, MIS, PMP
Maximum Global Business, LLC

Thursday, January 22, 2009

Changing SQL Server views for SmartList Builder smartlists

One of the valuable tools that Microsoft has released is SmartList Builder for Dynamics GP. The concept is very simple: if the standard out-of-the-box Smartlist does not cut it for you, well, you build your own! Furthermore, SmartList Builder does allow for some nifty stuff, such as working with Microsoft SQL Server views and (custom) tables. Custom tables are not the focus of this article.

If you can't get your smartlist done with all the SmartList Builder query building capabilities, then create your own views of the data in Microsoft SQL Server, grant access to them in SQL Server, then, grant security to them in SLB, and use them.

But what if after deploying your smartlists, users request more columns of information that you did not include in your original SQL Server views, and now you need to make those changes and affect your previous work of art?


The solution may be easier than you think, but please do not delete your existing SmartList Builder and recreate all that work again! It's not necessary! These simple steps will illustrate how to change your SQL Server views and make these changes affect your existing SmartList Builder objects.

1) First, create your SQL Server view (If you already have a view skip to step 5), i.e:

/* Mariano Gomez, MVP
Sample code to illustrate changes in SQL Server view-based smartlist
IF OBJECT_ID('dbo.querySomething') IS NOT NULL
DROP VIEW dbo.querySomething

CREATE VIEW dbo.querySomething AS

GRANT SELECT ON dbo.querySomething TO DYNGRP

NOTE: This is a trivial example to illustrate the solution. Views should and must be used when you have exhausted all possibilities in SmartList Builder.

2) Grant security to the new object in SmartList Builder in GP. Go to Microsoft Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security

3) Create a smartlist with the new view, marked all columns in the Default list

4) Build the smartlist by going to Microsoft Dynamics GP > SmartList. Click on Yes to build SmartList.

5) Now, go back to SQL Server Management Studio and add a new column to the view, as follows:

/* Mariano Gomez, MVP
Sample code to illustrate changes in SQL Server view-based smartlist
ALTER view [dbo].[sampleGLforSLB] AS

NOTE: In this case I added the CRDTAMNT (credit amount) column.

6) Go back to SmartList Builder, open the smartlist and click on the Edit Selected Table button to open the Add SQL Table window.

All that's needed is to click on the Save button, nothing else! Surprisingly, this is not documented anywhere in the manuals. Now the CRDTAMNT column shows up in the fields list on the right pane. Then click on the Default check mark to add to the default list of columns to be displayed, or manually add the column in your smartlist after rebuilding with the changes.

7) Now, click on the Save button in the SmartList Builder window.

8) Open SmartList to build the modified smartlist with the new changes.

Hope these simple steps help you with modifying your views and getting your SmartList Builders to work smoothly with the changes.

Until next post!

Mariano Gomez, MIS, PMP
Maximum Global Business, LLC

Wednesday, January 21, 2009

New Articles on MSDynamicsWorld: Mark Polino on "Attending Convergence" and "Smartlist Sprawl"

Not even I can put out so many articles at once! Fellow MVP, Mark Polino has innundated the MSDynamicsWorld news outlet with two very compeling articles:

Taming the SmartList Sprawl. This is got to be the worst nightmare for any Dynamics GP systems administrator. Hundreds, sometimes thousands of custom smartlists that no one seems to know who created them, what they do (well this one can be obvious), how aften they are needed, etc. Mark has a few great tips on how to avoid the sprawl.

"Sometimes SmartList can be too much of a good thing"

Top 10 reasons to attend Convergence. If you are still trying to convince your boss as to why you need to go to Convergence, or if you are the boss and you are still trying to decide who you want to tag along with you, this article is for you. By the way, see you there.

"It's all about ROI"

Hope you enjoy these articles as much as I did, but make sure you leave your comments for Mark.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Tuesday, January 20, 2009

Last chance for Convergence 2009 early discount registration

Psssst! If you haven't registered for Convergence 2009, here is the deal: let your boss know (or if you are the boss) tomorrow is the last day for early registration -- extended from it's original due date of 11:59pm PST on Jan. 14, 2009. Early Registration discount date has been extended to January 21, 2009. Save $300 by registering early, secure your favorite hotels while they are still available and take advantage of better airline rates.

Here are the links again:

Register as an Existing Customer or Partner
Register as a Prospect Customer or Partner
Register a Group
Register with an RSVP code

Do I need to say it's in New Orleans?

Related Articles

- Convergence 2009 registrations now open - click here

Until next post!

Mariano Gomez, MIS, MCP, PMP, MVP
Maximum Global Business, LLC

Monday, January 19, 2009

Sending emails with Collaboration Data Objects (CDO) and Dexterity

One of the things I always tell other Microsoft Dynamics GP developers is not to discard the power of Dexterity. Not because there is .NET and Visual Studio Tools it means that you should abandon Dexterity as a development option (while I confess, I am in the process of increasing my knowledge of C# and so on).

Business Situation

A common request is the ability to submit emails from within your Dexterity-based application. However, emails are not always straight forward. In environments where Dynamics GP resides on a Citrix farm or Terminal Server, companies are reluctant to install Microsoft Office and/or setup email profiles for everyone. After all, that's what the Exchange Server is for. So, how can you get email messages sent from your application without SMTP, Exchange, or Outlook?


Collaboration Data Objects (CDO) has been around for quite sometime and is Microsoft's technology for building messaging or collaboration applications or adding these capabilities to existing applications, including Dexterity applications.

CDO is the basis of Microsoft Exchange, and has evolved from OLE Messaging and Active Messaging. Using Microsoft Dexterity with CDO, a developer can, for example, write a script that will exchange e-mail with users or with other Dexterity applications, collaborate in discussions on other applications, or allow employees to schedule meetings with multiple recipients, review existing appointments, schedule new events, and so forth.

Collaborative Data Objects are made available through two CDO libraries. These libraries let you address programmable messaging objects (including folders, messages, recipient addresses, attachments, and other messaging components).


To implement CDO in Dexterity, the following steps are required:

1) Open Resource Explorer. Click on Base and highlight the Library entry. Click on the New button to continue. This will open the COM Type Library Definition window.

2) Click on the ellipses button and locate and add the Microsoft CDO for Windows 2000 Library in the COM Type Libraries.

* Click on image to enlarge

3) Click on the Select button to continue, click on the OK button to close the COM Type Library Definition window.

4) Now, you will need to create a Data Type that will reference the library. In Resource Explorer, locate the Data Type folder under Base resources. Click on the New button in the Dexterity toolbar. I set up the following information for this example:

NOTE: To select the COM Object Type click on the ellipses button. This will open the Class Browser window. Double-click on the Message class. In reality, Dexterity is able to access other classes for that object without the need for other references, which simplifies things as we will see ahead.

5) With our new data type in place, we can now create a global script that will allow us to send emails from within GP using our CDO object.


{ global script : Send_Email

(C) 2009, created by Mariano Gomez, MVP
This script uses a non-SMTP method of sending emails to receipients

in string IN_From_Recipient;
in string IN_To_Recipient;
in string IN_Subject;
in text IN_Body;

local CDO.Message objEmail;
local reference configParams;

local string cdoSendUsingMethod;
local string cdoSMTPServer;
local string cdoSMTPServerPort;
local string smtpServer;

pragma (disable warning LiteralStringUsed);

cdoSendUsingMethod = "";
cdoSMTPServer = "";
cdoSMTPServerPort = "";
smtpServer = ""; {* this is the address of the smtp relay server }

pragma (enable warning LiteralStringUsed);

configParams = new CDO.Configuration();

{ Configure parameters }
configParams.Fields.Item[cdoSendUsingMethod].Value = CDO.cdoSendUsingPort;
configParams.Fields.Item[cdoSMTPServer].Value = smtpServer;
configParams.Fields.Item[cdoSMTPServerPort].Value = 25;

{ Prepare and send the email }

objEmail = new CDO.Message();

{ set up recipient and sender infomation }
objEmail.Configuration = configParams;
objEmail.From = IN_From_Recipient;
objEmail.To = IN_To_Recipient;
objEmail.Subject = IN_Subject;
objEmail.Textbody = IN_Body;

clear objEmail;
clear configParams;
A few things to highlight in the code:

a) Dexterity will not allow you to access the configuration parameters in the Message object directly, as you would in VB or VBA. This is, you will not be able to invoke objEmail.Configuration.Fields.Item directly. Hence, there will be a need to create a separate reference to the Configuration object.

b) While in VB you are able to use the parenthesis () as a way to access the different elements of the Configuration class, in Dexterity is necessary to use the square brackets along with a variable representing the element to be initialized. Also, it is necessary to specify the keyword ".Value" in the scope to avoid a Dexterity crash. VB does not require the specification of the ".Value" in the scope.

{ Configure parameters }
configParams.Fields.Item[cdoSendUsingMethod].Value = CDO.cdoSendUsingPort;
configParams.Fields.Item[cdoSMTPServer].Value = smtpServer;
configParams.Fields.Item[cdoSMTPServerPort].Value = 25;
Also, to access class constants, it will be necessary to add "CDO." in front of the constant name. I suspect this to be the issue with other objects as well.

c) To pass in the Configuration values to our Message class, it is enough to initialize objEmail.Configuration with the configuration parameter (configParams) reference.

While working with COM objects may not come intuitively in Dexterity -- we also don't have intellisense capabilities -- it's very true that you can still accomplish a great deal of the techniques that are natural in other environments like VB. After all COM is COM.


KB 286431 - How to send HTML formatted mail using CDO for Windows 2000 and a remote SMTP service.

Hey 1129 - Hey, Scripting Guy! - How Can I Attach a File to an Email Sent Using CDO?

MSDN 978698 - Collaboration Data Objects Roadmap

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Saturday, January 17, 2009

Developing for Dynamics GP Weekly Summary

After a long absence, my friend David Musgrave is slowly regaining his writing pulse. Please check out some of his cool articles this week, but first and foremost let him know what you think about them. I always encourage everyone to test and play with some of the code we provide in these articles. They are a good way to get acquainted with Microsoft Dynamics GP functionality. So let's get on with the articles:

1. Microsoft Dynamics GP 10.0 Service Packs, Hotfixes and Payroll Compliance/Year End Updates May Damage the Modified Reports and Forms. After a few of these errors circulating on the newsgroups, it has been confirmed by Microsoft development team. The payroll year end update will in fact damage your modified forms and reports. I have a theory on this one: since the YE update is also inclusive of service pack 3, it will be necessary to take all the precautions layed out when applying a service pack, that is, export ALL your customizations to a package file BEFORE applying any patches. Read more here.

2. Microsoft Dynamics™ GP 10.0 Bootcamp (Australia). Now, if you ever wanted to go to a Dynamics GP training class, won't it be a dream to do so in the land down under? Now, that's what I call a Dynamics GP training with style. Read more here.

3. eOne.Dynamics.GP.ExcelBuilder.Engine.dll Exception. If you have received this error after applying Microsoft Dynamics GP 10 Service Pack 2 and above and you are currently using SmartList Builder and Excel Builder, make sure you take a look at this article and the fix by reading more here.

4. Hybrid - Adding Named Printers control to Reports using VBA. Wondering how to get Named Printers to work with unsupported reports? David answers this one with a cool VBA customization. Best of all, you can download sample code! To play with this code, click here.

5. WorldMaps Tracking Added. As if my uber friend wasn't all that uber geek already, he adds a cool hits tracker on his blog with the cool WorldMaps beta product from Structure Too Big. I have to confess, I will be adding this one pretty soon to my blog! I find it fascinating to know where your readers are concentrated, which helps when developing content for the blog. For more info, click here.

6. Modifier - Reading and Writing Data with ADO Example. It does not matter how many of these we do, I always find Modifier with VBA fascinating and a very good alternative to Dexterity customizations when possible. Now, don't get me wrong. I love Dexterity, but I also love the portability offered by VBA customizations too. To read more, click here.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Thursday, January 15, 2009

New article on MSDynamicsWorld: 5 Tips for a Smooth Upgrade to Microsoft Dynamics GP 10

"Avoid the temptation to take shortcuts"

For those of you who follow me on MSDynamicsWorld, after a small writing sabatical I am back with a new article on upgrading to Dynamics GP 10.0. My key recommendations: work from the FP1 DVD, test several times, make sure you know your upgrade path, take advantage of the latest Microsoft OS and database technology, but foremost, work with your partner!

Until next post!

Mariano Gomez, MIS, PMP
Maximum Global Business, LLC

Tuesday, January 13, 2009

New MSDynamicsWorld article: Mark Polino on "Leveraging Dynamics GP expertise"

"Dynamics GP experience becomes a big plus when job hunting"

Fellow MVP Mark Polino, addresses, in his most recent MSDynamicsWorld article, something employers rarely sit down to consider: your Dynamics GP expertise! If there is one thing that will differentiate you among your peers during these tough times, when pink slips seem to be at the order of the day, that would have to be your ability to take a business issue and leverage your knowledge of Dynamics GP to solve a problem.

On the other hand, if your layoff is imminent, make sure you brush up your resume and highlight your Dynamics GP skills first and foremost. Believe it or not, it may well be the key to your next career move.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Monday, January 12, 2009

Microsoft Dynamics GP Automated Solutions

While I am a big SQL Server development fan and get a lot of support issues taken care of this way, it amazes me that many customers and partners are still not taking advantage of the Microsoft Dynamics GP Automated Solutions. This may be due to lack of understanding or simply a lack of knowledge of the existance of these tools.

What are Automated Solutions?

In Microsoft's own terms "Automated Solutions are used to simplify certain tasks which would otherwise require knowledge of the SQL Query Language and access to the SQL Query Tools or Support Administrator Console". In my own terms, Automated Solutions takes away the amount of SQL and Dynamics GP architecture you would need to know to resolve some very common everyday issues. The best part of it all, they are bundled with your Microsoft Dynamics GP enhancement plan and accessible via CustomerSource or PartnerSource.

*Click on image to enlarge

Automated Solutions exists for releases of Dynamics GP going back to 7.5 and are organized by product series. Some of the typical solutions include verifying whether the GL trial balance is out of balance, PO being edited by another user, SOP being edited by another user, among others.

v10 Automated Solutions - click here
v9 Automated Solutions - click here
v7.5 and v8 Automated Solutions - click here

Know that you know about Automated Solutions, be sure to check them before you run that SQL statement.


KB article 935384 - Automated solutions that are available for the Financials series in Microsoft Dynamics GP

KB article 935383 - Automated solutions are available for the Distribution series in Microsoft Dynamics GP

KB article 935382 - Automated solutions that are available for the System series in Microsoft Dynamics GP

Article: Stock Batches in Dynamics GP - by Doug Pitcher at Rose Business Solutions

Until next post!

Mariano Gomez, MIS, PMP
Maximum Global Business, LLC

Friday, January 9, 2009

Rebuilding Tax History

Business Situation

A few days ago, a user was facing an issue in which apparently, their tax history was not being recorded or was missing for a good number of transactions created in Sales Order Processing. In addition, the user wanted to use Dynamics GP's built-in tax reports as they did exactly what she needed.

Of course, the question came in: How can I rebuild my Tax History?


One thing I love about Dynamics GP is the fact that information created in the subsidiary modules, flow to other parts of the application tables when GP is instructed to do so. This instruction comes via the configuration options in the system.

In this particular case, when Sales Order Processing transactions are created and taxes are calculated for those documents, the tax detail information is stored in the Sales Tax Work and History table (dbo.SOP10105).

When invoices and returns are posted, and if history is being maintained for the tax details on these documents, the resulting tax records are then maintained in the Tax History table (dbo.TX30000) with tax information calculated from other subsidiaries.

With this in mind, the following T-SQL query will rebuild the missing sales tax information for all invoices that are not currently in the Tax History table.

/* 2009. Created by Mariano Gomez, MVP
This code is provided "AS IS" with no warranties expressed or implied

To be executed against your company database
FROM SOP10105 a

With minor tweaks, you should be able to get the above code to run for Return documents.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Thursday, January 8, 2009

How to schedule Dynamics GP to automatically log in and run an Integration Manager integration

One very common request by users and organizations using Microsoft Dynamics GP and Integration Manager is the ability to schedule an integration to run overnight. For small companies this can become a challenge, more so, when they lack the technical resources on staff or the basics on the Dynamics GP architecture.

The Basics

Any of the approaches used to schedule the integration will start from the same point: creating a login macro. The steps to create a login macro are widely documented in the Integration Manager Users Guide printable manual, in Chapter 17, under the section Recording the Login Macro, hence will not be the focus of this article. However, here is a sample of what the macro file (conveniently named LOGIN.MAC) should look like.


# Created by Mariano Gomez, MVP
# Sample Dynamics GP Login Macro.
# This sample code is provided "AS IS" and conferes no rights express or implied
# DEXVERSION=10.0.313.0 2 2
Logging file 'IMMACRO.LOG'
CheckActiveWin dictionary 'default' form Login window Login
MoveTo field Password
TypeTo field Password , 'p@ssw0rd'
MoveTo field 'OK Button'
ClickHit field 'OK Button'
NewActiveWin dictionary 'default' form sheLL window sheLL
NewActiveWin dictionary 'default' form 'Switch Company' window 'Switch Company'
ClickHit field '(L) Company Names' item 1 # 'Fabrikam, Inc.'
MoveTo field 'OK Button'
ClickHit field 'OK Button'
NewActiveWin dictionary 'default' form sheLL window sheLL
NewActiveWin dictionary 'default' form sheLL window sheLL
NewActiveWin dictionary 'default' form syReminders window syReminders
MoveTo field '(L) btnClose'
ClickHit field '(L) btnClose'
NewActiveWin dictionary 'default' form sheLL window sheLL
NewActiveWin dictionary 'Support Debugging Tool' form 'MBS_Debugger' window 'MBS_Debugger'
CloseWindow dictionary 'Support Debugging Tool' form 'MBS_Debugger' window 'MBS_Debugger'
NewActiveWin dictionary 'default' form sheLL window sheLL
NOTE: Your macro must close all windows opened upon startup of Dynamics GP for Integration Manager to proceed successfully. In addition, you will manually need to edit the file to include the line in red above to prevent displaying any message generated by the macro execution.

Macros and running Integration Manager from inside Dynamics GP

The macro system has a limitation when it comes to executing integrations from within the Dynamics GP application: macros cannot interact with Win32 applications menus and options. Hence, if you attempt to use the Run Integration menu option found under Microsoft Dynamics GP > Tools > Integrate > Run Integration, and while GP will launch the Microsoft.Dynamics.GP.IntegrationManager.IMRun.exe application (IMRUN for a short), the macro system will not be able to record the extra click on the integration you want to execute after the IMRUN application is launched.

*Click on image to enlarge

However, there is a workaround! The macro system can interact with the Dynamics GP Shortcut Navigation Bar. If you add a shortcut to the Dynamics GP Navigation Bar to include the IMRun application with the integration to be executed as a parameter, your macro can record the action when you click on the shortcut.

* Click on image to enlarge

Scheduling the Integration

I have worked with clients solving the issue of scheduling without resorting to add-ons or complex scheduling applications.

Batch Files and Windows Task Scheduler

Batch files can still prove very useful, even in today's GUI-based world. The batch file technique will require executing Dynamics GP with the log in macro as a parameter to the launching process. In addition, the login macro must contain additional actions to run the integration from the Navigation Bar, as previously described.

The following is a sample batch file named IMEXEC.BAT


:: Created by Mariano Gomez, MVP
:: Sample batch file to launch GP and run an integration
:: This sample code is provided "AS IS" and conferes no rights express or implied
C:\Program Files\Microsoft Dynamics\GP\DYNAMICS.EXE C:\Program Files\Microsoft Dynamics\GP\DYNAMICS.SET C:\Program Files\Microsoft Dynamics\GP\DATA\LOGIN.MAC
Once the batch has been created, we can use Microsoft Windows Task Scheduler to complete the scheduling process. The following Microsoft Knowledgebase and TechNet articles will help with scheduling the batch file based on your operating system:

KB article 308569 - How to Schedule a Task in Windows XP
TechNet article 906020 - Windows Vista Task Scheduler

I will address running integrations as separate tasks in a future installment. This will allow for discussion of more advance scheduling techniques.

Other Resources

Windows Networking - Working with the Windows Server 2008 Task Scheduler (Part 1).
Windows Networking - Working with the Windows Server 2008 Task Scheduler (Part 2).
Dynamics GP Blogster- Macros in Micrsoft Dynamics GP v 10.0. Click here.

Until next post!

Mariano Gomez, MIS, PMP
Maximum Global Business, LLC

Frank Hamelly on Entering Vendor Prepayments

Fellow MVP Frank Hamelly addresses a concrete day to day business issue: how to enter prepayments for vendors in GP. While it's very simple to record prepayments for customers in the Sales series, it is not always as straight forward in the Purchasing series. Take a look at Frank's article and let him know what you think.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Tuesday, January 6, 2009

New article on MSDynamicsWorld: Mark Polino on "Five Habits to Break in the New Year"

Stop making more work for yourself!

Fellow MVP Mark Polino opens the year at with an article I believe you should read very carefully. Since we all make New Year's resolutions, it should come as no surprise that these resolutions should extend to our use of business solutions too. Mark's 5 resolutions are: 1) take SmartList for what it is, not for an Excel file generator, 2) get over the darn palette windows and menus, 3) how many GP clients do you need to install to prove you know GP? Consider Terminal Server instead, 4) Stop forcing your receivables modules to do project accounting, get the modules you actually need, and go get some training for God's sake!

Until next post!

Mariano Gomez, MVP, MCP
Maximum Global Business, LLC

How to search for a text within a SQL Server trigger, stored procedure, or UDF

Just recently I had been helping my friend Cal at EMC to resolve an issue with the Professional Services Tools Library (PSTL) Item Combiner utility. In a previous life, his company had installed the Manufacturing series and decided not to use it. They upgraded to Dynamics GP 10.0 and decided they wanted to take advantage of PSTL's Item Combiner to do some cleanup work on their product master.

Not so quick! The Item Combiner failed with the message:

'[Microsoft][SQL Native Client][SQL Server]Invalid object name 'MOP1014'

We began an extensive troubleshooting process to find the culprit of the problem with no success, that is, until the problem was narrowed down to the following question: How to find a text within a SQL Server trigger, stored procedure, or UDF module.

The following T-SQL query accomplishes just that:

-- run against DYNAMICS and company databases

FROM sys.sql_modules
WHERE [definition] LIKE '%MOP1024%';

It turns out that SQL Server does have this information available when the trigger, stored procedure, or UDF object is not created with encryption.

My thanks go out to SQL Server MVP Aaron Bertrand for his contribution to this post.


- Finding Columns in Tables - Click here

Until next post!

Mariano Gomez, MVP, MCP
Maximum Global Business, LLC

Monday, January 5, 2009

Round 1 - 2009 Payroll Tax Updates now available

Microsoft has made Round 1 of the 2009 Payroll Tax Updates now available. Round 1 updates a number of state and federal tax tables, per the document:

- California
- Maine
- Michigan
- Minnesota
- New Mexico
- Oklahoma
- Fica/Social Security Wage Limit
- Personal Exemption amounts
- Earned Income Credit

In past days, I reported the EIC value being incorrect for 2009 and apparently it has been taken care of in Round 1.

Tax updates can be applied/re-applied by following these simple instructions:

Dynamics GP 10
Go to Microsoft Dynamics GP > Maintenance > U.S. Payroll Updates > Check for Tax Updates

Dynamics GP 9.0
Go to Help > U.S. Payroll Updates > Check for Tax Updates

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

Saturday, January 3, 2009

How to roll down SOP's Requested Ship Date changes to line items and purchase orders - Part II

In the previous installment, you saw how to make changes to the Requested Ship Date roll down to the line items and linked purchase order documents via a SQL Server trigger.

This article will now show a VBA method combined with a SQL Server stored procedure call. The advantage of using VBA is to allow more interaction with the end-user and control the flow a bit more.

The following steps will get us there:

1) Add the Sales Transaction Entry window to VBA. Open the Sales Transaction Entry window and press CTRL + F11 on your keyboard.

2) Add the Document Type drop-down list, Document Number, and Customer ID fields to VBA. Press Shift + F11 on your keyboard to activate the visual cue. Once active, click on both fields. Press Shift + F11 to deactivate the visual cue.

3) Add the Sales Date Entry window to VBA. Click on the expansion button next to the Date field to open the Sales Date Entry window and press CTRL + F11 on your keyboard.

4) Add the Requested Ship Date field to VBA. Press Shift + F11 on your keyboard to activate the visual cue. Once active, click on the Requested Ship Date field. Press Shift + F11 to deactivate the visual cue.

5) Open the Visual Basic Editor. Press ALT + F11 on your keyboard. Locate the Microsoft Dynamics GP project in the Project Explorer window to the left of the screen. Expand to locate the SalesDateEntry (Window) object. Double-click to open the code editor.

6) Copy and paste the following code in the Editor:

' Created by Mariano Gomez
' Maximum Global Business, LLC -
' (C) 2008
' This code is provided "AS IS" with no warranties expressed or implied
Option Explicit

Private Sub RequestedShipDate_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    Dim result As Integer
    Dim SOPNumber As String
    Dim SOPType As Integer

    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRS As New ADODB.Recordset

    If Not (SalesTransactionEntry.DocumentNo.Empty) And Not (SalesTransactionEntry.CustomerID.Empty) Then
        ' Check to see if there are any line items entered for this transaction, otherwise

        Set objConn = UserInfoGet.CreateADOConnection()
        With objConn
           .CursorLocation = adUseClient
           .DefaultDatabase = UserInfoGet.IntercompanyID
        End With
        With objCmd
            .ActiveConnection = objConn
            .CommandType = adCmdText
            .CommandText = "SELECT * FROM SOP10100 WHERE SOPNUMBE = '" & SOPNumber & "' AND SOPTYPE = " & CStr(SOPType)
            Set objRS = .Execute
        End With
        If objRS.RecordCount > 0 Then
            ' We have some records to process in the SOP_Line_WORK
            result = MsgBox("Do you want to roll down changes to line items and linked POs?", vbYesNo, "Roll down Requested Ship Date")
            If result = vbYes Then
                SOPNumber = SalesTransactionEntry.DocumentNo
                ' Since the SOP Types in the DDL are not equivalent to the actual SOP Types stored in SOP10100
                ' we need to switch them around a bit
                Select Case SalesTransactionEntry.TypeTypeID
                    Case 1
                        SOPType = 1  'Quote
                    Case 2
                        SOPType = 2  'Order
                    Case 4
                        SOPType = 3  'Invoice
                    Case 5
                        SOPType = 4  'Return
                    Case 6
                        SOPType = 5  'Back Order
                    Case 3
                        SOPType = 6  'Fulfillment Order
                End Select
                With objCmd
                    .CommandType = adCmdText
                    .CommandText = "EXEC usp_SOPRollDownReqShipDate '" & SOPNumber & "', " & CStr(SOPType) & ", '" & CStr(RequestedShipDate) & "'"
                    .Execute , , adExecuteNoRecords
                End With
                Set objCmd = Nothing
                ' close and destroy Recordset object
                Set objRS = Nothing
                ' close and destroy connection object
                Set objConn = Nothing
             End If

        End If
    End If
End Sub

7) Go to the Debug menu and select Compile Microsoft Dynamics GP.

8) The above code calls the dbo.usp_SOPRollDownReqShipDate stored procedure which must be created for each company database. Open SQL Server Management Studio and create a New Query. Copy and paste the following code to your query window:

' Created by Mariano Gomez
' Maximum Global Business, LLC -
' (C) 2008
' This code is provided "AS IS" with no warranties expressed or implied
USE [YourCompanyDB]

/****** Object:  StoredProcedure [dbo].[usp_SOPRollDownReqShipDate]    Script Date: 12/22/2008 19:34:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SOPRollDownReqShipDate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SOPRollDownReqShipDate]

/****** Object:  StoredProcedure [dbo].[usp_SOPRollDownReqShipDate]    Script Date: 12/22/2008 19:34:32 ******/


CREATE PROCEDURE [dbo].[usp_SOPRollDownReqShipDate]
 @SOPNumber char(31),
 @SOPType smallint,
 @ReqShipDate datetime

IF ISNULL(@SOPNumber, '') = ''


  -- roll down to all items
  UPDATE SOP10200 WITH (ROWLOCK) SET ReqShipDate = @ReqShipDate

  -- updated the Required Date column for linked POs

  -- updated the Required Date column for the line items on linked POs

 IF @@ERROR = 0


GRANT EXECUTE ON [dbo].[usp_SOPRollDownReqShipDate] TO DYNGRP

9) Execute the query against each company database to be able to run the VBA customization across more than one company.

Testing the Customization

To test the customization, select an existing sales order. Change the Requested Ship Date. Close the sales order saving any changes. Reopen the order and all line items should now reflect the correct requested ship date.

You can choose to select a sales order that has a linked purchase order to test changes to the Required Date on the purchase order and purchase order line items.


You can download the package files and stored procedure for this project below

Click here to download the usp_SOPRollDownReqShipDate stored procedure SQL file.
Click here to download the v10_SOP_Transaction_Entry package file.
Click here to download the v10_SOP_Transaction_Entry_VBA_References package file.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC

08/08/2013: Changed If @@Error <> 0 to If @@Error = 0 in stored procedure code. In its previous implementation, the stored procedure would always rollback the changes if there was no error causing requested ship date not to roll down.

Friday, January 2, 2009

Incorrect EIC value in 2009 United States Payroll Tax Update

If you have applied 2009 Payroll Tax Update to your Microsoft Dynamics GP system, you will need to correct the Earned Income Credit amount in the UPR41300 tax table from its old value of $1,750 for 2008, to $1,826 for 2009. However, the amounts seem to be correct for the married and single filing statuses in the UPR41302 tax table.

To fix this issue, run the following script against your DYNAMICS database:

-- Run againt your Dynamics database

Thanks to Bob Harrison at The Lighthouse Neurological Rehabilitation Center ( for this pointer.

Related Articles

2009 Payroll Tax Update for Microsoft Dynamics GP 8.0 - Click here
2008 Year End Update and Microsoft Dynamics GP 8.0 - Click here

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Congratulations to Victoria Yudin, MVP!

Kudos to Ms. Victoria Yudin for her 5th straight year of being selected as one of Microsoft's Most Valuable Professionals for the Dynamics GP product line. It's safe to say that the Microsoft Dynamics Community won't be what it is today without the contributions of Ms. Yudin, who as of the date of this post has racked up 2933 responses in the microsoft.public.greatplains newsgroup.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC