Why modifying a product at the dexterity level is rarely a good thing

I get requests to customize Dynamics GP and/or Signature all the time.  Over my 17 years of doing this I have tried to pay attention to what works and what doesn't.  Here are my observations:

Above all, keep it simple.  Process trumps code every time.  Many times the process can be simplified and at the same time avoid customization altogether.  Left to their own devices, people tend to complicate processes.  There are many reasons for this, which I will not debate here.  The bottom line is that each business is not as unique as their employees like to think they are.  If hundreds of service companies of a similar size to yours are able to use a piece of software with no modifications, and you think your company absolutely needs that modification, you need to take a hard look at why.

Invariably, there are differences and unique situations that bring about the need for custom code.  In those cases, you should use a minimalist approach, while taking into account long term maintenance.  A customization that you can't afford to maintain over the long haul is not a solution.  The same goes for one that is overly complicated and fragile.

I get a lot of requests where the partner assumes Dexterity is the best fit.  That is almost never the case.  Here is how dexterity mods work:
*** Dynamics GP allows dexterity mods to the core product.  These are called 3rd party products and appear in the dynamics.set file with a number issued by Microsoft.  Signature is basically one great big dexterity mod.
*** Any time Microsoft changes anything about Dynamics GP or the 3rd party that has been modified changes anything (Service packs, version changes, end of year payroll, etc...) the dexterity mods have to be re-evaluated and potentially changed to continue working with the new core Dynamics GP product.  In the case that a dex mod has been done to a 3rd party product, you must wait for the 3rd party dictionary to be re-evaluated, changed, tested, and released.  Then you must start with the new code base and completely re-code the dex mod.
*** The ability of 3rd party dictionaries to talk to each other is limited.

So if you are building a product with functionality that needs to tightly integrate to Dynamics GP, dexterity may be the best solution.  If you are customizing a product, it is always a last resort.


Here are some ways that customizations can be accomplished, and the pro's and cons of each.  I have listed them in order of what is typically lowest cost to highest cost.

Modifier:
This tool is packaged with Dynamics GP and can be used on 3rd party products also.
Pros:
- Relatively cheap to purchase
- With minimal training, one or more of your own employees can make and maintain these types of modifications.
- Visual changes can be made to forms and reports

Cons:
- You can't make any code changes with this
- Report changes are limited

Modifier with VBA:
This tool is packaged with Dynamics GP and can be used on 3rd party products also.
Pros:
- Same as Modifier
- Code can be added
- Long term maintenance costs are low

Cons:
- Although you can easily add code, changing the behavior of the dex code is limited and problematic
- Report changes are limited
- Can be buggy in a multi-user environment

SSRS
Dynamics GP and many 3rd party products have the capability of calling SSRS reports rather than Dexterity reports.  The SSRS report can be customized.
Pros:
- Easy to customize.  Many minor customizations can be done by power users.
- With some care in naming conventions, they can be easy to maintain over time.
- Long term maintenance costs are low.

Cons:
- May need to create a Addin to call a custom SSRS report if the SSRS report does not already exist in the product, or if parameters need to be passed.
- Requires SQL language skills and table structure knowledge to build reports from scratch.


.NET Addins
This tool requires Visual Studio in order to use it.  It can be used on 3rd party products also.  It involves placing custom DLLs in the Addins folder in the GP client.
Pros:
- Robust UI and code ability
- Long term maintenance costs are low.
- Service packs to the product usually require no change at all to the addin
- Can be coded and implemented quickly
- Can easily override and replace most dex logic
- Can easily create entirely new custom windows

Cons:
- Can't override absolutely everything in dex.

Dexterity Mods
This involves creating a custom dexterity dictionary that is added to the dynamics.set file.  The pros and cons below assume you are making changes to a 3rd party.
Pros:
- Can directly and surgically change dexterity code.
- Results in the tightest possible integration to GP.

Cons:
- Difficult or impossible to communicate between 3rd party dictionaries, meaning you have to create a custom version of the 3rd party dictionary in order to make changes.
- Difficult to test and deploy
- Rapidly shrinking pool of proficient dexterity programmers means higher costs and longer wait times.
- Very high long term maintenance costs.  Basically you are re-coding the entire thing each time you do a service pack, meaning you get to pay full price for it over and over as long as you need it.

