Friday, May 29, 2009

MVP Frank Hamelly on Account Rollups and Organizing SmartLists

After a long writing hiatus, MVP Frank Hamelly on his gp2themax blog explains how to setup the often overlooked Accounts Rollup feature which he describes as "not as tasty as fruit rollups, but equally satisfying".

Frank also explains how to use Microsoft Dynamics GP's Navigation Pane to organize SmartLists for the end-users. His narrative is based on a requirement posed by the client at an actual project we both had the opportunity to work and collaborate on.

Please be sure to read Frank's articles as you will find a lot of interesting things from his personal experience.

Until next post!

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

The inner workings of Microsoft Dynamics GP on Microsoft SQL Server

Unless you have been working with Dynamics GP from the days of Ctree and Btrieve, it is quite difficult to comprehend why Dynamics GP seems to behave (as in act up) in certain ways on SQL Server -- how would I say this... not quite like your other Windows applications that run on SQL Server.

To understand some of these behavioral issues, David Musgrave brings a two-part series on Understanding how Microsoft Dynamics GP works with Microsoft SQL Server -- if it was my article I would have labeled it Dynamics GP technological idiosyncrasies, but then again, I did not write it. :-)

Ever wonder why the cryptic table names and columns? A lot of it has to do with the origins of the application and the multiple operating systems and ISAM platforms supported back in the 80's and 90's. Know your Microsoft Dynamics GP history... in the early days, long before Microsoft SQL Server, Dynamics GP ran on the Mac OS platform and supported Ctree and Btrieve as file server platforms. I guess at the end of the day, the development team figured it would be a daunting effort to make those tables and columns names meaningful for SQL Server.

Until next post!

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

Thursday, May 28, 2009

Moving data between Microsoft Dynamics GP companies

I recently came across a question as to how to move data across Dynamics GP companies. It then became almost an instant challenge to find all the possible answers to this question and while I don't claim to have them all, I will point out as many methods (and resources) I could think of. If you have used other methods I would certainly value your input to this post.

So here we go:

Method 1. SQL Server Backup/Restore.
This method assumes you will be creating an exact copy of the production data for either test purposes or as a simple fall back method for recovery purposes. See KB article 871973 for more information on how to create a mirrored copy of your GP data.

Method 2. Support Debugging Tool
You can use Support Debugging Tool's XML Export and XML Import features to select specific tables to transfer. If transfering master tables, KB article 872709 lists the tables you will need to select for the transfer.

Method 3. Integration Manager
Integration Manager is still one of the best methods to transfer data between companies, especially at the end of an implementation, when you need to import master records and beginning balances into a production company that were previously recorded in a test company. Integration Manager can be found on your Microsoft Dynamics GP installation CDs. The following are links to the latest Integration Manager updates:

Microsoft Dynamics GP v9 - Click here
Microsoft Dynamics GP v10 - Click here

Method 4. SnapShot for Microsoft Dynamics GP
Written by David Musgrave, SnapShot works by copying the contents of selected tables to Ctree files in a separate folder, thus creating a SnapShot of the data. This separate folder can then be copied to a target system and the data inserted back into the actual tables. For more information on SnapShot and to download the product, visit the Developing for Dynamics GP website here.

Method 5. Company Setup Automated Solution
If the purpose is to transfer only setup information, then you will want to try the automated solution offered by Microsoft. The automated solution can be found here (CustomerSource/PartnerSource access required). For more information on Automated Solutions check out my previous article here.

Method 6. Table Import
Table Import is the forgotten tool. As many new and more robust tools are developed, this tool seems to drop to the bottom of the memory lane. Don't forget that Table Import can bring data into specific GP tables that are not supported by the tools. Click here for more information about Table Import.

Hope you find these methods useful and contribute to the article with other methods you have used.

Until next post!

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

Thursday, May 21, 2009

Microsoft SQL Server versions and editions

The purpose of this article is to describe how to identify your current Microsoft SQL Server version number and the corresponding product or service pack level. It also describes how to identify the specific edition of SQL Server you are using in your environment.

How to determine which version of SQL Server 2008 is running

To determine which version of Microsoft SQL Server 2008 is running, connect to SQL Server 2008 by using SQL Server Management Studio, and then run the following Transact-SQL statement.


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:
The product version (for example, 10.0.1600.22)
The product level (for example, RTM)
The edition (for example, Enterprise)
For example, the results resemble the following.

10.0.1600.22 RTM Enterprise Edition

The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2008:


Product Version File Version (SQLSERVR.EXE)

RTM 10.0.1600.22 2007.100.1600.0
SQL Server 2008 Service Pack 1 10.0.2531.0 2007.100.2531.0

How to determine which version of SQL Server 2005 is running

To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement.


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:
The product version (for example, 9.00.1399.06)
The product level (for example, RTM)
The edition (for example, Enterprise Edition)

