Registering the new field in search tables

In this lesson, the profit margin field is registered in search tables with the proper property names so that profit margin displays as filter name and sort criteria in product recommendation.

About this task

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 id resolve is performed 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, it is recommended that you use the Data Load utility process to prevent Id duplication.

The benefit of using the Data Load utility is that you do not have to manually resolve the SRCHATTR_ID; the Data Load resolves the Id. If you have many search attributes to register, use this method.

Configure the Data Load utility. An example of configure the data load utility can be found in Configuring the Data Load utility. For completing this tutorial, use the sample data load file in the search_attribute_dataload.zip compressed file.
Download and extract the search_attribute_dataload.zip compressed file to a temporary directory, such as c:\search_attribute.
Modify the database connection information in the wc-dataload-env.xml file that is within the compressed file that you downloaded and extracted.
Stop the WebSphere Commerce server.
Run Data Load utility to register the search attribute
- dataload.bat c:\search_attribute\wc-dataload.xml
- ./dataload.sh [path to search_attribute dir]/wc-dataload.xml
- dataload.bat c:\search_attribute\wc-dataload.xml
Restart your WebSphere Commerce server.

Procedure

If you are already logged in Commerce Management Center, you must logout, clear your browser cache, and log in again before you create your product recommendations.
Register profit margin as a new indexed catalog entry property in the SRCHATTR table by executing the following insert statement.
```
INSERT INTO srchattr (SRCHATTR_ID, INDEXSCOPE, INDEXTYPE, IDENTIFIER) 
VALUES (10001, '0', 'CatalogEntry', '_cat.ProfitMargin');
```
Note:
- All SRCHATTR_ID less than 10000 are reserved by WebSphere Commerce. So, we are using 10001 as SRCHATTR_ID. However, if you used 10001 for some other attribute, determine an available id and use it to register the profit margin attribute, and use this id for all following procedures. Or, you can use the Data Load utility to load this data and register the search attribute.
- Use the '_cat.' prefix in IDENTIFIER to identify that this attribute is part of the catalog attribute.
- IDENTIFIER does not need to be the same as the index field, but it should be meaningful to identify this attribute. So, we name it ProfitMargin in this example.
Specify the usage and data type of the new property in the SRCHATTRPROP table by executing the following SQL statements.
```
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');
```
- Use profit margin as both filter and sort criteria in product recommendation, so we specified both 'merchandising-Filter-Numeric' and 'merchandising-Sort-Numeric' property names for profit margin. You can find more details of what property names are available and their usage in Adding new catalog entry properties to search rule actions or targets
- PROPERTYVALUE 'profitMargin' matches the indexed field name that you used in previous lesson.
Note: profitMargin is the example index field name that is used in this tutorial. Ensure that the PROPERTYVALUE matches the indexed field name that you defined when you updated your search schema.xml file.
Specify the language sensitive display name of the added indexed catalog entry property in the SRCHATTRDESC table by executing the following SQL statement:
```
INSERT INTO srchattrdesc (SRCHATTR_ID, LANGUAGE_ID, DISPLAYNAME, DESCRIPTION) VALUES (10001, -1, 'Profit Margin(%)', null);
```
This makes Management Center display 'Profit Margin(%)' for profit margin property when you are working in English language. You can add name for another language by using the similar SQL. For example, to add French display value for the profit margin indexed catalog entry property:
```
INSERT INTO srchattrdesc (SRCHATTR_ID, LANGUAGE_ID, DISPLAYNAME, DESCRIPTION) 
VALUES (10001, -2, 'translatedText', null);
```
Where translatedText is the translated text in the language you want to show.
Restart your WebSphere Commerce server.

Results

By completing this lesson, the profit margin displays in Management Center in Product Recommendation.