Dynamic Code Blocks

Microsoft Dynamics GP & .NET technical Blog by Tim Wappat

soptypedatabase vs soptype in Dynamics GP SOPEntry Window

It is really easy to use the wrong SopType field in SopEntry of Dynamics GP, I examine the issue here.

I was asked today why I’d just typed SOPTypeDatabase rather than SOPType when we were code reviewing a bit of code. In the SopEntry Window the there is a field SOPType, this shares its name with the database field soptype that defines what type an order is:

Type of Sales Document

SOPTYPE  value

Drop Down Field Index
Quote 1 1
Order 2 2
Invoice 3 4
Return 4 5
BackOrder 5 6
Fulfilment Order 6 3

image

The SopType field value originates from the position index of the selected item in the drop down box as shown on the SOP Entry form below. As the order of items in the combobox items does not follow the order used in the database, a translation is needed of values.

To make it easier, some helpful MS dev has created a field that does this for you (also helps binding the field no doubt in dex). This field, the SOPTypeDatabse field represents the currently selected SopType, but with a value as it should be stored in the database, hence the word database on the end of the field. Hence in most circumstances this is the value to use for SOPTYPE when writing VBA or .NET add-ins for SOPEntry.

It is really easy to get caught out and forget this when working between TSQL and different GP forms and just type into the keyboard soptype, and as that field exists no error occurs, but it is the wrong field for that form, copy and paste of snippets of code from other forms can cause this too. I should know better but I was caught out by it myself, after this question, we checked and there were a couple of instances of the wrong SOPType field being used in the code that we were reviewing. Due to the way the code was written it had not caused any odd behaviour yet (“is soptype in (2,3)?”) but would rear its head later in use or testing.

image

Phantom stock allocations and Dynamics GP

Looking at one of the reasons phantom allocations of stock can occur in Dynamics GP

Users find instances of an item in inventory that claims to be allocated, but no transaction document with the same total allocations for that item exist (allocation enquiry). This happens very frequently and support will be quick to blame 3rd party products, Here I show it happening with base product. So for the question from the user is how could this happen? it is easy to show a sequence of events to replicate it.

Create phantom allocation from sales document

There are many modules that can allocate stock, I’d recommend reading this post !item allocation quantity does not match sum of allocated orders in microsoft dynamics gp and downloading the show allocations script to see just how many places an inventory allocation can occur.

Lets look at the example of a sales order transaction, this could equally be another type of transaction such as an inventory movement..

A new sales order is created, enter an item code for the first item, it needs to be an item that has available inventory. Use the scrolling grid window on SOP entry. Tabbing off item number field to the quantity field, check the database to see what is happening. The database shows that the sales order row is not yet inserted in the sales order table. It also shows nothing is allocated in inventory for this item.


image

image

Next tab through the quantity field, check the database and see what has happened. See that now the item has an allocation in inventory, the item allocate script runs when a user leaves the quantity field. Note that a row representing the sales order line has not yet been created in the database

image.

image

Next tab through the prices and onto the next sales order line. Check the database, it can be seen that the sale order line row has been be inserted into the database at this point.

image

image

 

For a short time the sales order line only existed in the local (in memory) table buffer within the Dynamics GP sales order entry form, However there is stock allocated to this virtual sales line. It is important to appreciate that the allocation of the stock in the database and the creation of the order line in the database are not inside one SQL transaction, so it is vulnerable to corruption.

Corruption could happen if the GP client application got disconnected after it had allocated the inventory stock and before the user saves the sales line or causes the line to save by moving to a new line in the order. The quantity allocated could also be corrupted by a user adjusting the quantity on an order, thus changing the allocation relative to the database sales order line but then never updating that database quantity by crashing the GP client before the line is saved. This would result in an allocation amount that differs to the line.