For example, the results resemble the following.

9.00.1399.06 RTM Enterprise Edition

The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2005:


Product Version File Version (SQLSERVR.EXE)

RTM 9.00.1399 2005.90.1399
SQL Server 2005 Service Pack 1 9.00.2047 2005.90.2047
SQL Server 2005 Service Pack 2 9.00.3042 2005.90.3042
SQL Server 2005 Service Pack 3 9.00.4035 2005.90.4035

How to determine which version of SQL Server 2000 is running

To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code.


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:

The product version (for example, 8.00.534)
The product level (for example, "RTM" or "SP2")
The edition (for example, "Standard Edition").

For example, the results resemble the following:

8.00.534 SP2 Standard Edition

The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2000:


Product Version File Version (SQLSERVR.EXE)

RTM 8.00.194 2000.80.194.0
SQL Server 2000 SP1 8.00.384 2000.80.384.0
SQL Server 2000 SP2 8.00.534 2000.80.534.0
SQL Server 2000 SP3 8.00.760 2000.80.760.0
SQL Server 2000 SP3a 8.00.760 2000.80.760.0
SQL Server 2000 SP4 8.00.2039 2000.8.00.2039

For releases of SQL Server prior to SQL Server 2000, check Microsoft Support KB article 31185. Keep in mind also there were a number of Cumulative Updates for each version, which are not listed above. For a detail list of Cumulative Updates version information check SQL Team's article on SQL Server Versions.

Until next post!

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

Wednesday, May 20, 2009

Securing VBA projects

It seems that nowadays we can add security to pretty much every aspect of the Microsoft Dynamics GP application, but when it comes to VBA projects things are not too clear for many developers and Dynamics GP systems administrators.

Follow these simple steps to secure your VBA project.

1. Open the Visual Basic Editor. Go to Microsoft Dynamics GP > Tools > Customize > Visual Basic Editor, or press ALT+F11 from your keyboard.

2. Open the project's Properties window. Right-click on the project within Project Explorer and select the project Properties option. You can also access the project Properties window by highlighting the project and using the Tools menu.




3. Click on the Protection tab, then click on the Lock project for viewing checkmark.




4. Enter a password to prevent unauthorized access, then confirm the password entered.



5. Press Ok to apply the settings.

6. Save your VBA project and exit Dynamics GP. When you access the application next time and attempt to access your VBA project you will be prompted to enter a password to display it's content.



If you have multiple projects, you will need to apply security individually to each by following the above outlined steps. Hopefully you will use the same password across to make things easier :-)

Until next post!

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

Monday, May 18, 2009

The wonders of CTRL+Q: saving and printing SOP documents in one step

How many times have you wished you could save and print a sales order, a fullfillment order, or an invoice document, with their corresponding packing slips and picking tickets all in one step? The SOP Quick Print feature offers just that! Written by my friend David Musgrave, and a standard part of Microsoft Dynamics GP, it's one of the most overlooked (or perhaps, unknown) yet, one of the most requested options by users working with SOP.

David says "Just set it up with the Keep Current Document Displayed option unchecked.Then instead of saving and then printing. Just use Ctrl-Q to quick Print.That will then save the document and print it.That's what I wrote the feature for."

To access the setup window, click on the Options menu on the Sales Transaction Entry screen, then select Quick Print Setup. You can configure additional options even for packing slips and picking tickets, along with report output settings. You can also choose to let the document remain on the screen even after printed.



Once configured, you can then begin to enter your documents or select previously entered ones, then hit CTRL+Q from your keyboard. That's it! Your document is saved then printed, all in one step! One of the most important benefits of this feature is, you don't need to create your own customization.

Have a happy CTRL+Q with your SOP documents!

Until next post!

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

Friday, May 15, 2009

Sales Order Processing posting statuses

Well, everyday I learn something new. My friend and fellow MVP Victoria Yudin had been wondering what could be the possible values stored in the PSTGSTUS ('Posting Status') column of the tables dbo.SOP10100 (technical name: SOP_HDR_WORK) and dbo.SOP30200 (technical name: SOP_HDR_HIST) statuses as a result of a posting operation in Sales Order Processing.

In fact, just recently on the Dynamics GP Newsgroup, someone reported seing a status 508 in the SOP30200 table. The complete list was provided by a Microsoft support engineer on the Partners forum -- take note as they are not documented in the SDK.

Transcript

All transactions in SOP10100 that have not been posted yet should have a PSTGSTUS value of 0 (unposted). This value of this field will change to 2 (posted) when posted and the record will move to SOP30200. With this in mind, all transactions in SOP10100 should have PSTGSTUS value of 0 and all transactions in SOP30200 should have PSTGSTUS value of 2 (posted). Any other values in either table would indicate that there was a posting interruption.

