Buttons not persisting size in Dynamics GP add-in

Steve Endow, posted yesterday on Buttons mysteriously resize on VS Tools Dynamics GP Forms -- VST template bug?This could have a big impact as I’m now working against GP2013R2 and so I set about investigating the issue. Thanks to Steve for sending me a ZIP of his example, from that I confirmed the same buggy behaviour on my environment, what is more, I also confirmed it on my own projects in my environment.

A big thanks Steve for pointing this issue out, here are my findings.

Reproduce the issue


Start new Dynamics GP Add-in Project

image

Add a form into the project

image

Drag two buttons onto the form

image

Resize the buttons so one is significantly larger than standard and one is much smaller than standard.

image

Save the form Form

close the form down, then open it again in the designer view….

image

Yes the buttons have defaulted to “default” size.

I have tested this with Visual Studio 2012 & 2013 as I remembered running Dynamics GP Tools is not technically supported for VS2013.

This is an interesting behaviour!

Testing

None of the following testing helped:

  • Using Visual Studio 2012
  • Applications.Dynamics / Microsoft.Dexterity.Bridge / Microsoft.Dexterity.Shell dlls from other copies
  • Downloading and installing latest Visual Studio Tools for GP including GP2013R2
  • Trying class library projects

To track down what was going wrong I jumped into the designer file, thinking that the design time values were not being persisted in the DynamicsGPForm1.Designer.cs

image

From the above screen shot, the sizes are as expected, one button longer horizontally than the other. Clearly the designer is ok, so why this behaviour? Forms that inherit from the type DexUIForm (i.e. “Microsoft Dynamics GP Forms” from project >> Add New Item), have some extra goodness in the designer “tray area”, as shown below. One of those is the dexButtonProvider. This component grabs the buttons in the form and adds some extra properties to them, it allow the developer to give the users the same experience from the buttons in an add-in, as they would have from the native GP Dexterity buttons.

-so this component is overriding the drawing of the button, there we have a lead to the answer.

image

The DynamicsGPForm1.resx file holds the form resources. If you are “in the know”, then you will know that these providers are persisting design time state through the form resources file, as seen from a snippet below:

<metadata name="dexButtonProvider.TrayLocation" type="System.Drawing.Point, System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
<value>107, 17</value>
</metadata>
<metadata name="dexDefaultColorsProvider.TrayLocation" type="System.Drawing.Point, System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
<value>260, 17</value>
</metadata>
<metadata name="dexLabelProvider.TrayLocation" type="System.Drawing.Point, System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
<value>450, 17</value>
</metadata>

We would expect to be seeing a XML fragment for each button in here, but it is missing. The expected missing fragment would like this, for button1;

<metadata name="button1.DexDefaultsSet" type="System.Boolean, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<value>True</value>
</metadata>


If you paste this code in before the final </root> element in the resource file, save and open the form again, amazingly the label size is displayed correctly.
 
The problem seems to be that at design time, when the button is added to the form, the button provider is not writing the information into the resource file that it needs to bind itself to the button correctly.
 

Workaround

It turns out there is a work around that forces the form to emit this meta data into the resource file. Simply flip the “Button Type on DexButtonProvider” property of each button on the form. Do this by after creating the button, set the value other than “Standard” and back again to “Standard”.
Now save the form and check the form resource file. The XML that was missing has been generated for the buttons, and it is confirmed by re-opening the form, the buttons will now retain their designed sizes.

image

 

Conclusion

There is an issue with both Steve and My environments, or there is a bug in the button provider when used in GP2013R2 and .NET 4.5. I have a work around for now, but may come back to this out of curiosity, perhaps reflecting into the button provider to find the root cause.

Find Custom SQL Triggers before Dynamics GP upgrade

imageBe aware that any custom created SQL triggers in the Dynamics GP database that have been created outside of Dynamics GP will be dropped at time of upgrade from one version of GP to another. Custom triggers are a technique SQL Server Admins might use to, for example default a field in GP, without using a GP application modification. Introducing triggers in GP can cause application malfunctions, so please read up on the subject before attempting such an action.

Custom eConnect stored procedures also suffer this fate so also grab back ups for those before any upgrade

