Data Cleansing in Sugar – Part 1 (Using Mass Update)
We all know that our database is only as good as the data we have in it. Sometimes we need to do some clean-up, so our data is more useful. Maybe we want to market to leads that have expressed an interest in a particular product. Can you easily filter for these records in your database?
Mass Update can be used to change existing data with new values. In this example, we have a custom field for the Contact’s product interest. We have dropdown values for both ‘Mobile’ and ‘Remote Access.’ These are really the same interest in our example, so we want to update the Contact records to have a consistent value (Remote Access), for easy filtering and marketing.
Note: If you are updating values in your Sugar system, be sure that any new values have been added to the drop-down list for selection before starting your Mass Update. If a new value is not in the dropdown list, it can be added in under the Dropdown Editor in SugarCRM Administration.
The next step would be to create a filter to find the Contacts that have “Mobile” entered as the product interest.
Use the checkbox in the top left to select all the records that match the criteria.
There are only 6 results in my demo database, but in a real database you might have more results than you can see on the List View, so you would have to use the ‘Select all records’ option as shown in blue below.
Once ‘Select all records’ has been selected, you’ll see a count of the records selected.
Select Mass Update from the drop-down list of actions in the upper left corner of the List View results.
Select the field to be updated, and the value with which it should be replaced. Select Update when you’ve completed the selections. The update will only affect the records that you identified in your filter.
You’ll get a confirmation when the update is done.
Housekeeping Note: You will likely want to remove the old value (‘Mobile’ in this example) from the drop-down list, so users don’t keep entering this value.
Using the Mass Update tool is very effective when you have less than 1,000 records to update. This is the maximum number of records that can be viewed by the filter in Sugar, and that can be updated using Mass Update.
If you have more than 1,000 records to update, there are a couple of methods to deal with this. One way is to refine your filters to identify smaller groups of records, and run Mass Update on these smaller groups. Another method is to export the records, update the data another way, such as in Excel, and then re-import the updated values. This method will be described in ‘Data Cleansing – Part 2.’
If you are a W-Systems customer, you can contact your W-Systems consultant or the support team for assistance with data cleansing.
It is always recommended to have a backup before doing data manipulation. If you are a Sugar On-Demand customer, Sugar support would be able to restore a backup in the event of a problem. If you have Sugar On-Premise, be sure the administrator has a current backup.