I have found invalid values of 12, 14, 508, etc. These values may have been assigned to the record in different stages of the posting process. Meaning, the posting process did not complete and the transactions need to be recovered in the Batch Recovery window.

Example:

1. Transaction 1 in SOP10100 (Work) with PSTGSTUS = 0.
2. Transaction 1 is posted:

a. PSTGSTUS is assigned the value of 7 while GP is checking for duplicate Document Numbers.
b. PSTGSTUS is assigned the value of 12 while GP is verifying the accuracy of the amounts between the detail and header records.
c. PSTGSTUS is assigned the value of 14 while GP is calculating that the total detail amounts match the header amount.
d. PSTGSTUS is assigned the value of 508 while GP is is in the process of transferring the record from Work (SOP10100) to History (SOP30200).
e. PSTGSTUS is assigned the value of 600 while GP is validating if the detail and header records match in the History tables (SOP30200 and SOP30300).
f. PSTGSTUS is assigned the value of 2 upon the end of the posting process.

In this example, if the posting process was interrupted after step a, then the record will still be in SOP10100 with a PSTGSTUS = 7. Or, if the posting process was interrupted after step d, then the transaction may still be in SOP10100 with a PSTGSTUS = 508.

The example above is not the exact posting process in GP. This is just a way of showing how a record in SOP10100 can have a PSTGSTUS value other than 0 and 2.

I must personally add that a transaction will only make it to SOP30200 with statuses of 2 or 3, as the transcript indicates, the other status are more used as a workflow within the posting process than really a status.

Until next post!

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

Thursday, May 14, 2009

How to import the Vendor 1099 Box with Integration Manager

Business Situation

A few days aback I addressed an issue where the user needed to set the 1099 Box on the vendor import with Integration Manager. To my surprise, while there is a field to map the 1099 Type, there is no 1099 Box field in the Options collection in the Vendor import in IM. To make matters a bit more interesting, the user needed to have the 1099 Box filled based on some settings in the source system.



Solution

The 1099 Box is not accessible via Integration Manager, however we can create a small SQL Server stored procedure in the company database that will accept the vendor ID and the 1099 Box as parameters. The stored procedure in turn will update the vendor record with the value submitted.

dbo.uspUpdateVendor1099Box


-- Created by Mariano Gomez, MVP

IF OBJECT_ID ( N'dbo.uspUpdateVendor1099Box', N'P' ) IS NOT NULL
DROP PROCEDURE uspUpdateVendor1099Box;
GO

CREATE PROCEDURE dbo.uspUpdateVendor1099Box
@VENDORID CHAR(21) = NULL
,@TEN99BOXNUMBER SMALLINT = NULL
,@RET_VAL INT OUTPUT
AS

SET @RET_VAL = 0

IF (@VENDORID IS NULL) OR (@TEN99BOXNUMBER IS NULL)
BEGIN
SELECT @RET_VAL = 0;
RETURN
END

BEGIN TRY
UPDATE PM00200 SET TEN99BOXNUMBER = @TEN99BOXNUMBER WHERE VENDORID =
@VENDORID
END TRY
BEGIN CATCH
SELECT @RET_VAL = ERROR_NUMBER()
END CATCH;
GO

GRANT EXECUTE ON dbo.uspUpdateVendor1099Box TO DYNGRP
GO


Now, in the Vendor integration, we can open the After Document event script and add the following VBScript code:

After Document


' Created by Mariano Gomez, MVP
Const adUseClient = 3
Const adCmdStoredProc = 4
Const adSmallInt = 2
Const adInteger = 3
Const adChar = 129
Const adParamInput = 1
Const adExecuteNoRecords = 128

Dim oCn
Dim oCmd
Dim pVendorID, pTen99BoxNumber, pRetVal

set oCn = CreateObject("ADODB.Connection")
oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient
GPConnection.Open(oCn)

With oCmd
.ActiveConnection = oCn
.CommandType = adCmdStoredProc or adExecuteNoRecords
.CommandText = "dbo.uspUpdateVendor1099Box"


'Set parameters
set pVendorID = .CreateParameter("VENDORID", adChar, adParamInput, 21, _
GetVariable("gVendorID"))
.Parameter.Append pVendorID

set pTen99BoxNumber = .CreateParameter("TEN99BOXNUMBER", adSmallInt, _
adParamInput, 8, GetVariable("gBoxNumber"))
.Parameter.Append pTen99BoxNumber

set pRetVal = .CreateParameter("RET_VAL", adInteger, adParamOutput)
.Parameter.Append pRetVal

.Execute
End With

If pRetVal.Value <> 0 Then
' Error handling here, you can choose to write to IM's log
End If

Since I am making use of global variables, gVendorID and gBoxNumber, we will need to capture both of these values in the vendor ID field script -- we will no longer map the vendor ID since it's necessary to use a field script.

Vendor ID field script