Pre-Upgrade

  • Identify the custom triggers for each database
  • Script out the custom triggers using SQL Server Management Studio

Post-Upgrade

  • Run the create script from the pre-upgrade step to recreate SQL triggers in the database.

In an ideal world, your documentation will have details of these triggers, and the trigger create scripts are in your server build scripts, locked away safely in source control –right?

For those of us in the real world, the script below can be used to group the create dates of triggers in a database together, look for where the rank changes on the far right column. As the majority of triggers are created together when products are installed, the ad-hoc created triggers stand out as being in their own ranking groups.

-- Script will list all triggers in the current database
-- Custom SQL triggers are removed at upgrade by the upgrade
-- Script triggers out to recreate them post upgrade
-- The rank column can be used to find where triggers have been manually addded
-- those triggers will usually be groups together by that ranking
-- Remember to run for each company and the DYANMICS database too
SELECT
sysobjects.name AS trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name AS table_schema
,OBJECT_NAME(parent_obj) AS table_name
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled],
sysobjects.crdate as [Trigger Created],
RANK() OVER ( order by DATEADD(dd, 0, DATEDIFF(dd, 0, sysobjects.crdate)) desc) [Rank of Trigger Created Date]
FROM sysobjects

INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid

INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id

WHERE sysobjects.type = 'TR'
order by sysobjects.crdate desc

Result

image

How to script the triggers out

Once they have been identified them, use the “generate scripts” by a right click of the database node in SQL Server Management Studio object explorer, to script all triggers out. It is then possible to search through the generated script for the term “CREATE TRIGGER” and extract the triggers of interest into another TSQL file to run after the upgrade.

image

To do this, script out all the tables

image

On the options before you finish the script wizard, in the tables section, select to allow scripting of the triggers.

image

Beware DEX_ROW_TS for data synchronisation

DEX_ROW_TS is a SQL server database field. It is found on some tables in Dynamics GP ERP system(since V10). The field contains a UTC timestamp that is “stamped” whenever that row in the database is changed, setting it to current time and date. A blog entry from David Musgrave, Understanding how Microsoft Dynamics GP works with Microsoft SQL Server, says that this was introduced to “help with Business Data Catalog (BDC) indexing for SharePoint searches”.

Run this script from Tim Foster, to see all the tables in the GP database that use it;

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%DEX_ROW_TS%'
ORDER BY schema_name, table_name;
 
Executing this script yields the following tables from using a test GP2010 database (including the DYNAMICS & Company database):
 
Figure: Tables containing DEX_ROW_TS
MC40200 GL00100 IV00101 PM00200 POP10100 RM00101 SOP10100 SY01200
  GL10000 IV00107 PM00300 POP10110 RM00102 SOP10200 UPR00100
  GL12000 IV00108 PM10000 POP30100 RM00301 SOP30200  
  GL20000 IV10000 PM20000 POP30110 RM00303 SOP30300  
  GL30000 IV30200 PM30200   RM10301    
  GL32000 IV40201     RM20101    
    IV40202     RM30101    
    IV40800          

A database trigger is responsible for keeping the timestamp updated on changes. Below you can see the trigger that works against the table MC40200.
CREATE TRIGGER [dbo].[zDT_MC40200U] 
ON [dbo].[MC40200]
AFTER UPDATE
AS
set nocount on
BEGIN
UPDATE dbo.MC40200
SET DEX_ROW_TS = GETUTCDATE()
FROM dbo.MC40200,
inserted
WHERE MC40200.CURNCYID = inserted.CURNCYID
END set
nocount off

As can be seen from the script above, the timestamp is updated with the current server UTC time when a row insert occurs. There are similar triggers for updates to the rows. The time used is the time of the SQL server, set by using the TSQL function GETUTCDATE(). It is worth noting that this date time is with no time zone offsets. It is easy to write SQL scripts that may break due to local time zones, remember to take this into account.

Using DEX_ROW_TS for data synchronisation