Some examples of how this may happen:

  • an administrator kills off user’s GP client on terminal server/citrix, while GP session is in use, in a sales order line or other inventory transaction
  • a user kills off the GP process or the GP process dies (crashes on the client)
  • network vanishes -say network infrastructure problem or VPN dropping on a direct connection to the database (not recommended configuration for this reason)
  • Power outage
  • Computer sleeps and disconnects from network
  • SQL server is force restarted or has failure  of some nature

I’m certain there will be other ways for this to happen too, feel free to comment below if you can think of any.

I see a constant trickle of these phantom allocations issues. Reconciling stock or using a SQL script to do the same thing quickly puts things right again but it is frustrating that it happens at all. If GP were written today I am certain these sorts of operation would be performed inside a single SQL transaction that would protect the integrity of the data. GP comes from an era before modern databases and lacks both in built database referential integrity and full use of SQL transactions. Allocation of the stock has to be real time, the nature of stock allocation makes it a multiuser real time system, but the creation of the sales line could be inside a transaction with the allocation, I wish Microsoft would sort this one.

To manage the problem it is possible to write SQL script to run on SQL job to watch for phantom allocations and notify administrators, or even let the script fix the allocations say when the system is idle, overnight.

-2% progress on Sales Work Checklinks

Strange case of the negative progress bar in Checklinks of Dynamics GP

image

This was a strange one, reconciling Sales Work in Dynamics GP, the percentage started a 0% started going up then jumped to minus 2%! Truly bizarre!

Care naming database objects in Dynamics GP for custom GP Addins

A discussion on “The customer combiner” tool/form in Dynamics provokes thoughts on good practice in

image

A discussion on “The customer combiner” tool/form in Dynamics provokes thoughts on good practice in database field naming. However lets not start there, first instead let me take you through my current thinking.

Schemas

Schemas are a container level within the object hierarchy of SQL server. Using SQL you may have noticed from formats for SQL statements such as:

SELECT * FROM TWO.dbo.SOP10100
 
The dot notation shows the database (TWO) followed by the schema (dbo) and then the object (table SOP10100, sales order processing header). The schema is the bit in the middle, dbo, as default, or database owner. If calling from the owner database with the normal user, then both the database name and the schema can be omitted and the statement will still work, this is for convenience.
 
SELECT * FROM SOP10100
 
The above statement will run against the user’s default schema, dbo for GP users, and thus will operate correctly.
All our modifications to the GP database, except for smart list views are held in our own schema, say it is named “mods”. This means listing all the sql objects that “belong to us” is easy as they are grouped together in the GUI of SQL server management studio. They are also easy to select out in SQL as DMO statements all understand schemas and thus specificity of schemas to apply changes to is easy. Schemas can also be granted permission sets, so this also makes it easy to manage permissions for our object. Being in a schema makes it also easy to script all the objects out and in for creating new instances or build scripts for all our assets in the GP databases. I’m a fan boy, can’t you tell!
 
GP natively does not make use of schemas (or really much of SQL server for that matter), everything is lumped into the dbo schema. However our architecture of keep everything separate has worked extremely well over the many years, only on very, very rare occasion presenting issues.
 
I admit if we were reselling add-ins I would be more nervous about using schemas as on very rare occasions we encounter a GP upgrade script or utility that does not play with them nicely, support calls I’d just rather not have. When issues occur, every time except for smartlists, it has been the same issue. The cause, dynamically built SQL in native GP SQL scripts that ignore the schema.
These scripts discover our objects as the scripts are none specific to schema (correctly), and then try to work with our objects without prepending the schema name, which is wrong, at that point they need to specify the schema. Every time it is a doddle to fix too, just a shame the SQL devs don’t have an awareness of this issues, or it is not added as a test scenario in testing, as support for schemas would allow everyone to separate more properly their product’s assets from every other vendors product assets . Moan over.
 
A view created in a schema other than dbo does not get picked up by custom smart lists, hence they end up in the default schema. Naming conventions can still be applied to help keep these together.
 
An example of a fail with schemas, is the customer combiner utility, I have written that issue up in another post.

