We’ve talked many times before about the value of using Indexed Custom Fields (ICF) to juice your performance. It’s clearly important to think about this when you are going through initial design and configuration. But what about after deployment – when you start to figure out exactly what bits of data are being most heavily searched and reported on?

Lose not hope! Even in a live system, months or years after initial launch, you can make the move to ICFs with a little care and planning. And let’s face it – every CRM system is going to need some tuning after a while. Business needs evolve or sometimes change dramatically and your CRM application must keep up. So let’s dig into how you can make a slow dog field into a sprinter.

The Basics

OK, so let’s be clear – you don’t get something for nothing. The one big prerequisite here is that you must have an available ICF to move to. You can’t actually turn a plain-jane custom field into an indexed field. What we’re talking about is moving the values and replacing the field with an ICF, so you must have an ICF ready to use.

As an aside, keep in mind that there are levels of goodness when it comes to the fields in CRMOD. Basically three of them: ICFs, base fields, and custom fields.

ICFs are our super heroes. They’re pre-indexed so searching, reporting and listing with these fields will be darn fast. You know them and love them.

Base fields are the fields you see on day one when you get into CRMOD. These are put there for you to use, re-use or just ignore. Base fields are stored on the base table and that means that they are pretty fast as well.

Custom fields are what you get whenever you click “New Field” in the application customization area. These are great for storing data, but searching a lot of records using one of these could be a challenge. Custom fields are stored on an extension table, so using them as a search or filter means a lot of added work for the application.

So if you are all out of ICFs, you may consider re-using an existing Base field instead, as it may provide a bit of benefit over a custom field. If you aren’t sure anymore what field is what (because you renamed them all), you can always hit the “Rename Fields” button from the Field Setup screen to see the original labels of your fields.

Plan It!

Right upfront, you need to get a handle on how much work you’ll need to do so you can appropriately plan and resource the work.

Document the following:

- All page layouts containing the slow field

- All related information layouts containing the slow field

- All search layouts containing the slow field

- All workflows referencing the slow field

- All lists containing the slow field

- All reports / analyses containing the slow field

Put all of this in a spreadsheet. Check and double-check it. This should give you a sense of the scale of the work – do you need to update 10 lists and a 20 reports? Or is it hundreds of lists and thousands of reports?

The Process

OK, so what’s the process? How much time will it take? What will it cost?

Good questions, all. So let’s assume you’ve got your candidate field – a custom picklist. Everyone is using it to search for Accounts and everyone is complaining because it takes, like, days to get a result.

Let’s also assume you have an available Indexed Custom Picklist on the Account object. Great, you are on your way!

Step 1: Set up your ICF

If you are moving to a Indexed Short Text field, then this step isn’t necessary. For picklists, you’ll want to enter all the values so they exactly match the values in the existing field. You can do this manually or, if you can generate an import file, you can load a dummy account and choose the option to “add picklist values”. 

Keep in mind that you may need to translate the values as well, so plan accordingly if you have multi-language environment. Also, picklist entries have two values – the language-dependent value and the language-independent value. It will make life easiest if both values match what you have in your existing field. You can’t change the languge-independent code(LIC) – it gets set as the first entry made. So make sure you enter each value as the LIC. If the displayed value is different, you can always go back and change that later.

Step 2: Default Your ICF

In most cases I’ve been involved with, timing is a concern. You want to get your new field in place as seamlessly as possible and you certainly don’t want any user input being lost while you are shuffling things around.

So you can do all the work in the dark of night on a weekend, or you set up some simple default values and workflow to catch user inputs.

Add a default value on your ICF that simply grabs the value from the existing, non-indexed custom field. This will mean that any new records being created will have the correct value right away in the new field – even before you expose it to users.

You’ll also want to capture updates. For this, employ a “before modified record saved” workflow event to update the new ICF with the non-indexed field value. Beautiful, simple and completely transparent to users.

Step 3: Export your data

