#FabrikamDay How to quickly update the sample data to 2027


David Meego - Click for blog homepageIn case you missed it, yesterday was Fabrikam Day for the Microsoft Dynamics GP community. This was the 12th 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:

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:

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.

Advertisements

3 thoughts on “#FabrikamDay How to quickly update the sample data to 2027

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s