Dynamics NAV – Query and Update System Indicator across multiple companies using SQL Script

Tony Zhang - Friday, July 24, 2015

Howdy from Dynamics Southwest!

Starting from NAV 2009 R2, System Indicator can be used to differentiate between a production environment and a test environment, or identifying the companies with in the same instance, by displaying database, company name with coloring. For how to use the System Indicator, please refer to the online help: https://msdn.microsoft.com/en-us/library/hh879446(v=nav.80).aspx.

In addition, Waldo covered this feature in the article http://dynamicsuser.net/blogs/waldo/archive/2011/01/10/nav-2009-r2-system-indicator.aspx, where the System Indicator Style codes get visualized.

Because the System Indicator is configured at company level, in case you have many companies, this can be a pain to consistently set this up across all companies contained in the database / instance. The following queries are meant to address this challenge.

/*System Indicator: 0-None; 1-Custom Text; 2-Company Information; 3-Company; 4-Database; 5-Company+Database

System Indicator Style: background/font: Accent1-Red/White; Accent2-Bule/White ; Accent3-Green/White; Accent4-Olive/White Accent5-Purple/White ; Accent6-Black/White ; Accent7-Yellow/Black ; Accent8-Yellow green/White ; Accent9-White/Black */

--Query the System Indicator Setup
DECLARE @QUERY VARCHAR(MAX)
; With CompanyName AS (SELECT REPLACE(Name, '.', '_') Name FROM Company)
SELECT @QUERY = STUFF((
    SELECT CHAR(13) + 'UNION ALL' + CHAR(13) + 'SELECT Name, [System Indicator], [System Indicator Style] FROM [' + Name + '$Company Information]'
    FROM CompanyName
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')
EXEC (@QUERY)
--Update the System Indicator Setup, in this case style 3 (Green) is chosen to indicate this is a production database
DECLARE @SQL VARCHAR(MAX)
; With CompanyName AS (SELECT REPLACE(Name, '.', '_') Name FROM Company)
SELECT @SQL = 'UPDATE [' + STUFF((
    SELECT CHAR(13) + ';' + CHAR(13) + 'UPDATE [' + Name + '$Company Information] SET [System Indicator]=5, [System Indicator Style]=3'
    FROM CompanyName
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')
EXEC (@SQL)
In case you need to setup system indicator differently for the companies, you can change the EXEC to  PRINT, as shown below. So it prints the update SQL statement, based on which you can modify.
DECLARE @SQL VARCHAR(MAX)
; With CompanyName AS (SELECT REPLACE(Name, '.', '_') Name FROM Company)
SELECT @SQL = 'UPDATE [' + STUFF((
    SELECT CHAR(13) + ';' + CHAR(13) + 'UPDATE [' + Name + '$Company Information] SET [System Indicator]=5, [System Indicator Style]=3'
    FROM CompanyName
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')
PRINT (@SQL)


One caveat is that you might need to Refresh the Company Information page to let the change take effect. Refreshing in one company takes care the changes to all other companies. Or you can restart the NAV instance.

NAV Company Information Refresh

Hope you find this helpful. If you have any suggestion, please drop a line here.

Auf Weidersehen!