The subject maps in CRM 2011 and any version of CRM is a complicated business. For some reason it is extremely difficult to get out and put in data into the subject map.
I needed to create an import for the subject tree, so the first thing I tried to do was an advanced find so I could export the current data to see what format it is in but you can do an advanced find using the Subject Tree. To make matters worse when you type in import/export Subject Tree or any of those words in any order you like you don’t get much information, a few scraps, a couple of forum posts but not many articles.
The Stupid thing is you need to export the current data if you want to import some data using the hierarchy structure in your current subject tree.
The way I found out how to get the information out of CRM 2011 was to write an SQL select against the SubjectBase table
SELECT [SubjectId]
,[Title]
,[Description]
,[ParentSubject] ‘Parent Subject’
,[FeatureMask] ‘Feature Mask’
FROM [hosk_MSCRM].[dbo].[SubjectBase]
This was the data I got out
SubjectId | Title | Description | Parent Subject | Feature Mask |
9BFDDA0E-DC2E-E011-9645-00155D106B02 | Construction | NULL | NULL | 1 |
CD0DC51E-DC2E-E011-9645-00155D106B02 | Finance | NULL | NULL | 1 |
I have changed the column names for ParentSubject and FeatureMask because you can save the results and headings into excel, save it as a csv file and then you can import this and use default mappings. You will have to do a search and replace on the NULL values if any.
Title | Description | Parent Subject | Feature Mask |
Utilities | 1 | ||
Food & Drink | 1 | ||
Automative, Transport | 1 | ||
Medical & Pharmaceutical | 1 |
To import some data into the root of the Subject Tree I imported the above (I removed the subjectID because this would be created when it was imported.
To import with parent values you will need to export the data again to get Parent Subject values that you want to use as parent. The easiest way to do this is to create the import files one level at a time I think. Here you can see I have done the second level, I got the parentId values from export the data.
Title | Description | Parent Subject | Feature Mask | |
Service Level 1 | 7718110F-AE2F-E011-AB7E-00155D106B02 | 1 | ||
Utilities level 1 | C0CE2263-955F-E011-82A4-00155D10A60A | 1 | ||
You can see that because I have named the column headings the same as the subject tree heading, CRM 2011 automatically maps to them. I found the heading names by going into Settings/System/Data Management/Templates for Data Import. You can then select the Subject to see what headings it is expecting.
Here is a picture of the subject tree
By the way, you do not need to have the GUID, the name is sufficient for the ‘Parent Subject’ field.
LikeLike
Thanks for the comment, I have found out recently the importing functionality in CRM has been improved and I am still making the transition from CRM 4
LikeLike
Any idea what does that Feature Mask = 1 mean and what other options do we have?
LikeLike
FeatureMask specify if the item must show or hide.
LikeLike
Here is a query to get the subjects level by level in order to import them
;WITH x AS
(
— anchor:
SELECT SubjectID, Title, ParentSubject, FeatureMask, [level] = 0
FROM SubjectBase WHERE ParentSubject IS NULL
UNION ALL
— recursive:
SELECT t.SubjectID, t.Title, t.ParentSubject, t.FeatureMask, [level] = x.[level] + 1
FROM x INNER JOIN SubjectBase AS t
ON t.ParentSubject = x.SubjectID
)
SELECT SubjectID, Title, ParentSubject, FeatureMask, [level] FROM x
ORDER BY [level]
OPTION (MAXRECURSION 32);
LikeLike
Hi, what about OnLine version? There is a way to export the subjects tree in order to reimport it in another instance? Thanks.
LikeLike
I don’t know a way to do this
LikeLike