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;
: 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
: 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/
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".