' Created by Mariano Gomez, MVP
SetVariable "gVendorID", SourceFields("someSource.Vendor")
SetVariable "gBoxNumber", SourceFields("someSource.Ten99Box")
CurrentField.Value = SourceFields("someSource.Vendor")

This conclude our integration! Don't get discourage if you don't see a specific field mapping in IM. With little creativity and the right mindset you can always come around with a solution that is worth showcasing.

Until next post!

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

Tuesday, May 12, 2009

Visit the new Dynamics Partner forums

Global English Partner Online Technical Communities upgraded to the forum platform since Apr 27, 2009, at 9:00am PST. I have to say I have been quite excited lately working on the new Dynamics GP (Partners) forum platform. Forums defer from Newsgroups in that these are moderated by Microsoft employees and are exclusive to Microsoft partners who are Registered members, Certified members, or Gold Certified members -- this is, they are not public.





The following are links to the different partner forums:

Dynamics GP
Dynamics AX
Dynamics NAV
Dynamics SL
Dynamics RMS
Dynamics Sure Step

Until next post!

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

Monday, May 11, 2009

Is there a maximum number of lines that can be inserted in any given scrolling window?

Folks, this one can rather come across as a brain teaser or a catchy certification question, but don't be surprised by how often I get asked during a demo and how often I -- and many others -- have automatically provided the unequivocal answer "Unlimited!". The question comes in various flavors, for example:

  • How many distributions can I add to a journal entry?
  • How many line items can I add to a Sales document?
  • How many line items can I add on a Purchase Order?
Unlimited? The truth is, there is a limit to everything, whether the contraints are imposed by physical storage capacity or by a data type definition, the fact is, it's nonetheless a limit.

So let's take a look.

The Line Sequence Number field in Dynamics GP is generally defined as an integer value. According to Microsoft SQL Server Books Online, an integer requires 4 bytes of storage, which means it can any number between -2^31 and 2^31 or -2,147,483,647 and 2,147,483,647. Now consider this, the first line sequence number in a scrolling window is generally initialized with a value of 16,384 or 2^14, so mathematically speaking, a user will be able to add 131,072 lines or 2^17 before they hit an error and have to resort to inserting distribution lines in between the previous 131,072 ones.

I don't know about you, but 131,072 lines in a scrolling window sound like quite a bit of lines -- but certainly not unlimited -- before you even begin to consider inserting lines in between, but you never know when you will come across that one customer who will push the system to it's limit.

Related Articles:



Until next post!

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

Wednesday, May 6, 2009

How to create a smartlist to show invoices without a physical PDF document

Not too long ago, I answered a pretty interesting question on the Dynamics GP newsgroup. The user's company images signed delivery tickets and store these as PDF documents on a shared location on their network. The PDF document is named after the corresponding invoice number in Dynamics GP, for example, if the invoice number is INV010001, the corresponding image of the delivery ticket would be stored as INV010001.PDF





















The user wanted to know if it was possible to create a smartlist to show invoices without a scanned PDF image of the delivery ticket.

Solution

When you think of this problem from the eyes of a user, it seems almost impossible to write a SmartList that would produce the results wanted by the user. But when you breakdown the problem, the real issue is, how do we create a SQL Server view that is able to show whether a file exists for a specific Dynamics GP invoice document.

The solution is to create a SQL Server user-defined function (UDF) that is able to return whether a file exists or not. Then we can incorporate this UDF into our view query. Since views can be addressed from SmartList Builder then we are good to go.

First the UDF.

dbo.IsFileExist

-- Created by Mariano Gomez, MVP

-- Check if the UDF exists and drop
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
[IsFileExist]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION dbo.IsFileExist
GO

-- Create the UDF
CREATE FUNCTION dbo.IsFileExist(@Path VARCHAR(200), @FileName VARCHAR(200))
RETURNS INT
AS
BEGIN
DECLARE @FullPath VARCHAR(200);
DECLARE @FileExists INT;

-- Check for the back-slash at the end of the path string
IF RIGHT(RTRIM(@Path), 1) <> '\'
SET @Path = RTRIM(@Path) + '\';

-- concat the path with the file name
SET @FullPath = RTRIM(@Path) + RTRIM(@FileName);

-- run the xp_fileexist system stored proc to retrieve wheter the file exists or not
EXEC master.dbo.xp_fileexist @FullPath, @FileExists OUT;
RETURN @FileExists;
END;
GO

GRANT EXECUTE ON dbo.IsFileExist TO DYNGRP
GO

The above UDF uses the undocumented system stored procedure xp_fileexist which, when executed in its native form, will display information about the file passed in as a parameter. For example:


exec master..xp_fileexist 'c:\boot.ini


... produces the following results:


File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
1 0 1

(1 row(s) affected)


Now that we have the UDF function in place, we can prepare the view to be used with SmartList Builder.

dbo.vShowSOPAttachments

