Posted on Jan 25, 2012

UPDATE: In Spring '12, Role Hierarchies were added to the MetaData API, so you can use changesets or your IDE of choice to migrate role hierarchy changes.  No need for this whole mess!!!  Stop reading!! :)

I've been learning about and working with SalesForce over the past several months, and it has been both a joy and a frustrating experience.

(If you're already familar with SalesForce, you can skip to the next section...)

SalesForce, as a base product, is a CRM system (Customer Relationship Management) that helps your company manage your sales pipeline, sales team, customer issues/tickets, and lots more.  It's also a database-in-the-sky with a web-based interface, and it can be used to build almost any kind of data-driven business application, using its pretty flexible API, scripting and page-building framework.

The only problem is that, even amongst all this flexibility, SalesForce still kind of seems like a product in transition, and parts of its code and functionality seem to still hold on to or harken back to when SalesForce was just a CRM.  Either that or some features that havn't quite matured yet and are only partially implemented.  It's definitely something they're working to improve - every quarter there's a new release of the platform - and they're very open in communicating where they're at, but it can be frustrating when you run into one of these "walls" unexpectedly.

One of those things that's really frustrating is the Role Hierarchy, and migrating it from environment to environment in particular.

Production and Sandboxes: Depending on your product liscence, your SalesForce implementation may have the ability to create "sandbox" environments based off of the settings in your Production environment.  Sandboxes can be used to create new code, tweak configurations and permissions, or experiment with other ideas, prototypes or SalesForce add ons, without mucking around with your Production environment.  Once you're satisfied with your changes, you can promote them back into your production environment using various methods, primarily through the browser-based Change Set mechanism and the Eclipse IDE.

Role Hierarchies: This is basically an org chart, a "tree" structure that can define your company's hierachy of who manages who.  The Role Hierachy can also be used to control permissions as well. (ex. Parent "nodes" in the hierarchy can see all records created and managed by the child "nodes", but children can't necessarily see what the parents see)

In certain kinds of projects, Role Hierarchies can form the backbone of your permission system, and with some companies, the hierarchy can become quite big, numbering in the hundreds of nodes.

The Problem with Role Hierarchies

