#GPPT Copying Company Color Themes for a Test Environment

I have been asked a couple of times recently about how to copy the Company Color Themes for a test environment, so I thought I would document the answer in a blog article.

GP Power Tools has the ability to change the background colors within Microsoft Dynamics GP for each company. This feature currently only works for the desktop client, if you want it to work in the web client, vote for this MS Connect product suggestion.

The code in GP Power Tools that handles the Company Color Themes has been created to be as smart as possible. By storing the colors in both the company and system databases and checking both locations, the code can make sure that the color themes are displayed correctly.

This smart code becomes apparent when you start copying company databases around. For example:

• Creating a new company and copying an existing database into the new company. The color theme is stored in the company database and not in the system database, so the company data is used and the new company has the same them as the original company.
• Copying a live company database into an existing test company. The color theme is stored in the company database, but the test company’s color theme is also stored in the system database, so the system data is used and the company data is updated so that the test company keeps its own color theme.

However, there is a situation where the smart logic cannot help…. Instead of copying a live company into a test company on a single server, you duplicate the entire system to a test environment on a separate server.

In this situation, GP Power Tools does not identify that the server has changed and so the color themes (along with all the rest of the data) is unchanged.

The code below shows you how to query the color theme data in both the company and system databases for the 5 colors as well as the theme group and theme name (there should be 7 records per company).

The Transact SQL select statement below gets the theme from the company database:

select * from SY01401
where USERID = ' DEBUGGER'
and ((coDefaultType >= 22001 and coDefaultType <= 22005) or coDefaultType = 22011 or coDefaultType = 22012)
order by USERID, coDefaultType

The Transact SQL select statement below shows the themes for all companies from the system database. Note the company ID is shown after the comma in the USERID column:
select * from SY01402
where USERID like ' DEBUGGER,%'
and ((syDefaultType >= 22003 and syDefaultType <= 22007) or syDefaultType = 22013 or syDefaultType = 22014)
order by USERID, syDefaultType

Create your test environment and then after setting the color themes for your companies in the test environment, use the code to get the color theme settings from the system database.
We can then re-apply these settings to the system database each time you update the test environment from the live environment to restore back the test environment color themes. We only need to apply to the system database as those settings will override the company settings and be rolled down when the first user logs into a company.
Just create a script with all the update statements to set the values as desired, there should be 7 statements for each company. For example:
update SY01402 set SYUSERDFSTR = '10271438' where USERID = ' DEBUGGER,-1' and syDefaultType = 22003
update SY01402 set SYUSERDFSTR = '13031399' where USERID = ' DEBUGGER,-1' and syDefaultType = 22004
update SY01402 set SYUSERDFSTR = '3832227' where USERID = ' DEBUGGER,-1' and syDefaultType = 22005
update SY01402 set SYUSERDFSTR = '16777215' where USERID = ' DEBUGGER,-1' and syDefaultType = 22006
update SY01402 set SYUSERDFSTR = '15001331' where USERID = ' DEBUGGER,-1' and syDefaultType = 22007
update SY01402 set SYUSERDFSTR = 'Dynamics' where USERID = ' DEBUGGER,-1' and syDefaultType = 22013
update SY01402 set SYUSERDFSTR = 'Original - Dynamics' where USERID = ' DEBUGGER,-1' and syDefaultType = 22014

Once you have the script, just execute it after copying the databases to the test environment.
More Information
Also see the article below:

#GPPT Setting up GP Power Tools in a Development Environment

Hope you found this useful.
David
21-Sep-2021: Added article about setting up a Test/Development Environment.
This article was originally posted on http://www.winthropdc.com/blog.