-- Created by Mariano Gomez, MVP
CREATE VIEW dbo.vShowSOPAttachments AS
WITH SOP_CTE (SOPNUMBE, SOPPDF) AS (
SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP10100
UNION ALL
SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP30200
)
SELECT SOPNUMBE, SOPPDF, dbo.IsFileExist('C:\', SOPPDF) AS [File Exists] FROM SOP_CTE;
GO

GRANT SELECT ON dbo.vShowSOPAttachments TO DYNGRP


Note the call to the dbo.IsFileExists UDF. With the view in place you can follow standard SmartList Builder procedures to implement and deploy the view. For information managing security and changes to SmartList Builder smartlist click here.

Until next post!

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

Monday, May 4, 2009

How to find the line number of an item on a Microsoft Dynamics GP document - Part 2

In Part 1 of this series you saw how to leverage a Common Table Expression (CTE) query with the use of the T-SQL OVER clause to determine the ordinal position of a line item displayed in a scrolling window. The ordinality is calculated based on the Line Sequence Number stored by Dynamics GP, but frees the developer of having to use complex formulas -- thay may or may not always work -- to determine ordinality of an item within the set. Continuing with our series, we will now explore how to use these scripts with VBA and Dexterity to retrieve the ordinal position value to use within any customization.

First, we will convert one of our scripts to a SQL Server stored procedure that can accept, say a document number, document type, and an item number and will then return the ordinal value.

Let's look at one such script from Part 1:

SOPLineItemSequenceWorkHistory.SQL

-- Created by Mariano Gomez, MVP
WITH SOPCTE (SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ) AS (

SELECT SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ FROM SOP10200
UNION ALL
SELECT SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ FROM SOP30300

)
SELECT SOPTYPE
, SOPNUMBE
, ITEMNMBR
, LNITMSEQ
, ROW_NUMBER() OVER(PARTITION BY SOPNUMBE, SOPTYPE ORDER BY SOPNUMBE, SOPTYPE, LNITMSEQ ASC) AS ROWNUMBER
FROM SOPCTE;

NOTE: This query is only supported on SQL Server 2005 and above.

We can convert this into a stored procedure as follows:

dbo.getSOPLineOrdinalValue'

-- Created by Mariano Gomez, MVP
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.getSOPLineOrdinalValue') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.getSOPLineOrdinalValue;
GO

CREATE PROCEDURE dbo.getSOPLineOrdinalValue
@SOPTYPE SMALLINT = NULL,
@SOPNUMBE CHAR(21) = NULL,
@ITEMNMBR CHAR(31) = NULL,
@ORD INT OUTPUT
AS

DECLARE @SOPDocs TABLE (
SOPTYPE SMALLINT,
SOPNUMBE CHAR(21),
ITEMNMBR CHAR(31),
LNITMSEQ INT,
ORD INT
);

-- create CTE query
-- NOTE: results are passed into a table variable because a WHERE clause would automatically set ordinality to 1

WITH SOPCTE (SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ) AS (
SELECT SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ FROM SOP10200
UNION ALL
SELECT SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ FROM SOP30300
)
INSERT @SOPDocs(SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ, ORD)
SELECT SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ, ROW_NUMBER() OVER(PARTITION BY SOPNUMBE, SOPTYPE ORDER BY SOPNUMBE, SOPTYPE, LNITMSEQ ASC) AS ROWNUMBER
FROM SOPCTE;

-- we will only retrieve the first occurrence as there may be
-- more than one line item for the same item number
SELECT TOP 1 @ORD = ORD FROM @SOPDocs WHERE (SOPTYPE = @SOPTYPE) AND (SOPNUMBE = @SOPNUMBE) AND (ITEMNMBR = @ITEMNMBR);
GO

GRANT EXECUTE ON dbo.getSOPLineOrdinalValue TO DYNGRP;
GO


NOTE: This query is only supported on SQL Server 2005 and above.

A few things to note, before testing the stored procedure. The result set of the CTE will be stored in a table variable to avoid forcing an ordinality of 1 created by a WHERE clause on the SELECT statement of the CTE.

We are forcing the query on the table variable to return the first matching item in the set by using the TOP clause in the SELECT statement. This will prevent our query from returning multiple results to the @ORD output variable which would cause an error in the query execution.

To call this stored procedure from VBA we will use the following Dynamics GP Developer newsgroup case in which the developer wants to know how to retrieve the ordinality of a line number within the scrolling window and store the value in a custom table in another application. While I will not go to the extent adding code to store the value, I will however add a field to the scrolling window to show the line number of the line item being displayed.

For this example I have added the field '(L) LineNumber' with Modifier to the scrolling window. Then, back in Dynamics GP, I added the SOP Entry window to VBA along with the SOP Type DDL, the SOP Number, Item Number, and '(L) LineNumber' fields. Now the scripts:

The getSOPType() function converts the SOP Type DDL value to the SOP Type value as stored in the SOP table. Remember that the DDL list values do not correspond with the storage value for the SOP Types. For simplicity sake, I am using a VBA function to do this conversion, but as a best practice you should be using the native Dynamics dictionary SOPTypeToInt() function which can be accessed via pass-through Sanscript with the Continuum Integration Library.

Private Function getSOPType()

' Created by Mariano Gomez, MVP
Private Function getSOPType(visualSOPType As Integer) As Integer
If visualSOPType > 3 Then
getSOPType = visualSOPType - 1
Exit Function
ElseIf visualSOPType = 3 Then
getSOPType = 6
Exit Function
End If

getSOPType = visualSOPType
End Function


The getSOPLineNumber() function will invoke our stored procedure to retrieve the actual ordinal value of the line number. As I write this, you can change the stored procedure to accept the Line Sequence Number as a parameter which would allow you to deal with multiple lines with the same item number.

Private Function getSOPLineNumber()

' Created by Mariano Gomez, MVP
Function getSOPLineNumber(nSOPType As Integer, sSOPNumber As String, sItemNumber As String) As Integer
Dim oUser As New UserInfo
Dim oCn As New ADODB.Connection
Dim oCmd As New ADODB.Command
Dim pSOPType, pSOPNumber As New ADODB.Parameter
Dim pItemNumber, pOrder As New ADODB.Parameter

'Retrieve an ADO connection for the current user
Set oCn = UserInfoGet.CreateADOConnection()

'Set the connection properties
oCn.CursorLocation = adUseClient

'Set the current database, using the IntercompanyID property
oCn.DefaultDatabase = UserInfoGet.IntercompanyID

'Create a command to retrieve the stored proc parameter
With oCmd
.ActiveConnection = oCn
.CommandType = adCmdStoredProc
.CommandText = "dbo.getSOPLineOrdinalValue"

' Set the stored procedure parameters
Set pSOPType = .CreateParameter("SOPTYPE", adSmallInt, adParamInput, 8, nSOPType)
.Parameters.Append pSOPType

Set pSOPNumber = .CreateParameter("SOPNUMBE", adChar, adParamInput, 21, sSOPNumber)
.Parameters.Append pSOPNumber

Set pItemNumber = .CreateParameter("ITEMNMBR", adChar, adParamInput, 31, sItemNumber)
.Parameters.Append pItemNumber

Set pOrder = .CreateParameter("ORD", adInteger, adParamOutput)
.Parameters.Append pOrder

.Execute Options:=adExecuteNoRecords
End With

getSOPLineNumber = pOrder.Value

Exit Function
End Function


Finally, we can choose a scrolling window VBA Grid event to display the line number, such as the Grid_BeforeLinePopulate(). Note the calls to the two previous functions.

Private Sub Grid_BeforeLinePopulate()

' Created by Mariano Gomez, MVP
Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)
Dim nSOPDDLType As Integer
Dim iSOPType As Integer
Dim sSOPNumber As String
Dim sItemNumber As String

