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!

In this post we look at the dangers of choosing to use DEX_ROW_ID as a unique pointer to a record.

Don’t use DEX_ROW_ID as a persistent row identifier

Currently there is a project in progress to integrate Microsoft Dynamics GP with nopCommerce, an eCommerce web application. This involves SQL replication and transformations between schemas. My colleague that is working on the SQL integration is doing a fantastic job of optimising the integration but has had lots of questions regarding GP tables.

A issue that keeps cropping up is the temptation of the alluring DEX_ROW_ID column, a column that is found in many of the GP tables. The temptation is to use this unique (it is a primary key in the database) column as a unique identified to a row in the tables. The temptation, for instance is to store this id on the distant side of an integration and use it as a joining column to maintain the synchronisation going forward, among over temptations.

More...