Registering the new field in search tables

In this lesson, you use SQL statements to register the new ratings field in the search-related database tables.

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.

Procedure

  1. Register the new indexed catalog entry property within the SRCHATTR database table by using the following SQL statement. 
    INSERT INTO srchattr (SRCHATTR_ID, INDEXSCOPE, INDEXTYPE, IDENTIFIER, OPTCOUNTER) 
VALUES (10001, '0', 'CatalogEntry', '_cat.customerRanking', 0);
  2. 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, OPTCOUNTER) 
VALUES (10001, 'merchandising-Sort-Numeric', 'customerRanking', 0);
    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.
  3. 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, 'Customer Ranking', null);
    When you are adding a property to the SRCHATTRPROP table, you can also add a language-specific name for the property, which can display in the Management Center. Add this property name into the SRCHATTRDESC table. This name is used for the search column that is registered in the SRCHATTR table for which you are defining a new purpose in the SRCHATTRPROP table. If no record is added to the SRCHATTRDESC table, the IDENTIFIER column value from the SRCHATTR table is used.
    For example, to add another language display value for the customerRanking indexed catalog entry property, you can use an SQL statement that resembles the following statement:
    INSERT INTO srchattrdesc (SRCHATTR_ID, LANGUAGE_ID, DISPLAYNAME, DESCRIPTION, OPTCOUNTER) 
VALUES (10001, -2, 'translatedText', null, 0);
    Where translatedText is the translated text for the property in the language that you want the property to display.