nSOPDDLType = SalesTransactionEntry.TypeTypeID
sSOPNumber = SalesTransactionEntry.DocumentNo
sItemNumber = Me.ItemNumber

iSOPType = getSOPType(nSOPDDLType)
LineNumber = getSOPLineNumber(iSOPType, sSOPNumber, sItemNumber)
End Sub


Note that you can enhance this code to add the line number when the line change script is executed. This way, new line items in the window will also be numbered. I will provide the Dexterity technique as a comment entry to this article.

Related Articles:

Part 1 of the Series - On this blog, click here.
Scrolling Windows and Line Sequence Numbers - On this blog, click here.

Downloads:

v10 - SOP Entry modified screen and VBA code package - Click here.
v10 - Continuum Integration Library Reference package - Click here.
v10 - Microsoft ActiveX Data Objects 6.0 Reference package - Click here.

Until next post!

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

Sunday, May 3, 2009

How to find the line number of an item on a Microsoft Dynamics GP document - Part 1

One of the questions that most often come up in newsgroups and at client sites is, "how can I find the line number of an item on a *document*?", you can replace the word *document* for anything from a sales order, sales invoice all the way to a purchase order, purchase receipt, or invoicing invoice -- and pretty much any other thing you can think of.

The bottom line is, while Dynamics GP creates a line sequence number for every item entered in a scrolling window -- click here to see my past article on line sequence numbers and scrolling windows -- it is not very good at aiding users and/or developers in tracking the true ordinal value (1, 2, 3,.., n) of an item within the set of items on a document. So, I decided to give you a push by creating several T-SQL scripts that will help you identify these ordinal values. You can then take these same scripts and convert it into a SQL Server UDFs or stored procedures and call them from VBA or Dexterity if needed. I will also provide an example on these two techniques in Part 2 of this series.

So here are the scripts:

SOPLineItemSequenceWorkHistory.SQL

