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 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
SELECT * FROM SOP10100
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.
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.