There are two problems with Role Hierarchies though.  The web UI, while easy enough to a non-technical user to add a couple of new roles, is a tedious bottle neck if you need to add more than a couple dozen.  The second problem is that after you're done all that work, the hierarchy cannot be easily migrated between sandboxes because it's not part of the current SalesForce MetaData API. (At least currently as of Winter '12 release. Check here for a List of Components available in the API and a little further down for those that are not in the API)

Fortunately, the information on the Role Hierarchy can be exported and imported using the Apex Data Loader.

Using the Apex Data Loader to speed up role creation and modifications

To help speed up Role Hierarchy creation, you can use the Data Loader to pull an export of your current hierarchy, make additions to the CSV, and then import the additions as an insert using the data loader.

Adding Roles

  1. First export the Role (UserRole) table from SalesForce.  Login to your system using the Apex Data Loader and go to Export.  Check the "Show all Salesforce objects" option and look for "Role (UserRole)" in the list.
  2. On the next screen, select only the "Id", "Name" and "ParentRoleId" columns. You won't need the rest.
  3. Hit "Finish" and then locate your exported CSV.

If  you have an existing hierarchy, delete the existing entries from the CSV and resave it under another name. (Important: when saving a CSV, make sure your spreadsheet program doesn't change the formatting, as OpenOffice sometimes does unless you check "Edit Filter Settings")

To make additions, add new rows to this new file you just saved.

  1. Leave the "ID" field blank (SF populates that when you make your insert)
  2. Give the Role a name. Important: its essential if all your roles have a unique name.  This helps admins know which role is which in the web UI when editing a user, and it's also required if you want to migrate your hierarchy using the process I outline below.
  3. Leave the "ParentRoleID" column blank for now. That column controls where in the hierarchy the role will sit, and it's populated with a SF ID.  If your new additions include both parent and child nodes, then you'll have to import them first so that SF IDs can be generated, before the child nodes can refer to their parents.
  4. Once you're done, import your new roles using the Data Loader and your new CSV file using the Insert function. (when mapping columns to fields, you'll only need to map the "Name" and "ParentRoleID" colums)

The new roles will appear at the top/root level of your hierarchy, since we didn't specify any ParentRoleID values.  To fix this, we need to re-export the Role table again. (repeat the steps above to get a new CSV)

Now things get a little tricky...ParentRoleID requires a SF ID, but these can get confusing and they're certainly hard to remember.  A better solution would be to type in the Parent Role's "Name" value instead, and then replace that string with corresponding SF ID before inserting.  This makes the sheet more "human readable".

I used a custom PHP script along with a CSV parsing library to do the replacements.  You can use whatever scripting language you're more comfortable with, or even Excel if it's possible to write a macro to do this process. (If you'd like to see my PHP code, leave a request in the comments and I'll try and make a generic version of the code)

After parsing and manipulating the CSV to replace the Parent Role SF IDs with Role Names, edit it by hand and type in the names of the Roles that are the parents of your new additions.  After this is complete, you will need to reverse the search/replace process, turning the Role names back into SF IDs, and then do an Update through the Apex Data Loader, using your new CSV.

Once you've updated SalesForce with your modified CSV, all those new Roles that were at the root of the tree will have moved into their correct locations.

In summary:

  1. Manipulate CSV, replacing existing ParentRoleID SF ID values with Role Name string values
  2. Edit CSV by hand, typing in the name of the Role in the ParentRoleID column.
  3. Reverse the process, changing all strings in the ParentRoleID column to their matching SF ID values.
  4. Update the Role table with this CSV using the Apex Data Loader.

Modifying existing roles

You would follow this same process to make mass modifications to an existing Role Heirarchy.  Using this search and replace process, you can move roles around to other parts in the tree, and just rename roles directly in the CSV file.  Just be careful when you're doing both moving and renaming of Roles at the same time, as the whole search and replace process is dependent on being able to match the names of the Parent Roles with their IDs.  Do the renaming first, import, export, and then do the Parent Role ID values.

Modifications that require deleting Roles

If you're just deleting a role, full stop, it's probably best to do the deletion from the web UI as all child roles will be deleted along with it.  But what if you're just deleting a parent role, but you want all the child nodes to be moved to another parent?

First, make a temporary role in your tree.  Just call it "Roles to be deleted" or something like that.

Follow the steps above to modify the child nodes so that appear under your new or pre-existing role.  After doing that, your old role's ID should no longer show up in the ParentRoleID column. (Do a search, just to make sure)  With the roles that you will be deleting, set their ParentRoleID to the ID of the temporary node that you just created.

After doing an update with this CSV, the child nodes should be moved under the correct parent role, and your old role will be moved under the temporary parent node.  From there you can use the web UI to delete the old roles, making sure to change any users who have the old role over to the new one. (Tip: if you click on the role's name in the web UI, you can see all the users that have that role.)

Migrating a Role Hierarchy

We can use all the steps above in order to migrate a hierarchy from one environment to another.  If you are starting out fresh and clean on your target system, that works best.

  1. Start by exporting your hierarchy from your "source" system, modify it to remove all values in the ParentRoleId column and save this as a separate file.
  2. Insert the new roles using the separate file and they will all be given brand new SF IDs from the target system.
  3. Re-export the roles. You now have a CSV from the source system that includes your hierarchy with parent and child nodes, and a CSV from the target system that has no parent/child relationships defined.  Both CSVs should have the same number of rows.
  4. Take your "source" CSV and use your search/replace scripts to change the ParentRoleID into a string.  Sort the whole CSV (using a spreadsheet program) by Name into alphabetical order.
  5. Take your "target" CSV and sort it by Name as well.
  6. Now copy the ParentRoleID column from the "source" CSV into the same column in the "target" CSV.  You should now have the ParentRoleID colum in your "target" CSV populated with all the parent roles, represented by their role names.
  7. Use your search/replace script to turn all those parent roles names into their new SF IDs from the "target" system.
  8. Now you have CSV that you can use to run an update through the Apex Data Loader on your "target" system, and move all the new roles into their proper parent-child relationship.

Migrating changes in a hierarchy into a pre-existing hierarchy

If you are trying to migrate a modified hierarchy (that includes additions, order changing and deleting of roles) into an existing one, that's trickier.

You will either have to destroy your target system's heirarchy first (delete all roles) and start fresh, or before making your updates on your "source" system, you do a refresh from your production system, so that both "target" and "source" are starting with the same set of roles with the same set of SF IDs. This assumes that your "source" is always a dev sandbox and your "target" is always a production system; if you need to do this for a sandbox to sandbox migration, I got nothing for ya. If you can't create a common key between the two systems (the SF ID), we can't tell what's changed, so you're out of luck and will have to migrate manually. :(

Before you can start this process, you will have to refresh your source system from Prod, and then begin your changes.

Once your changes are done on your "source" system, you will need to do something similar to the processes outlined above, but you will need to break down the inserts into separate steps and files.

First do role name changes.

  1. Take an export from both your systems
  2. Using the common ID values between the two CSVs, use your search/replace script to match records in "target" CSV with the "source" CSV.
  3. Once you have a match, copy the Name of the role from the "source" CSV into the "target" CSV.  Any new roles in your "source" system will be skipped because there's no matching SF ID.
  4. Now you can do an Update with the resulting CSV in order to modify all the role names.

The second step is to do all your role additions.  If you take both CSVs and sort by the ID column, you should find all the new additions will be at the tail end of the "source" CSV, and they will have IDs that don't exist in the "target" system.  Copy these rows out into a separate CSV, and follow the same steps above for adding new roles, although you should stop before setting the parent role ids for your new files.

The third step is to realign all the roles, both the new additions and the roles that will eventually be deleted.

  1. Take an export from both systems in their current state.
  2. Use your search/replace script to modify the "source" CSV and turn all ParentRoleIDs into their matching Role name.
  3. Then use the same script and match up role names between the two CSVs.
  4. Once you have a match, copy the Parent Role ID value from the "source" CSV (now a string, intead of a SF ID) into the conciding field in the "target" CSV.  Roles that don't exist in the "source" CSV (ie. those that were deleted) will not find a match and will not be replaced.
  5. The resulting file will have a mix of strings and SF IDs.  The roles that don't have strings are roles that will eventually be deleted.  Separate these out into a new CSV file, which will hold all the Roles that we'll be deleting. (Note: any roles that you need to delete that are at the top of your role hierarchy obviously won't have a Parent Role ID value.  If you have that situation, make sure you copy those roles out into this separate CSV as well)
  6. Use your search/replace script to modify the "target" CSV and change all the string Parent Role IDs back into SF IDs, using the ID values from the "target" system.
  7. Use the resulting CSV to do an update on the "target" system and now all the additions will be in the right place, and all the other roles that you moved around will also be moved into the right location.

The final step is to delete roles that are no longer in use.

  1. Create a temporary role to store your roles that will be marked for deletion.
  2. Make a note of what the SF ID is for this temporary role.
  3. Go back to the CSV you created earlier, in step 5 above, with all the roles that exist in the "target" system but not in the "source".
  4. Modify the ParentRoleID of all of these roles and set it to your temporary role.
  5. Update through the Data Loader as normal.

Now all the obsolete roles are collected under the temporary role you created.  Delete them manually using the web UI as normal.

Hopefully that all made sense!  Sorry for the length of the post and hope you find this useful.  As with anything you find on the internet, your mileage with this code may vary and may not work for every single use case, but hopefully there's something in here that will help you get started.

Good luck!

Categories: 

Tags: 

Comments

Hierarchy Filter on reports

Thanks for the all this information. Our organization is currently in the process of migrating to a brand new role hierarchy. One thing we recently is many reports have a field called "roleHierarchyFilter" field that shows up under the properties in the UI and in the meta data report files. When adding and deleting fields this filter is effected. What we haven't determined is what the change is, and if it will impact the results of our reports after the migration.

Do we need to try and manage the report filters? Or can we just rely on salesforce to do the right thing and reassign the filters based on the users' new roles?

Hmmmmm....

Hey Bill,

Sorry for the delay in responding to this. I'm not familar with the Report field "roleHierarchyFilter", so I'd have to look into it and see just what it holds and what it does exactly.

I suspect though that, at a database level, SF is smart enough to pick up the changes in the role hierarchy that you made using the dataloader, and cascade those changes out to other objects and fields that use the hierarchy or refer to it.

Incidentally, with the Spring '12 release, SF added Role Hierarchies to Change Sets, so you no longer have to go through this ridiculous process I outlined above. :) Sharing Settings, Field-level security on standard fields, a few other important things still can't be migrated and need to be done using the data loader or manually, but we're getting there. :)

roleHierarchyFilter

BTW. Just so you know, salesforce is not smart enough to update this reporting field. Ultimately, it is up to the users to manually go back in and fix their reports. At the time we had about 80,000 reports in our system. About 20,000 were broken by our role migration. It might be possible to use the metadata API to automatically fix this field. However, we settled for simply providing our users with instructions on how to fix their own reports, which in most cases is simply toggling the checkbox for this setting.

Not sure...

Hi Akshaya, First, make note that Role Hierarchies are now deployable using changesets or your IDE of choice through the MetaData API, no need to do this whole mess of CSV parsing and searching/replacing. Regardless, I posted the PHP code below.

I'm not sure if it's possible with ANT, but quite possible. ANT is a powerful tool that I've only recently started to get into, but it's also pretty verbose, it might be easier to do this task in another language.

As for the PHP code, here's a bit of it cleaned up.  Please note that this was written in 2011 with some version of PHP, I don't remember, so you may not be able to just copy and paste and use it. Also, I don't really remember exactly how I used this, what my source CSVs looked like, etc.

Good luck, hope it's useful to you.

<?php /* Role Migrator Script * * This script helps prep a role table for migration to another system * It will: * - parse the csv and create an array of Roles names against IDs * - replace all SF ID values in the ParentRole column with the string that represents the parent * * After prep, this file should be loaded into the target system without the ParentRole mapped. * All roles will enter "flat" into the system. Then we do a new export, with the new ids from the new system. * Copy and paste the parent strings into this new table (be sure to sort the columns first so everything will line up) * Then using the new ids and strings, replace the parent role strings with the correct ids. * Do an upsert, and hopefully you've got your role structure. */ echo "<h3>Sales Force Role Migrator Utility v0.4</h3>"; include("parsecsv-0.3.2/parsecsv-enclose.lib.php"); // Debugging function function spit($obj=NULL) { if ($obj) { return "<pre>" . print_r($obj,1) . "</pre><br/>"; } else { return "<pre>Null object</pre><br/>"; } } // Stage one turns parent IDs into strings // Stage two turns strings back into parent IDs // [CHANGE THIS VALUE BEFORE RUNNING THE SCRIPT] $stage = "2"; // Roles mapping file $map_roles_name = "[INSERT THE NAME OF YOUR CSV HERE, WITH NO FILE EXTENSION; .CSV IS ASSUMED]"; if (file_exists($map_roles_name . "-cleaned.csv")) { echo "<p>Error: Output csv already exists; rename, move or delete to prevent overwriting of data.</p>"; exit; } // Grab the contents of the user table, create a new file if (!$handle = fopen($map_roles_name . ".csv","r")) { exit("Table file not found!"); } $contents = fread($handle, filesize($map_roles_name . ".csv")); fclose($handle); echo "Creating file...<br/>"; $target = fopen($map_roles_name . "-cleaned.csv", "w"); if (fwrite($target,$contents)) { echo "Done<br/>"; } else { echo "Something went wrong"; exit; } $csv = new parseCSV(); $csv->delimiter = ","; $csv->linefeed = "\r"; $csv->always_enclose = true; $csv->parse($map_roles_name . "-cleaned.csv"); // Create mapping arrays for Roles and Profile $map_roles_ids = array(); foreach($csv->data as $key => &$value) { // Clean up, parser is goofy $value["ID"] = str_replace("\n","",$value["ID"]); if ($stage == "1") { $map_roles_ids[$value["ID"]] = $value["NAME"]; } else { $map_roles_ids[$value["NAME"]] = $value["ID"]; } } function match($org_value, $replace_array) { $replace_value = trim($org_value); $check = $replace_value; foreach ($replace_array as $key => $value) { if ($key == trim($org_value)) { //echo "Replacing " . $value . " with " . $replace_value . "<br/>"; $replace_value = $value; break; } } echo $replace_value == $check ? "Value not replaced: " . $replace_value . "<br/>" : ""; return $replace_value; } echo "Parsing CSV...<br/>"; // Loop through the CSV... foreach($csv->data as $key => &$value) { // Replace values.... $value["PARENTROLEID"] = match($value["PARENTROLEID"],$map_roles_ids); // Clean up, parser is goofy $value["ID"] = str_replace("\n","",$value["ID"]); } //echo "<pre>" . print_r($csv->data,1) . "</pre>"; echo "Done"; $csv->save(); exit;