Converting Integers to Guids
30 March 2016
We know that Integers are not Guid’s they are very different data types. This causes a few complications with systems like Dynamics CRM that uses Guid’s for Primary Keys.
Most source systems will use an Integer as the Primary Key typically an auto-incrementing ID column on the source table.
If we could simply create a Guid from the Integer value we could keep the Integer ID column in our source system and link it to the Guid Primary Key in the Target System. No longer would we need to map the ID to a custom column or manage other lookup/conversion tables.
If we take a Guid value and replace the first 4 bytes with the bytes from an Integer we can then reliably convert an Integer to a Guid.
OK so we loose the fact that we can guarantee that every Guid value is unique, however are these values really likely to exist in our small applications?
Taking an Empty Guid and replacing the first 4 bytes with the values 1-9 produces.
Or Take an existing Guid and replacing the first 4 bytes with the values 1-9 produces
So as of Data Sync 3.0.992 release we have added a new Calculated Column function NEWGUID(int) and NEWGUID(guid, int) these functions help us produce the values above.
This version will merge your integer value with an Empty Guid.
This version will merge your integer value with a defined Guid value.
So now you can convert your source integer ID columns directly to a Guid to use with Dynamics CRM and map directly to the CRM Entity ID.