Microsoft Dynamics GP

Care naming database objects in Dynamics GP for custom GP Addins

Tim Wappat

Sep 2, 2015 • 10 min read

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. [more]

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.

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.