Editing table structure in SQL

By default, SSMS will not let you insert a column in the middle of a table and save the changes.  This is because it requires dropping and recreating the table.

However, if you are OK with doing that, you don;t need to do it manually.  You can just shut off this validation in SSMS.

Tools > Options > Designers > Table and Database Designers
Clear the Prevent saving changes that require the table to be re-created check box.

It's that easy.


Actually, here are all the changes what this validation catches:

• Change settings allow nulls of a column.
• Rearrange the columns of the table.
• Change the data type of the column.
• Add a new column.

As for why this validation is there, it seems to mostly have to do with change tracking.
https://support.microsoft.com/en-us/help/956176/error-message-when-you-try-to-save-a-table-in-sql-server-saving-changes-is-not-permitted

Custom Date and Time functions in SQL

Many of you are probably aware that you can create a function in SQL that returns a value and then use it like a column in a SQL statement.

For instance,
select WS_DateOnly(getdate())
will return a single column containing the date portion of the current date (truncating off the time).

In GP, these type of time truncations are critical because the columns contain either a date or a time, but not both.

I understand the need for using functions to keep formatting and such the same.  They can also simplify SQL statements by removing much of the calculations from the statement.

However, in the case of date and time truncation, format and calculation does not come into play at all.  The following statements are equivalent.

select WS_DateOnly(getdate())
select convert(varchar,getdate(),102)

and

select WS_TimeOnly(getdate())
select convert(varchar,getdate(),114)

The formats for the dates do not really matter other than 102 is a time-less date and 114 is a date-less time.

Here are some reasons that burying this type of calculation in a function are a bad idea:
1. SQL creates a dependency for each place the function is used.  This adds to overhead and erodes performance.
2. A developer reading the statement that uses the function does not know exactly what the function is doing unless they go open it, slowing down troubleshooting.
3. SQL traces see the function call as a separate event, cluttering up traces.


The most painful issue of all is when the function gets added to a check constraint on a table.  This creates a dependency that will crash the upgrade utilities for GP and requires manual correction.

So the takeaways are:
1. Never use functions to mask simple SQL functions like date truncation
2. Use functions sparingly
3. Never use a function in a table constraint on a GP database.

script to read company setup options

/* Break Down Company Options Multi-Select List Box into individual options. */
/* Script by David Musgrave, Created 01-Aug-2012, Last Modified: 01-Aug-2012 */
select INTERID, CMPANYID, CMPNYNAM, Company_Options
, CASE WHEN Company_Options & 65536 * 256 > 0 THEN 1 ELSE 0 END — Bit 24
+ CASE WHEN Company_Options & 65536 * 512 > 0 THEN 2 ELSE 0 END — Bit 25
+ CASE WHEN Company_Options & 65536 * 1024 > 0 THEN 4 ELSE 0 END — Bit 26
+ CASE WHEN Company_Options & 65536 * 2048 > 0 THEN 8 ELSE 0 END — Bit 27
+ CASE WHEN Company_Options & 65536 * 4096 > 0 THEN 16 ELSE 0 END — Bit 28
+ CASE WHEN Company_Options & 65536 * 8192 > 0 THEN 32 ELSE 0 END — Bit 29
+ CASE WHEN Company_Options & 65536 * 16384 > 0 THEN 64 ELSE 0 END — Bit 30
+ CASE WHEN Company_Options < 0 /* Negative */ THEN 128 ELSE 0 END — Bit 31

+ CASE WHEN Company_Options & 65536 * 1 > 0 THEN 256 ELSE 0 END — Bit 16
+ CASE WHEN Company_Options & 65536 * 2 > 0 THEN 512 ELSE 0 END — Bit 17
+ CASE WHEN Company_Options & 65536 * 4 > 0 THEN 1024 ELSE 0 END — Bit 18
+ CASE WHEN Company_Options & 65536 * 8 > 0 THEN 2048 ELSE 0 END — Bit 19
+ CASE WHEN Company_Options & 65536 * 16 > 0 THEN 4096 ELSE 0 END — Bit 20
+ CASE WHEN Company_Options & 65536 * 32 > 0 THEN 8192 ELSE 0 END — Bit 21
+ CASE WHEN Company_Options & 65536 * 64 > 0 THEN 16384 ELSE 0 END — Bit 22
+ CASE WHEN Company_Options & 65536 * 128 > 0 THEN 32768 ELSE 0 END — Bit 23

