Bulk updating user's preferences (UserSettings) in CRM: PART II

  I have split this post into 3 parts:
· Part One: Retrieving a spreadsheet with the list of available time zones and languages
· Part Two: Generating a spreadsheet for entering the user preferences for each user
· Part Three: Uploading the updated spreadsheet to update user preferences

Part Two: Generating a spreadsheet for entering the user preferences for each user

In Part One we gathered a list of available time zones and languages. Now we need to provide a spreadsheet were we can assign a time zone and language for each user. Note that this code requires part one to be executed first to populate the dictionaries in memory:

 

private static void CreateUserSettingsSpreadsheet(IOrganizationService service, string fileName)

{

    XmlDocument xDoc = new XmlDocument();

    xDoc.AppendChild(xDoc.CreateElement("Users"));

    QueryByAttribute usersQuery = new QueryByAttribute(SystemUser.EntityLogicalName);

    usersQuery.AddAttributeValue("isdisabled", false);

    usersQuery.ColumnSet = new ColumnSet("fullname");

    EntityCollection coll = service.RetrieveMultiple(usersQuery);

    foreach (Entity user in coll.Entities)

    {

        XmlNode userNode = xDoc.CreateElement("User");

        XmlNode userIdNode = xDoc.CreateElement("Id");

        XmlNode languageNode = xDoc.CreateElement("Language");

        XmlNode timeZoneNode = xDoc.CreateElement("TimeZone");

        XmlNode nameNode = xDoc.CreateElement("Name");

        userNode.AppendChild(userIdNode);

        userNode.AppendChild(nameNode);

        userNode.AppendChild(languageNode);

        userNode.AppendChild(timeZoneNode);

        xDoc.DocumentElement.AppendChild(userNode);

        userIdNode.InnerText = user.Id.ToString();

        nameNode.InnerText = user["fullname"] as string;

        UserSettings settings = RetrieveUserSettings(service, user.Id);

        languageNode.InnerText = settings.UILanguageId.HasValue ? LanguageCodeToName[settings.UILanguageId.Value] : string.Empty;

        timeZoneNode.InnerText = settings.TimeZoneCode.HasValue ? TimeZoneCodeToName[settings.TimeZoneCode.Value] : string.Empty;

    }

    xDoc.Save(fileName);

}

The code above should give you a nice XML (fileName) that when you open with Excel looks like this:

Id	Name	Language	TimeZone
efe78790...	Nelson Suddeth	English (United States)	US Eastern Standard Time
d0d010ae...	Maricela Speller	French (France)	US Eastern Standard Time
36d231c0...	Kathrine Minott	English (United States)	Pacific Standard Time
c7b26b5a...	Hugh Claybrook	French (France)	US Eastern Standard Time
b1990973...	Jessie Burgoon	English (United States)	Canada Central Standard Time
4dcfc7fe...	Cody Helfer	English (United States)	Pacific Standard Time
d58455...	Ted Tartt	English (United States)	Canada Central Standard Time
3f7d84e...	Saundra Willits	French (France)	Pacific Standard Time
2e271a...	Kelly Doster	French (France)	Canada Central Standard Time

Now you can edit in Excel which language and time zone to assign to each user. You can even provide the template to the business to gather personal settings for each user. Note that the file is currently stored as XML so you cannot add data validation to the cells. It would be nice to provide a dropdown for the language and time zone columns. You can save the file as an Excel spreadsheet and then add data validation to those columns using a validation list and populating it with the values we retrieved from Part One:

Note that providing the drop-down in Excel is an optional step and requires you to save the file in a different format (spreadsheet instead of XML). I’ve used the standard data validation feature in Excel to create the dropdown, and you can do the same for language too.

Now you can provide the spreadsheet to your customer / business to fill in the time zones and languages for each user J.