The workflow and default values are now doing their job to capture new and changed values, but we have a whole lot of records with no ICF value. To bring those up to speed (!) we’ll need to perform a batch update via import, mapping the old field value to the new field.

Exporting can be done a few ways – via a list, mass export, or through reports. Since we are updating ALL records, a list may not make much sense. Mass export is fine, though it dumps EVERY field into the file, and we don’t need all of that.

My personal preference is to use a report. I know, I know – you’ve been told not to use reports for data dumps. Totally agree. But for this one-time operation, I think it works fine. Since we’ve got our default value taking care of newly created records, we can build this as an historical report.

For most record types you have the ability to match imported records by Row Id. Previously you could match on key fields (Account Name + Location) or External Unique Id. Note that not all record types (such as Custom Objects) offer this option – check the Import screen for any record type to see what matching option is available.

You’ll need to include just two fields in your report – Row ID and the non-indexed field. Preview the report and choose “Download to csv” – save the file.

4. Import your field values

Now we’ve got a file that will allow us to run a mass update of all the existing records and set the new ICF field to the correct value.  Hopefully this is a process that is well known to you, but worth making a few points…

First, choose the right tool for the job. This largely depends on the volume of data. The web-based import is usually best, but it requires you to split your data into 30k or so chunks and run multiple uploads. For really large data sets, you may consider the data loader or even a custom web services tool.

Second, TEST. Regardless of the mechanism, test it out. Pull the first row or two from the file and run it in. Check the results, make sure it’s what you expected!

So the result of this action is that you now have a new ICF picklist which has selected values on each Account record that mirrors the value on the old custom picklist field. And with workflow in place, they should stay in sync.

Put it to Work

So far everything we’ve done is behind the scenes – users don’t need to even know this has happened and they are continuing to use the old slow-poke field.

Now we need to put the new field to work. Depending on the complexity of your implementation, this may involve a fair amount of work. Also, you’ll need to consider when this is done to minimize disruption to your end users.

The nut of it is swapping out one field for another – so re-naming the old field and then re-naming the new ICF and doing replacements wherever it appears.

Here are the areas you’ll want to focus on:

- Page Layouts

- Related information layouts (where Account is the related item)

- Search layouts

- Lists

- Workflows (any that reference the old field)

- Reports / Analytics

(This isn’t intended to be a prioritized list – that’s going to be up to you which is most important.) 

So, you may be asking, how long will this take? In my experience, we’ve been able to do all of the above with three people over a weekend. Most of the work was in reports – obviously the effort is a factor of the number of reports that reference the old field.

Oh, and a bit of clean-up: At this point, you can turn off the workflow and default values we put in earlier. Best is to remove these as soon as possible.

Communicate It!

For most users, they’ll wake up Monday morning, log-in and start doing their job. They will, of course, be ecstatic when they attempt that usual slow search and see the blindingly fast results… but otherwise they won’t notice a difference.

So should we just silently congratulate ourselves and move on? No! We need to let folks know what’s changed for two big reasons.

1. Custom personal reports – any users that create their own reports may have used the old field. They’ll need to replace it themselves.

2. Custom lists – ditto above. Users will need to update any personal lists.

Wrap-up

So there you go – it’s a process I’ve walked numerous customers through and while it can seem a bit daunting, it’s really not that bad and the payoff can be quite dramatic. Good planning makes all the difference.

Share and Enjoy:
  • Digg
  • LinkedIn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • Twitter
  • StumbleUpon
  • Technorati
  1. Ben on 15 Feb 11 7:40 am

    Great article, very helpful and informative. We are going through this process now…only wish it was a bit simpler! :)

    Thanks
    Ben

  2. Jason on 17 May 11 1:50 pm

    Nice outline and information on data maintenance. Overall concept can also be applied to moving any field to another field. Example is initial configuration was a picklist field but business requirements have change and they only need to use a text field. Also, Bulk Loader makes process a lot easier since you don’t have to worry about record count in your export. However, if you do need to segment export lists best field to use is “Modified: Date” because it is indexed across all record type, but any other index field should suffice as well.