+ CASE WHEN Company_Options % 65536 & 256 > 0 THEN 65536 * 1 ELSE 0 END — Bit 8
+ CASE WHEN Company_Options % 65536 & 512 > 0 THEN 65536 * 2 ELSE 0 END — Bit 9
+ CASE WHEN Company_Options % 65536 & 1024 > 0 THEN 65536 * 4 ELSE 0 END — Bit 10
+ CASE WHEN Company_Options % 65536 & 2048 > 0 THEN 65536 * 8 ELSE 0 END — Bit 11
+ CASE WHEN Company_Options % 65536 & 4096 > 0 THEN 65536 * 16 ELSE 0 END — Bit 12
+ CASE WHEN Company_Options % 65536 & 8192 > 0 THEN 65536 * 32 ELSE 0 END — Bit 13
+ CASE WHEN Company_Options % 65536 & 16384 > 0 THEN 65536 * 64 ELSE 0 END — Bit 14
+ CASE WHEN Company_Options % 65536 & 32768 > 0 THEN 65536 * 128 ELSE 0 END — Bit 15

+ CASE WHEN Company_Options % 65536 & 1 > 0 THEN 65536 * 256 ELSE 0 END — Bit 0
+ CASE WHEN Company_Options % 65536 & 2 > 0 THEN 65536 * 512 ELSE 0 END — Bit 1
+ CASE WHEN Company_Options % 65536 & 4 > 0 THEN 65536 * 1024 ELSE 0 END — Bit 2
+ CASE WHEN Company_Options % 65536 & 8 > 0 THEN 65536 * 2048 ELSE 0 END — Bit 3
+ CASE WHEN Company_Options % 65536 & 16 > 0 THEN 65536 * 4096 ELSE 0 END — Bit 4
+ CASE WHEN Company_Options % 65536 & 32 > 0 THEN 65536 * 8192 ELSE 0 END — Bit 5
+ CASE WHEN Company_Options % 65536 & 64 > 0 THEN 65536 * 16384 ELSE 0 END — Bit 6
+ CASE WHEN Company_Options % 65536 & 128 > 0 THEN -2147483648 ELSE 0 END AS MLSB_Value — Bit 7

, CASE WHEN Company_Options & 65536 * 256 > 0 THEN 1 ELSE 0 END AS Opt01 — Bit 24
, CASE WHEN Company_Options & 65536 * 512 > 0 THEN 1 ELSE 0 END AS Opt02 — Bit 25
, CASE WHEN Company_Options & 65536 * 1024 > 0 THEN 1 ELSE 0 END AS Opt03 — Bit 26
, CASE WHEN Company_Options & 65536 * 2048 > 0 THEN 1 ELSE 0 END AS Opt04 — Bit 27
, CASE WHEN Company_Options & 65536 * 4096 > 0 THEN 1 ELSE 0 END AS Opt05 — Bit 28
, CASE WHEN Company_Options & 65536 * 8192 > 0 THEN 1 ELSE 0 END AS Opt06 — Bit 29
, CASE WHEN Company_Options & 65536 * 16384 > 0 THEN 1 ELSE 0 END AS Opt07 — Bit 30
, CASE WHEN Company_Options < 0 /* Negative */ THEN 1 ELSE 0 END AS Opt08 — Bit 31