If the need arises to keep the Dynamics GP data replicated to a CRM system or say an ecommerce website in Azure, perhaps a data warehouse, then the field becomes very useful. The value of DEX_ROW_TS since the last sync can be compared checked across all the tables and allow the selection of only the rows that have changed and thus only those rows may be selected for sync or push to The Cloud.

Don’t get too excited, I’m about to put the fly in the ointment. Using the field DEX_ROW_TS is very useful, but there are issues with relying on it too much…

Only some tables have the DEX_ROW_TS column on them

It soon becomes apparent that the field DEX_ROW_TS is not ubiquitous over all tables (as was seen from the results of the earlier database query). Although it exists on many of the common tables used in integrations, it is also lacking from so many others. Unfortunately once it is not on one table that requires synchronisation, then a another solution needs to be sought for identifying changed rows.

Table IV00105, used in price list setup is an example of a table lacking the column.

image

Perhaps it is possible to infer the need to re-sync a record in some cases, from the change in a related record in a table that does contain the timestamp however treading down this route means introducing a lot of logic and database work for each update.

Tables can update the time stamp on different table, even if it didn’t change

YES, that is correct, the time stamp can lie! Tables such as Item Master are updated by any change in the Item Quantity table for the same item!

By scripting out the tables and looking at the triggers, it is found that six tables have cross table updates of this nature. A operation on a row in these tables will update its own time stamp, but also crucially it will update the timestamp of another table too, even if nothing has changed in that other table’s row!

All the occurrences where there are interdependencies between updates in one table and update of DEX_ROW_TS column updates in another table are illustrated below. All of these are in the company database, none are in the DYNAMICS database.

Table Source of Trigger Trig Type Time Stamp Updated In Table
[IV00102] – Item Quantity Master INSERT IV00101 – Item Master
[IV00102] – Item Quantity Master DELETE IV00101 – Item Master
[IV00102] – Item Quantity Master UPDATE IV00101 – Item Master
[IV00104] – Item Kit Master INSERT IV00101 – Item Master
[IV00104] – Item Kit Master UPDATE IV00101 – Item Master
[IV00104] – Item Kit Master DELETE IV00101 – Item Master
[IV00107] – Item Price List Options UPDATE IV40800 - Price Level Setup
[IV00108] – Item Price List INSERT IV40800 - Price Level Setup
[IV00108] – Item Price List INSERT IV00107 – Item Price List Options
[IV00108] – Item Price List DELETE IV40800 - Price Level Setup
[IV00108] – Item Price List DELETE IV00107 – Item Price List Options
[IV00108] – Item Price List UPDATE IV40800 - Price Level Setup
[IV00108] – Item Price List UPDATE IV00107 – Item Price List Options
[IV40202] – U of M Schedule Detail Setup INSERT IV40201 – U of M Schedule Setup (header)
[IV40202] – U of M Schedule Detail Setup DELETE IV40201 – U of M Schedule Setup (header)
[IV40202] – U of M Schedule Detail Setup UPDATE IV40201 – U of M Schedule Setup (header)
[RM00102] - Customer Master Address File INSERT RM00101 - RM Customer Master
[RM00102] - Customer Master Address File DELETE RM00101 - RM Customer Master
[RM00102] - Customer Master Address File UPDATE RM00101 - RM Customer Master

 

Be aware it is not just synchronisation where this can be an issue. Perhaps there are SQL jobs sending reports when rows have been changed, in the customer set up records. These could be wrong due to corruption from changed addresses in the address table.

SQL Server Change Tracking

If you use SQL server change tracking, then you may also experience the same misleading update. Obviously the cross table update would be logged as an update to both tables by SQL server change tracking. At least it is possible to query for the columns updated by using a column bitmask with change tracking.

Customer Created Custom SQL Triggers

A blog post by Milan of Merit Solutions highlights another danger that is introduced by the triggers of the DEX_ROW_TS , read it here Dynamics GP Workflow Tips for DEX_ROW_TS.

Essentially be careful that triggers may run twice as the triggers that keep the time stamp updated are operating on the same table that triggered the trigger (follow me?), a loop could be accidentally caused. The blog proposes the use of (NOT UPDATE (DEX_ROW_TS)) and sp_settriggerorder  to prevent this happening and manage the trigger order, if required.