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/