Earlier this week, a forum post by my friend Abra Gilman on the GPUG Open Forum brought up the problem where a user was getting an error when attempting to insert a new line into a SOP Transaction.
This problem can be reproduced by attempting to insert lines in the same location more than 14 times, on the 15th attempt an error will be generated.
The issue is the way that inserting a line works. This may also explain why the Line Item Sequence (LNITMSEQ) field increments by 16384 each time you add a line at the bottom of the document.
When you insert a line, the system gets the sequence number of the line that is currently selected and the sequence number of the line above (or zero if no lines are above). Then it calculates the sequence number for the new line with the formula below:
New Sequence = (Current Line Sequence + Line Above Sequence) / 2
This formula is just averaging the numbers or selecting the midpoint between them. Each time a line is inserted in the same location the available range of numbers is halved. Eventually, there are no more integer values available and you get the error “You cannot insert a row“.
Given the example where you continuously add a line at the top of the document when the first line has the sequence number 16834 (2^14), the resulting sequence numbers of the inserted lines will be:
- 8192, 4096, 2048, 1024, 512, 256, 128, 64, 32, 16, 8, 4, 2, 1, Error.
So, I decided to create a renumbering solution using GP Power Tools and its Developer Tools module and post it for everyone to use.
Before you say that you don’t have GP Power Tools, please note that you can install it and activate a free 30 day trial period and load the configuration file containing this project and see if you find this solution useful.
The project contains 3 triggers, a runtime execute (Dexterity) script, 2 SQL scripts and a parameter list.
The first two triggers add a “Renumber Sales Transaction Lines” menu choice to the Additional Menu on the Sales Transaction Entry form and a “Renumber Sales Transaction Lines” application level menu to the Sales >> Utilities menu. The third trigger intercepts the error message dialog and replaces it with a dialog asking if you wish to renumber the lines of the sales transaction.
Note: The application level menu requires the free Visual Studio Integration Toolkit product to be installed and registered.
The Runtime Execute script reads the details of the transaction showing in the window and renumbers it. It is called by two of the trigger scripts.
The first SQL script is the actual Transact-SQL code which performs the renumbering. It has been designed to find all tables in the company database that contain the fields SOP Type (SOPTYPE), SOP Number (SOPNUMBE) and Line Item Sequence (LNITMSEQ) and contain records. It will then run an update statement against each of tables making the same changes to the sequence numbers. This approach should work correctly for all core Dynamics tables, all Microsoft addon products and all ISV addon products.
However, if you have linked data in other databases or tables that don’t use the same column/field names, they will not be updated. This code is provided “as is” and it is up to to you to test in your environment. That said if you find any issues, please let me know so I can fix them.
The second SQL Script is used by the Parameter List to provide a SOP Transaction lookup when using the Parameter List dialog.
This dialog is used when selecting the application level menu option or when running the published SQL script directly.
To use this example:
- If GP Power Tools (build 27 is minimum required) is not installed, just download and install GP Power Tools.
- If necessary, follow the on screen instructions to enter your contact details and request a trial period for all the modules.
- Once installed, use GP Power Tools >> Routines >> Configuration Export/Import to import the xml file contained in the archive linked to this article (below).
- Then you can manually start the Project’s triggers or just log in again to activate them automatically.
Now you have access to the added menus and if you do get the error, it will allow you to renumber the transaction lines straight away to fix it.
Hope this example helps you can see the power that GP Power Tools provides.
For more information see:
GPPT Configuration File: GP Power Tools Settings Sop_renumber.dbg.zip
This article was originally posted on http://www.winthropdc.com/blog.