, CASE WHEN Company_Options & 65536 * 1 > 0 THEN 1 ELSE 0 END AS Opt09 — Bit 16
, CASE WHEN Company_Options & 65536 * 2 > 0 THEN 1 ELSE 0 END AS Opt10 — Bit 17
, CASE WHEN Company_Options & 65536 * 4 > 0 THEN 1 ELSE 0 END AS Opt11 — Bit 18
, CASE WHEN Company_Options & 65536 * 8 > 0 THEN 1 ELSE 0 END AS Opt12 — Bit 19
, CASE WHEN Company_Options & 65536 * 16 > 0 THEN 1 ELSE 0 END AS Opt13 — Bit 20
, CASE WHEN Company_Options & 65536 * 32 > 0 THEN 1 ELSE 0 END AS Opt14 — Bit 21
, CASE WHEN Company_Options & 65536 * 64 > 0 THEN 1 ELSE 0 END AS Opt15 — Bit 22
, CASE WHEN Company_Options & 65536 * 128 > 0 THEN 1 ELSE 0 END AS Opt16 — Bit 23

, CASE WHEN Company_Options % 65536 & 256 > 0 THEN 1 ELSE 0 END AS Opt17 — Bit 8
, CASE WHEN Company_Options % 65536 & 512 > 0 THEN 1 ELSE 0 END AS Opt18 — Bit 9
, CASE WHEN Company_Options % 65536 & 1024 > 0 THEN 1 ELSE 0 END AS Opt19 — Bit 10
, CASE WHEN Company_Options % 65536 & 2048 > 0 THEN 1 ELSE 0 END AS Opt20 — Bit 11
, CASE WHEN Company_Options % 65536 & 4096 > 0 THEN 1 ELSE 0 END AS Opt21 — Bit 12
, CASE WHEN Company_Options % 65536 & 8192 > 0 THEN 1 ELSE 0 END AS Opt22 — Bit 13
, CASE WHEN Company_Options % 65536 & 16384 > 0 THEN 1 ELSE 0 END AS Opt23 — Bit 14
, CASE WHEN Company_Options % 65536 & 32768 > 0 THEN 1 ELSE 0 END AS Opt24 — Bit 15

, CASE WHEN Company_Options % 65536 & 1 > 0 THEN 1 ELSE 0 END AS Opt25 — Bit 0
, CASE WHEN Company_Options % 65536 & 2 > 0 THEN 1 ELSE 0 END AS Opt26 — Bit 1
, CASE WHEN Company_Options % 65536 & 4 > 0 THEN 1 ELSE 0 END AS Opt27 — Bit 2
, CASE WHEN Company_Options % 65536 & 8 > 0 THEN 1 ELSE 0 END AS Opt28 — Bit 3
, CASE WHEN Company_Options % 65536 & 16 > 0 THEN 1 ELSE 0 END AS Opt29 — Bit 4
, CASE WHEN Company_Options % 65536 & 32 > 0 THEN 1 ELSE 0 END AS Opt30 — Bit 5
, CASE WHEN Company_Options % 65536 & 64 > 0 THEN 1 ELSE 0 END AS Opt31 — Bit 6
, CASE WHEN Company_Options % 65536 & 128 > 0 THEN 1 ELSE 0 END AS Opt32 — Bit 7

from DYNAMICS..SY01500

/*
1 Use Shipping Method when Selecting Default Tax Schedule
2 Calculate Terms Discount Before Taxes
3 Enable Intrastat Tracking
4 Separate Payment Distributions
5 Merge Trade Discount and Markdown Distributions in Sales
6 Merge Trade Discount Distributions in Purchasing
7 Calculate Tax Rebates
8 Enable Posting Numbers in General Ledger
9 Allow Negative Debits and Credits in General Ledger
10 Enable GST for Australia
11 Enable Tax Date
12 Enable Shipping Document in Sales Order Processing
13 Enable Reverse Charge Taxes
14 Calculate Taxes in General Ledger
15 Allow Summary-Level Tax Edits
16 Require Tax Detail Totals to Match the Pre-Tax Amount
17 Specify Tax Details for Automatic Tax Calculation
18 Enable VAT Return
19 Enable EU Transaction Tracking
20 Enable DDR and European Electronic Funds Transfer
21 Enable Payables EFT
22 Enable Canadian Tax Detail
23 Exclude Tax in Inventory Cost when Tax is Included In Price
*/

/* Copyright © Microsoft Corporation. All Rights Reserved. */
/* This code released under the terms of the */
/* Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) */