-- Created by Mariano Gomez, MVP
WITH SOPCTE (SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ) AS (
SELECT SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ FROM SOP10200
UNION ALL
SELECT SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ FROM SOP30300
)
SELECT SOPTYPE
, SOPNUMBE
, ITEMNMBR
, LNITMSEQ
, ROW_NUMBER() OVER(PARTITION BY SOPNUMBE, SOPTYPE ORDER BY SOPNUMBE, SOPTYPE, LNITMSEQ ASC) AS ROWNUMBER
FROM SOPCTE;


This is just a sample of the results produced by the query -- note the row number column for each partition by SOP Type and SOP Number:


SOPTYPE SOPNUMBE ITEMNMBR LNITMSEQ ROWNUMBER
------- --------------------- ------------------------------- ----------- --------------------
3 INVPS1004 PHON-ATT-53BL 32768 1
3 INVPS1004 FAXX-HLP-5433 49152 2
3 INVPS1005 HDWR-PNL-0001 32768 1
3 INVPS1006 ANSW-PAN-1450 16384 1
3 INVPS1007 TRAN-STR-N394 16384 1
3 INVS3000 3-C2924A 16384 1
3 INVS3000 3-D2657A 32768 2
3 INVS3000 3-D2659A 49152 3
3 INVS3000 5-STDLABOR 65536 4
3 INVS3000 5-STDLABOR 81920 5
3 INVS3000 5-TVLLABOR 98304 6
3 INVS3000 5-OVTLABOR 114688 7
3 INVS3000 5-FEE 131072 8
3 INVS3000 5-FEE 147456 9
3 INVS3001 3-C2924A 16384 1
3 INVS3001 3-D2657A 32768 2
3 INVS3001 3-D2659A 49152 3
3 INVS3001 5-STDLABOR 65536 4
3 INVS3001 5-STDLABOR 81920 5
3 INVS3001 5-TVLLABOR 98304 6
3 INVS3001 5-OVTLABOR 114688 7
3 INVS3001 5-FEE 131072 8
3 INVS3001 5-FEE 147456 9
3 INVS3002 3-C2924A 16384 1
3 INVS3002 3-D2657A 32768 2
3 INVS3002 3-D2659A 49152 3
3 INVS3002 5-STDLABOR 65536 4
3 INVS3002 5-STDLABOR 81920 5
3 INVS3002 5-TVLLABOR 98304 6
3 INVS3002 5-OVTLABOR 114688 7
3 INVS3002 5-FEE 131072 8
3 INVS3002 5-FEE 147456 9



POPPOLineItemSequenceWorkHistory.SQL

-- Created by Mariano Gomez, MVP
WITH POPPOCTE (POTYPE, PONUMBER, ITEMNMBR, ORD) AS (
SELECT POTYPE, PONUMBER, ITEMNMBR, ORD FROM POP10110
UNION ALL
SELECT POTYPE, PONUMBER, ITEMNMBR, ORD FROM POP30110
)
SELECT POTYPE
, PONUMBER
, ITEMNMBR
, ORD
, ROW_NUMBER() OVER(PARTITION BY PONUMBER, POTYPE ORDER BY PONUMBER, POTYPE, ORD ASC) AS ROWNUMBER
FROM POPPOCTE;


This is just a sample of the results produced by the query -- note the row number column for each partition by PO Type and PO Number:


POTYPE PONUMBER ITEMNMBR ORD ROWNUMBER
------ ----------------- ------------------------------- ----------- --------------------
1 PO1006 PHON-ATT-53BL 16384 1
1 PO1006 PHON-ATT-53BK 32768 2
1 PO1007 HDWR-LDS-0001 16384 1
1 PO1007 HDWR-RNG-0001 32768 2
1 PO1007 HDWR-PRO-4862 49152 3
1 PO1008 HDWR-T1I-0001 16384 1
1 PO1008 HDWR-TPS-0001 32768 2
1 PO1008 HDWR-PRO-4866 49152 3
1 PO1009 HDWR-ACC-0100 16384 1
1 PO1009 FAXX-CAN-9800 32768 2
1 PO1012 ACCS-CRD-25BK 16384 1
1 PO1013 ACCS-HDS-2EAR 16384 1
1 PO1014 PHON-GTE-3458 16384 1
1 PO1015 FAXX-SLK-2100 16384 1
1 PO1016 ACCS-RST-DXWH 16384 1
1 PO2000 ANSW-PAN-1450 16384 1
1 PO2000 PHON-ATT-53RD 32768 2



POPRCLineItemSequenceWorkHistory.SQL