Object names

Object names, such as triggers, tables, views, stored procedures and functions should all have clear names, personally I like the name to specify the module it operates with followed by the function of the object. This helps keep like concerns together when listed.

Recently my old preference for neat, efficient to type terse names has given way for leaning toward more verbose descriptive names that allow new developers or IT pros to support the mods more easily, as they are readable if bubbled up through exception dialogs or when looked at raw in SQL management studio.
 
However every development house have their own style, reasons for conventions, roll with what works for you.

Field Names

It has now become a top consideration when creating database tables. Be very careful of what names are allocated to fields. I discovered this from a presentation I attended with the Fargo Microsoft GP developers where I immediately picked up on and interrogated them on a nuance of something they said when talking about the customer combiner tool. This is a tool in GP2015R2 that used to be part of the Professional Tools Library.  They mentioned that the tool “searches for customer numbers in the database” and renames the content in those fields. That as the words innocently left their mouths, a panic alarm went off in my head.

Using GP field names for your own fields

Basically (among other things) this tool performs DMO queries on the GP company and gets any field named CUSTNMBR that exists in any table. Remember to operate this window an admin user must be logged in. If you are into SQL see below a tiny bit of what it queries and the script generated(I’ve put the aaaa & bbb in just so you can run it safely).

SELECT 'update [' + o.NAME + '] set CUSTNMBR=' + 'aaaa' + ' where CUSTNMBR=' + rtrim('bbb')
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CUSTNMBR'
AND o.NAME <> 'RM00101'
AND o.NAME <> 'RM00102'
AND o.NAME <> 'RM00103'
AND o.NAME <> 'RM00106'
AND o.NAME <> 'RM00104'
AND o.NAME <> 'ASI82615'
AND o.NAME <> 'CN00500'
AND o.NAME <> 'SVC00950'
AND o.NAME <> 'SVC00960'
AND o.NAME <> 'MDS00501'
AND o.NAME <> 'ASI82610'
AND o.NAME <> 'ASILOC50'
AND o.NAME <> 'ASI82650'
AND o.NAME <> 'CN00400'
AND o.NAME <> 'CN100100'
AND o.NAME <> 'CN100200'
AND o.NAME <> 'PA00010'
AND o.NAME <> 'PA00001'
AND o.NAME <> 'SOP10100'
AND o.NAME <> 'SOP60300'
AND o.NAME <> 'PA00501'
AND o.NAME <> 'PA00511'
AND o.NAME <> 'PA00521'
AND o.NAME <> 'PA00531'
AND o.NAME <> 'PA00532'
AND o.NAME <> 'PA50100'
AND o.NAME <> 'VAT00300'
AND o.NAME <> 'VAT10101'
AND o.NAME <> 'ME147214'
AND o.NAME <> 'RM30701'
AND o.NAME <> 'RM30702'
AND o.NAME <> 'SV00100'
AND o.NAME <> 'PA02301'
AND o.NAME <> 'SOP30200'
AND o.NAME <> 'SOP60200'
AND o.NAME <> 'tcsSOPTB00007_Cust_Add'
AND o.NAME <> 'gpItmCus'
AND o.NAME <> 'SC020330'
ORDER BY o.NAME

The script ignores some specific tables that are dealt with separately or should not have the customer number renamed by specifically putting them in the WHERE clause. Some of these are to allow the script to specifically address the primary key issue for native GP tables, the primary key issue is something I will describe later in this post.

Thus: If your custom table has CUSTNMBR as a field it will be in scope for this script!

On the raw TWO example company this gives as an output:

