Why does my next document number change randomly?

January 31, 2009

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:

FixNextPONumber.sql


-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied
DECLARE @MaxPONumber INT;
DECLARE @AlphaPart VARCHAR(10);


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


WITH MyPOs (PONUMBER) AS (
SELECT PONUMBER FROM POP10100
UNION ALL
SELECT PONUMBER FROM POP30100
)
SELECT @MaxPONumber = MAX(dbo.fGetNumber(PONUMBER)) + 1
FROM MyPOs;


UPDATE POP40100 SET PONUMBER =
@AlphaPart + LEFT(REPLICATE('0', LEN(PONUMBER) - LEN(@AlphaPart)), LEN(PONUMBER) - LEN(@AlphaPart) - LEN(CONVERT(VARCHAR(20), @MaxPONumber))) +
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!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

UPDATES TO THIS ARTICLE:

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

FixNextGLNumber.sql


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


DECLARE @MaxJournal INT;


WITH MyJrnl(JRNENTRY) AS (
SELECT JRNENTRY FROM GL20000
UNION ALL
SELECT JRNENTRY FROM GL30000
)
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

FixNextPMVoucherNumber.sql


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

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

WITH MyVouchers(VCHRNMBR) AS (
SELECT VCHRNMBR FROM PM20000
UNION ALL
SELECT VCHRNMBR FROM PM30200
)
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);
Advertisements

Dynamics GP MVPs in the News

January 30, 2009


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 MSDynamicsWorld.com.

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!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


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

January 28, 2009

Problem

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“;
or
“The following SQL instruction produced an error: sql_statement

Followed by:

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

Cause

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.

Solution

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:

ForceProductIDUpgrade.SQL


-- Created by Mariano Gomez, MVP
-- Code is provided "as is". No warranties expressed or implied
USE DYNAMICS
GO
UPDATE DB_UPGRADE SET db_verMajor=0,
db_verMinor=0,
db_verBuild=0,
db_verOldMajor=0,
db_verOldMinor=0,
db_verOldBuild=0,
db_status=0
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!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Calling SQL Server stored procedures from Microsoft Dexterity

January 26, 2009

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.

dbo.uspGetServiceCallTotal


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

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

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

GRANT EXECUTE ON dbo.uspGetServiceCallTotal TO DYNGRP
GO

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;

try
call sproc "uspGetServiceCallTotal",
l_ReturnCode,
IN_callnmbr,
IN_service_type,
INOUT_serviceTotal;
catch [EXCEPTION_CLASS_DB_DEADLOCK]
if i <= n_loopcount then

timedelay = Timer_Sleep(200);
increment i;
restart try;
else
exit try;
end if;
else
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,
2,
call_total;
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!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Developing for Dynamics GP weekly summary

January 24, 2009

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!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


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

January 23, 2009


For all of you who follow me on MSDynamicsWorld.com, 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!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Changing SQL Server views for SmartList Builder smartlists

January 22, 2009

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?

Solution

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
GO

CREATE VIEW dbo.querySomething AS
SELECT JRNENTRY, TRXDATE, ACTINDX, DEBITAMT from GL20000
GO

GRANT SELECT ON dbo.querySomething TO DYNGRP
GO

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
SELECT JRNENTRY, TRXDATE, ACTINDX, DEBITAMT, CRDTAMNT from GL20000
GO

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!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/