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.
- 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.
- On the next screen, select only the "Id", "Name" and "ParentRoleId" columns. You won't need the rest.
- 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.
- Leave the "ID" field blank (SF populates that when you make your insert)
- 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.
- 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.
- 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.
- Manipulate CSV, replacing existing ParentRoleID SF ID values with Role Name string values
- Edit CSV by hand, typing in the name of the Role in the ParentRoleID column.
- Reverse the process, changing all strings in the ParentRoleID column to their matching SF ID values.
- 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.
- 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.
- Insert the new roles using the separate file and they will all be given brand new SF IDs from the target system.
- 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.
- 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.
- Take your "target" CSV and sort it by Name as well.
- 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.
- Use your search/replace script to turn all those parent roles names into their new SF IDs from the "target" system.
- 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.
- Take an export from both your systems
- Using the common ID values between the two CSVs, use your search/replace script to match records in "target" CSV with the "source" CSV.
- 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.
- 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.
- Take an export from both systems in their current state.
- Use your search/replace script to modify the "source" CSV and turn all ParentRoleIDs into their matching Role name.
- Then use the same script and match up role names between the two CSVs.
- 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.
- 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)
- 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.
- 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.
- Create a temporary role to store your roles that will be marked for deletion.
- Make a note of what the SF ID is for this temporary role.
- 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".
- Modify the ParentRoleID of all of these roles and set it to your temporary role.
- 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.