update [CB100000] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB300088] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB330222] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB332222] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB333002] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB333222] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB333555] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB440008] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB441111] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB550008] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB900058] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CB990007] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CBDEP002] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CBEU1188] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CN00100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CN100201] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CN20100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CN20101] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CN30100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [CN30200] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [DO10100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [DO20100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [DO30100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [DO50100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [EC010031] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [EDCEI009] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [EDCEI016] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [EDCEI019] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [EDCEI021] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [EDCVAT36] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [gpCustomerException] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [IC090000] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [ICJC9000] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [IV30300] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [IVC10100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [IVC30101] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [MC020102] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [MC020104] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [MC10101] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [PA50102] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [PA50103] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [palbCash] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [palbInvc] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [POP10100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [POP30100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [PT00101] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM00401] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM10101] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM10201] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM10301] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM10501] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM10504] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM10601] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM20101] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM20102] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM20201] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM20400] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM20401] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM30101] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM30201] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM30202] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM30301] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM30401] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM30501] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM30601] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM50100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM50101] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM50102] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM50103] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM50104] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM50105] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM50505] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RM50506] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RVLPD004] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RVLPD005] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RVLPD007] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RVLPD008] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RVLPD009] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [RVLPD010] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SOP00300] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SOP30201] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SOP40700] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SOP40703] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SOP50100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SOP50200] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00200] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00215] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00300] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00303] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00325] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00400] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00600] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00601] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00608] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00615] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00625] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00635] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00651] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00654] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00660] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00670] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00955] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC00998] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC05000] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC05015] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC05115] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC05200] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC06100] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC10301] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC30200] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC30600] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC30601] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC30608] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC35000] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC35200] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SVC80010] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [SY06000] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [taRMRECALC] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [tcsINVTB00091_IR_DD] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [tcsINVTB00091_IR_DDI] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [tcsINVTB00091_IR_EXITRNS] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [tcsINVTB00091_IR_INHRTD] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [tcsSOPTB00091_IR_EXTI] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [TX00301] set CUSTNMBR=aaaa where CUSTNMBR=bbb
update [VAT10302] set CUSTNMBR=aaaa where CUSTNMBR=bbb

This seems to  be a great idea, as developers of add-ins benefit from this tool without doing any work to support it, or do they…? This will only work so long as the field named in the custom table(s) have the same meaning/content as those of the default field names used by GP (CUSTNMBR). There lurks a problem here too. If the field to be merged (in the case of a customer merge script) happens to be the primary key on the table and the above update script where to be ran against it, a Primary Key Violation error would result. Thus the need to delete the record in the “pre” script, something I will discuss shortly. There are other modifier tools and upgrade scripts that will work the same manner, so consideration of the interaction between a merge script like this and any primary key columns on custom tables must be must be made every time a new tool is released. So now its not so easy is it?

For me this type of DMO script has set a precedence for the naming of fields in GP, in that it is now important to not use a well known GP field name for a purpose other than that which GP uses it for. For example, do not in a custom credit card table, hold the credit card number in a field called CUSTNMR, perhaps in the developers head this means, customer credit card number. Think about if you have a real customer number that happens to be freakily the same as that credit card number (by coincidence)  and that customer is subsequently renamed, well the credit card number is going to be renamed to something that is no longer a credit card number! I know this is a contrived example, but in reality I’m certain there are more plausible examples out there where this is likely to happen.

It is also worth considering the juxtaposition where a developer may, on purpose names fields to specifically take it out of scope as regards to taking part in such automated script operations.

An aside on Customer Combiner post and pre stored procedures

Although really outside the original scope of this post, it is interesting to note that the customer combiner developers have kindly given us SQL hooks in a similar manner to those used in eConnect with both a Pre and Post procedure provided; dbo.rmCustomerCombinerPre dbo.rmCustomerCombinerPost.

image

These procedures allow custom TSQL code to be injected before and after combining customers to handle more complicated issues with data relating to your add-ins, remember the primary key issue earlier. By adding a delete into this pre-procedure it is possible to deal with the duplicate key before the customer modifier even gets a chance to see the record.

Remember to play safely with other add-in developers, in reality it would be better to do this with hooks in the add-in code to prevent trampling the TSQL of others in these pre and post procedures. However for end users these are indeed useful.