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/

11 comments:

Jim said...

Dear Mariano Gomez,

Thanks for your great POST!

Can you tell me how do I get the correct line number if the sop got 2 same item?


Jim

Mariano Gomez said...

Jim,

Not quite sure. I would have to do some more research on this issue, but my gut feeling tells me you will only be able to work this effectively on SOPs with unique items.

Best regards,

MG.-
Mariano Gomez, MVP

Jim said...

Dear Mariano Gomez,

Finally, I found the 'Line Item Sequence' field in SOP Grid. My issue SOLVED. :)

Thanks for your help and hints.

Jim

MichaelJ2 said...

Mariano,

I used this today. Great Post!

Thanks!

MJ

David said...

Thanks Mariano,
This post is great. I am actually trying to use it for an RMA window. and like Jim I have the same issue of 2 items maybe the same, I was wondering how Jim was able to solve this issue. Also I am trying to add the (L)Line item in my window with modifier , but I can't figure out how to do this or how to find this field. Can you guide me on this?
Thanks in advance

David

Mariano Gomez said...

Dave,

Thanks for your kind words and inquiry. I don't think this query can effectively work in a situation where you have the same line item twice on the document.

As for your question about the '(L) Line Item' field, you need to create it with Modifier by adding an integer control to the window. Then you would programmatically display the line item number to that field with VBA.

Hope this helps,

MG.-

Syed said...

Hi Mariano,

This one is a great article and it gave me a boost to look into things in new direction.

I have a question for you, excuse me if it is a basic level one for you. I need to auto add a new line in the SOP Tranaction Entry detail grid once the item is already added. The scenerio is, 2 items are bound together and if we enter one item in the transaction line the other item should auto populate in the next line after the lost focus on item number. I have created a function to capture the other item from database once the item number is entered in the first line but I am stuck as how to create a new line on the grid on lost focus event and put it in the next line. I would require help in vba code in achieving this, I appreciate your help.

Mariano Gomez said...

Syed,

What you are asking for is now standard functionality in GP2013 - "Suggested Items". There's also a 3rd party product called Myridas Series from Trinity Computers which features "Linked Item Selling" and does exactly the same thing.

Hope this helps.

Syed said...

Hi Mariano,

Thanks for your quick reply. The issue is that I need to complete this task in the older running version which is GP 9 and it can only be handled by vba. How would I be able to add a new line automatically on a lost focus event of 1st item? This is creating confusion for me, do you have solution for this one? Again, thanks for your help.

simon said...

Hi Mariano,

Is there a way to "go to line" in POP Entry.

The problem?
We have a POP with 1000 items and We need to find one of them to change price but I can't find a method to do it.

Could you help us?

Thanks,

Simon

1antares1 said...

Undoubtedly appreciate the advice.

However, what was most optimal search or iterate SOP lines without addressing the data source whenever required business logic, being more constant validation. I think that it is sufficient to insert Dynamics connect each line to go down.

Has anyone found a way to interact with the DataGrid? I still can not for all that not all properties are documented.

Thanked.