-- Created by Mariano Gomez, MVP
WITH POPRCCTE (POPRCTNM, PONUMBER, ITEMNMBR, RCPTLNNM) AS (
SELECT POPRCTNM, PONUMBER, ITEMNMBR, RCPTLNNM FROM POP10310
UNION ALL
SELECT POPRCTNM, PONUMBER, ITEMNMBR, RCPTLNNM FROM POP30310
)
SELECT POPRCTNM
, PONUMBER
, ITEMNMBR
, RCPTLNNM
, ROW_NUMBER() OVER(PARTITION BY POPRCTNM ORDER BY POPRCTNM, RCPTLNNM ASC) AS ROWNUMBER
FROM POPRCCTE;


This is just a sample of the results produced by the query -- note the row number column for each partition by Receipt Number:


POPRCTNM PONUMBER ITEMNMBR RCPTLNNM ROWNUMBER
----------------- ----------------- ------------------------------- ----------- --------------------
RCT1000 PO1001 WIRE-MCD-0001 16384 1
RCT1000 PO1001 WIRE-SCD-0001 32768 2
RCT1001 PO1002 PHON-GTE-5043 16384 1
RCT1001 PO1002 PHON-GTE-3458 32768 2
RCT1002 PO1003 HDWR-TPS-0001 16384 1
RCT1003 PO1004 HDWR-SWM-0100 16384 1
RCT1003 PO1004 HDWR-SRG-0001 32768 2
RCT1004 PO1005 ACCS-HDS-1EAR 16384 1
RCT1004 PO1005 ACCS-CRD-25BK 32768 2
RCT1005 PO1001 WIRE-MCD-0001 16384 1
RCT1005 PO1001 WIRE-SCD-0001 32768 2
RCT1006 PO1002 PHON-GTE-5043 16384 1
RCT1006 PO1002 PHON-GTE-3458 32768 2
RCT1007 PO0996 PHON-ATT-53RD 16384 1
RCT1007 PO0996 ANSW-PAN-1450 32768 2
RCT1008 PO0998 PHON-ATT-53BL 16384 1
RCT1009 PO0999 ACCS-CRD-12WH 16384 1
RCT1009 PO0999 ANSW-PAN-1450 32768 2
RCT1010 PO0996 PHON-ATT-53RD 16384 1
RCT1010 PO0996 ANSW-PAN-1450 32768 2
RCT1011 PO1009 FAXX-CAN-9800 16384 1
RCT1011 PO1009 HDWR-ACC-0100 32768 2
RCT1011 PO1012 ACCS-CRD-25BK 49152 3



IVCLineItemWorkHistory.SQL

-- Created by Mariano Gomez, MVP
WITH IVCCTE (DOCTYPE, INVCNMBR, ITEMNMBR, LNITMSEQ) AS (
SELECT DOCTYPE, INVCNMBR, ITEMNMBR, LNITMSEQ FROM IVC10101
UNION ALL
SELECT DOCTYPE, INVCNMBR, ITEMNMBR, LNITMSEQ FROM IVC30102
)
SELECT DOCTYPE
, INVCNMBR
, ITEMNMBR
, LNITMSEQ
, ROW_NUMBER() OVER(PARTITION BY DOCTYPE, INVCNMBR ORDER BY DOCTYPE, INVCNMBR, LNITMSEQ ASC) AS ROWNUMBER
FROM IVCCTE;


This is just a sample of the results produced by the query -- note the row number column for each partition by IVC Type and IVC Number:


DOCTYPE INVCNMBR ITEMNMBR LNITMSEQ ROWNUMBER
------- --------------------- ------------------------------- ----------- --------------------
1 IVC01 PHON-ATT-53WH 16384 1
1 IVC02 PHON-ATT-53RD 16384 1
1 IVC03 PHON-ATT-53BK 16384 1
1 IVC04 FAXX-RIC-060E 32768 1
1 IVC05 PHON-PAN-3155 16384 1
1 IVC06 HDWR-CAB-0001 32768 1
1 IVC07 ANSW-PAN-1450 16384 1
1 IVC08 ACCS-RST-DXWH 16384 1
1 IVC09 ACCS-RST-DXBK 16384 1
1 IVC10 ACCS-CRD-12WH 16384 1
1 IVC11 PHON-PAN-3155 49152 1
1 IVC12 ANSW-PAN-1450 16384 1
1 IVC15 HDWR-SRG-0001 16384 1
1 IVC21 24X IDE 16384 1
1 IVC21 32X IDE 32768 2
1 IVC22 ACCS-CRD-12WH 16384 1
1 IVC22 BOT100G 32768 2
1 IVC23 40X IDE 49152 1

(18 row(s) affected)


NOTE: I am a big fan of SQL Server Common Table Expressions (CTE) and use those as often as I can to simplify my queries and avoid the use of subqueries. In addition, they tend to produce better query execution plans which translate into performance gains.

Please stay tuned! Part 2 of this series will show you how to take advantage of these queries from Dexterity and VBA.

Related Articles

Microsoft Dynamics GP Scrolling Windows and Line Sequence Numbers - On this blog, click here.

Until next post!

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