Working With Indexes

Overview

Sugar provides a simple method for creating custom indexes through the vardef framework. Indexes can be built on one or more fields within a module. Indexes can be saved down to the database level or made available only in the application for functions such as Import Duplicate Checking.

Index Metadata

Indexes have the following metadata options that can be configured per index:

Creating Indexes

Stock indexes are initially defined in the module's vardefs file under the indices array. For reference, you can find them using the vardef path of your module. The path will be  ./modules/<module>/vardefs.php.

Custom indexes should  be created using the Extension Framework. First, create a PHP file in the extension directory of your desired module. The path should similar to ./custom/Extension/modules/<module>/Ext/Vardefs/<name>.php.

In the new file, add the appropriate $dictionary reference to define the custom index:

  <?php

$dictionary['<module>']['indices'][] = array(
	'name' => '<index name>',
	'type' => 'index',
	'fields' => array(
		'field1',
		'field2',
	)
);

Note : For performance reasons, it is not recommended to create an index on a single field unless the source is set to non-db.

Once installed,you will need to navigate to Admin > Repair > Quick Repair and Rebuild to enable the custom index. You will need to execute any scripts generated by the rebuild process.

Removing Indexes

Stock indexes are initially defined in the module's vardefs file under the indices array. For reference, you can find them using the vardef path of your module. The path will be ./modules/<module>/vardefs.php.

Stock indexes should be removed using the Extension Framework. First, create a PHP file in the extension directory of your desired module. The path should similar to ./custom/Extension/modules/<module>/Ext/Vardefs/<name>.php.

In the new file, loop through the existing 'indices' sub-array of the $dictionary to locate the stock index to remove, and use unset() to remove it from the array.

Example

The following is an example to remove the idx_calls_date_start index from the Call module's vardefs.

First, create ./custom/Extension/modules/Calls/Ext/Vardefs/remove_idx_calls_date_start.php from the root directory of your Sugar installation on the web server. When creating the file, keep in mind the following requirements:

• The name of the file is not important, as long as it ends with a .php extension.
• The rest of the directory path is case sensitive so be sure to create the directories as shown.
• If you are removing the index for a module other than Calls, then substitute the corresponding directory name with that module.
• Ensure that the entire directory path and file have the correct ownership and sufficient permissions for the web server to access the file.

The contents of the file should look similar to the following code:

  <?php

$call_indexes = $dictionary['Call']['indices'];
$remove_index = "idx_calls_date_start";

foreach($call_indexes as $index_key => $index_item) {
    if( $index_item['name'] == $remove_index ) {
        unset($dictionary['Call']['indices'][$index_key]);
    }
}

Note : Removing the reference to the index from the module's indices array does not actually remove the index from the module's database table. Removing the reference from the indices array ensures that the index is not added back to the module's database table when performing any future Quick Repair and Rebuilds. The database index must be removed directly at the database level. On MySQL, with the current example, this could be done with a query like:

  ALTER TABLE calls DROP INDEX idx_calls_date_start;

Once installed,you will need to navigate to Admin > Repair > Quick Repair and Rebuild to remove the index from the $dictionary array. You will need to execute any scripts generated by the rebuilding process.

Creating Indexes for Import Duplicate Checking

When importing records to Sugar via the Import Wizard, users can select which of the mapped fields they would like to use to perform a duplicate check and thereby avoid creating duplicate records. The following instructions explain how to enable an additional field or set of fields for selection in this step.

Example

The following is an example to add the home phone field to the Contact module's duplicate check.

First, create ./custom/Extension/modules/Contacts/Ext/Vardefs/custom_import_index.php from the root directory of your Sugar installation on the web server. When creating the file, keep in mind the following requirements:

• The name of the file is not important, as long as it ends with a .php extension.
• The rest of the directory path is case sensitive so be sure to create the directories as shown.
• If you are creating the import index for a module other than Contacts, then substitute the corresponding directory name with that module.
• Ensure that the entire directory path and file have the correct ownership and sufficient permissions for the web server to access the file.

The contents of the file should look similar to the following code:

  <?php

$dictionary['Contact']['indices'][] = array(
     'name' => 'idx_home_phone_cstm',
     'type' => 'index',
     'fields' => array(
         0 => 'phone_home',
     ),
     'source' => 'non-db',
);

Please note that the module name in line 2 of the code is singular (i.e. Contact, not Contacts). If you are unsure of what to enter for the module name, you can verify the name by opening the ./cache/modules/<module_name>/<module_name>vardefs.php file. The second line of that file will have text like the following:

  $GLOBALS["dictionary"]["Contact"] = array (...);

The parameter following "dictionary" is the same parameter you should use in the file defining the custom index. To verify duplicates against a combination of fields (i.e. duplicates will only be flagged if the values of multiple fields match those of an existing record), then simply add the desired fields to the 'fields' array in the code example.

Finally, navigate to Admin > Repair > Quick Repair and Rebuild to enable the custom index for duplicate verification when importing records in the module.