#FabrikamDay How to quickly update the sample data to 2027

In case you missed it, yesterday was Fabrikam Day for the Microsoft Dynamics GP community. This was the 12^th April 2017, the date that the sample company’s, Fabrikam, data is based around.

For more information about Fabrikam Day and the sample company have a look at my article from yesterday:

• #FabrikamDay is today or is it?

In yesterday’s article I suggested that we should run a script to move the sample company forward another ten years, but I was not planning to do anything about it.

Then my friend and fellow GPUG All Star, Shawn Dorward (@ShawnMDorward), emailed me and suggested that it was important for the sample company to be in the future rather than the past due to the perception it provides about the Microsoft Dynamics GP product.

So I thought “Why not write the scripts needed?” …..

Using the system resource tables I was able to obtain all the columns in user tables of datetime datatype. With a little filtering to only include tables with data and exclude certain columns and tables that should not be changed, I built a temporary table containing table, column and the record count for each of the years between 2010 and 2019 in the data. Using the temporary table, the script generates update statements and executes them.

This method worked fine for the company database, but there is a possibility of company based data in the system database as well. So a variation of the script was created which only works for tables in the system database which have the Company ID, Intercompany ID or Company Name columns in them and restricts the columns to the values for the sample company.

These three scripts were placed into GP Power Tools SQL Execute scripts, along with the Dex.ini Configuration setting to adjust the sample data date, and exported as the xml file attached to the bottom of this article.

The scripts have been written in such a way that they will only work for the sample company and cannot harm data if they are executed against the wrong company database or against a system database which does not have the sample company installed. It also will only work once and will not find any data to update if run more than once.

Note: The scripts are compatible with Microsoft Dynamics GP 2013 or later.

To use the scripts you will need GP Power Tools to import the configuration file and execute the scripts.  On next login the sample company date will be updated automatically.

If you don’t have GP Power Tools, that is not an issue, just download it via my distribution partner, Mekorma, and contact sales@Mekorma.com to request trial keys (for customers) or NFR (Not for Resale) keys (for partners).

For more information visit the GP Power Tools Portal at:

• http://WinthropDC.com/GPPT

Download the GP Power Tools Configuration Settings file from here.

Thanks to Shawn for his help with testing.

Enjoy

David

13-Apr-2017: Added second Company Database Script to update Year, Open Year and History Year fields.

This article was originally posted on http://www.winthropdc.com/blog.