Attribute Manager

Attribute Manager

Attribute Manager is an installable tool to be used within XrmToolBox, which enable for addition, editing and deletion of attributes, including renaming and changing the type of an attribute.

It has replaced the plugin Local to Global Option Set Converter many years ago. Plugins are now called tools in XrmToolBox.

Example Videos

• Convert a text field to a Global Option Set Shows how to convert a Text field to a Global Option Set, as well as a walk-through of installing the Attribute Manager, and how some of the settings work.
• Convert a Local Option Set to a Global Option Set Shows how to convert a Local Option Set to a Global Option Set. Also shows how to handle any errors that occur while the tool is running.

Mapping original values to new values

Attribute Manager offers functionality to convert the current value stored in one type to a new value and possibly also in another type, using a mapping file created by you.

Example: The current value stores the name of a country in an attribute of type Text and you would like to use an Option Set, the text value can not be directly converted/cast to an option in an Option Set. With a mapping file, you can instruct Attribute Manager to map eg. text "Canada" to an option with value 124 (inspired by ISO 3166-1 Country Codes) and so on.

The Mapping File should have format as below:

Canada, 124
Denmark, 208

Each time a value is found in the text field, it will search for the value in the mapping file, and use the corresponding mapped value in stead.

Remove Existing Attribute

When an existing attribute is to be removed in CRM, the corresponding column will also be removed in the database. If the column is referenced in any database-views, -indexes or the like, you will have to manually "temporarily remove" the column from these views and indexes. You can locate these references called Enforced Dependencies using MSSMS > Server > Database > Tables > Table > View Dependencies on the base-table owned by CRM or a custom SQL-script to locate the relevant views and indexes and take action on this (in the example dbo.tc_myentityBase).

I have used the SQL-script below to locate the Enforced Dependencies by column. Unfortunately the script does not list relevant indexes - you have to find them on your own (or alter the script). Also the script will include the "public" view (in the example named dbo.tc_myentity) where the column nc_type should NOT be temporarily removed.

DECLARE @SchemaName sysname = 'dbo';
DECLARE @TableName sysname  = 'tc_myentityBase';
DECLARE @ColumnName sysname = 'tc_type';

SELECT
    @SchemaName + '.' + @TableName                                      AS [USED_OBJECT],
    @ColumnName                                                         AS [COLUMN],
    referencing.referencing_schema_name + '.' + referencing_entity_name AS USAGE_OBJECT,
    CASE so.type
        WHEN 'C' THEN 'CHECK constraint'
        WHEN 'D' THEN 'Default'
        WHEN 'F' THEN 'FOREIGN KEY'
        WHEN 'FN' THEN 'Scalar function' 
        WHEN 'IF' THEN 'In-lined table-function'
        WHEN 'K' THEN 'PRIMARY KEY'
        WHEN 'L' THEN 'Log'
        WHEN 'P' THEN 'Stored procedure'
        WHEN 'R' THEN 'Rule'
        WHEN 'RF' THEN 'Replication filter stored procedure'
        WHEN 'S' THEN 'System table'
        WHEN 'SP' THEN 'Security policy'
        WHEN 'TF' THEN 'Table function'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'U' THEN 'User table' 
        WHEN 'V' THEN 'View' 
        WHEN 'X' THEN 'Extended stored procedure'
    END                                             AS USAGE_OBJECTTYPE,
    so.[type]                                       AS USAGE_OBJECTTYPEID
FROM sys.dm_sql_referencing_entities
    (
        @SchemaName + '.' + @TableName,
        'object'
    ) referencing
    INNER JOIN sys.objects so 
        ON referencing.referencing_id = so.object_id
WHERE
    EXISTS
    (
        SELECT
            *
        FROM
            sys.dm_sql_referenced_entities
            (
                referencing_schema_name + '.' + referencing_entity_name,
                'object'
            ) referenced
        WHERE
            referenced_entity_name = @TableName
            AND 
            (
                referenced.referenced_minor_name LIKE @ColumnName   
                -- referenced_minor_name is sometimes NULL
                -- therefore add below condition (can introduce False Positives)
                OR
                (
                    referenced.referenced_minor_name IS NULL 
                    AND 
                    OBJECT_DEFINITION
                    (
                         OBJECT_ID(referencing_schema_name + '.' + referencing_entity_name)
                    ) LIKE '%' + @ColumnName + '%'
                )
            )
    )
ORDER BY
    USAGE_OBJECTTYPE,
    USAGE_OBJECT

I removed the column temporarily by using MSSQL > Server > Database > Tables > Table > Script Table as > ALTER As, commenting out the relevant column and execute the query. Then, after converting the attribute, I reverse it by uncommenting the column in the ALTER-scripts related to the affected objects.