Register the new field in search tables

In this lesson, you register the profit margin field in the search tables with the proper property names. By registering the fields, the profit margin data displays as a filter name and sort criteria in product recommendations in the storefront.

About this task

In this lesson, a SRCHATTR_ID value of "10001" is used. All ID values that are less that 10000 are reserved by HCL Commerce. If the value "10001" is already used within your system, determine an available ID value for use in completing this customization.

Note: This lesson provides a procedure that demonstrates how to register the search attribute by using SQL statements. In this procedure, you must determine the SRCHATTR_ID for your SQL statements. You can also register your new field using Data Load utility to load the data and register the search attribute. By using the Data Load utility, the SRCHATTR_ID value is resolved for you. To learn how search attributes are registered, it is recommended that you review and perform the SQL procedure. If you are modifying the steps in this tutorial to create data that you are deploying to your store and are registering many ID values, use the Data Load utility to prevent ID duplication errors. To run the Data Load utility to load this data, you need to configure the utility to load the sample data within the following compressed file:

search_attribute_dataload.zip

For more information about configuring and running the Data Load utility, see Configuring and running the Data Load utility. Ensure that you modify the environment and database connection settings in the wc-dataload-env.xml configuration file before your run the utility.

Procedure

To register the profit margin field in the search tables with the proper property names, complete the following steps:

Register the new indexed profit margin property within the SRCHATTR database table by using the following SQL statement.
```
INSERT INTO srchattr (SRCHATTR_ID, INDEXSCOPE, INDEXTYPE, IDENTIFIER) 
VALUES (10001, '0', 'CatalogEntry', '_cat.ProfitMargin');
```
Where
- The "_cat" prefix in the value for the IDENTIFIER column indicates that the attribute is part of the catalog attribute.
- The value for the IDENTIFIER column does not need to be the same as the index field. The value should be meaningful to identify the attribute. In this tutorial, ProfitMargin is used.
Specify the usage and data type of the property within the SRCHATTRPROP table by using the following SQL statement.
```
INSERT INTO srchattrprop (SRCHATTR_ID, PROPERTYNAME, PROPERTYVALUE) 
VALUES (10001, 'merchandising-Filter-Numeric', 'ProfitMargin');
INSERT INTO srchattrprop (SRCHATTR_ID, PROPERTYNAME, PROPERTYVALUE) 
VALUES (10001, 'merchandising-Sort-Numeric', 'ProfitMargin');
```
Where
- ProfitMargin is the example index field name that is used in this tutorial. Ensure that the PROPERTYVALUE matches the indexed field name ("externalValue": "ProfitMargin") that you used in step 1 of Customize the user data field name mapping and search configuration to include custom data.
- The 'merchandising-Filter-Numeric' and 'merchandising-Sort-Numeric' property names are included to identify profit margin as both a filter and sort criteria in product recommendations. For more information about the properties that you can use with HCL Commerce search and the usage of the properties, see Adding catalog entry properties to search rule actions or targets.
Specify the display name of the added indexed catalog entry property in the SRCHATTRDESC table by using the following SQL statement.
```
INSERT INTO srchattrdesc (SRCHATTR_ID, LANGUAGE_ID, DISPLAYNAME, DESCRIPTION) 
VALUES (10001, -1, 'Profit Margin(%)', null);
```
After you add these values, Management Center can display 'Profit Margin(%)' as the label for the profit margin property when a user is working in the indicated language. You can add name for another language by using the similar SQL.
For example, to add another language display value for the profit margin indexed catalog entry property, you can use an SQL statement that resembles the following statement:
```
INSERT INTO srchattrdesc (SRCHATTR_ID, LANGUAGE_ID, DISPLAYNAME, DESCRIPTION) 
VALUES (10001, -1, 'Profit Margin(%)', null);
INSERT INTO srchattrdesc (SRCHATTR_ID, LANGUAGE_ID, DISPLAYNAME, DESCRIPTION) 
VALUES (10001, -2, 'translatedText', null);
```
Where translatedText is the translated text for the property in the language that you want the property to display.

Results

The profit margin displays in Management Center and allows you to set up product recommendations. Before you can view this information, you must restart your test server. If you are already logged in to Management Center, you must also logout, clear your browser cache, and log in again before you can view the property label.

The following image displays the Profit Margin(%) property label in the list of filter and sort criteria options.

Filter and sort criteria that displays profit margin.