The Dynamics GP Blogster

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

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, ...

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 provid...

I thought I would write about something meaningless that no one really thinks about. However, I realized that this is a compelling topic since it makes for some interesting discussion around the ability to insert line items withinin most transaction scrolling windows. Let’s take some time to understand where Line Sequence Numbers come from. A Line Sequence Number is auto generated by Dynamics GP and uses a seed value of 16,384 as a basis for the first line. Each subsequent line in the scrolling window is incremented by this number, so line number 2 would have a Line Sequence Number value of 32,768, line number 3 would have Line Sequence Number Value of 49,152, and so on. Since most GP transaction entry scrolling windows allow inserting rows in between line items, the newly inserted Line Sequence Number value is calculated as an average between the previous and the next line item sequences, i.e., if you were to insert an item in between row 1 and row 2 in the scrolling window -- keep in...