How Custom Search Fields Are Stored

Last Updated: Sep 20, 2017 10:41AM EDT
Custom Search Fields are probably one of the most powerful features in the ProductCart e-commerce system. For an overview of what they are and what they can be used for, please see Managing Custom Search Fields.

Here we will focus on the database structure and provide some sample information on how to query this important product-level information from the store database.

Database Structure 

Table name: pcSearchFields
Description: Contains general information about a certain Custom Search Fields.

idSearchField Custom search field unique identifier. Integer
pcSearchFieldName Custom search field name (e.g. “Material”). Text (max 250 characters)
pcSearchFieldShow Whether it should be shown on the product details page in the storefront 1 = yes, 0 = no
pcSearchFieldOrder In which order it should show when a list of Custom Search Fields is shown. Integer
pcSearchFieldCPShow Whether it should be shown on the Modify Product page in the Control Panel. 1 = yes, 0 = no
pcSearchFieldSearch Whether it should be shown on the Advanced Search Page in the storefront. (1 = yes, 0 = no)
pcSearchFieldCPSearch Whether it should be shown on the Control Panel search forms. 1 = yes, 0 = no

 Table Name: pcSearchData

idSearchData unique identifier for the table. Integer
idSearchField unique identifier from pcSearchFields table. Integer
pcSearchDataName the actual Custom Search Field value (e.g. “100% Cotton”). String
pcSearchDataOrder order in which the field value should be shown when field values are displayed in a list Integer

Table Name:pcSearchFields_Categories
Description: Used for the Drill-down navigation feature, where custom search fields are associated with categories.

idSearchFieldCategory unique identifier for the table. Integer
idCategory Category ID from the categories table. Integer
idSearchData Contrary to what the name suggests this field is the id column from the pcSearchField table, not the pcSearchData table. Integer

Table Name: pcSearchFields_Products

idSearchFieldProduct Unique identifier for the table. Integer
idProduct Product ID from the products table. Integer
idSearchData Custom Search Field value ID from the pcSearchData table. Integer

Sample Queries 

Querying all custom search fields

The “Show Custom Search Fields” section of the product details page in the storefront contains a useful query that you could use in other sections of the application to retrieve product-specific custom search field information. This query returns all Custom Search Fields and field values assigned to the product. Prior to using the following query you must set a variable named “pIdProduct” to the ID of the product you want to query.
Public Sub pcs_CustomSearchFields
Dim query,rs,pcArr,intCount,i
  query="SELECT pcSearchFields.idSearchField,pcSearchFields.pcSearchFieldName,pcSearchData.idSearchData,pcSearchData.pcSearchDataName,pcSearchData.pcSearchDataOrder FROM pcSearchFields INNER JOIN (pcSearchData INNER JOIN pcSearchFields_Products ON pcSearchData.idSearchData=pcSearchFields_Products.idSearchData) ON pcSearchFields.idSearchField=pcSearchData.idSearchField WHERE pcSearchFields_Products.idproduct=" & pIdProduct & " AND pcSearchFieldShow=1 ORDER BY pcSearchFields.pcSearchFieldOrder ASC,pcSearchFields.pcSearchFieldName ASC;"
  set rs=connTemp.execute(query)
  IF not rs.eof THEN
    set rs=nothing
    response.Write("<div style='padding-top: 5px;'></div>")
    For i=0 to intCount
        response.write "<div class='pcShowProductCustSearch'>"&pcArr(1,i)&": <a href='showsearchresults.asp?customfield="&pcArr(0,i)&"&SearchValues="&Server.URLEncode(pcArr(2,i))&"'>"&pcArr(3,i)&"</a></div>"
  set rs=nothing
End Sub 

Querying a specific custom search field

If you instead wish to query the value associated with a specific Custom Search Field for a certain product, you can use the following ASP code, where “pcStrFieldName” is the variable that contains the field name and “pIdProduct” is the variable that holds the Product ID. Here we assume the Product ID is 100 and the field name is called “Resolution” (e.g. a Digital Camera). The variable “pcStrFieldValue” will hold the result of the query.
pcStrFieldName = "Resolution"
pIdProduct = 100

query="SELECT pcSearchFields.idSearchField, pcSearchFields.pcSearchFieldName, pcSearchData.idSearchData, pcSearchData.pcSearchDataName FROM pcSearchFields INNER JOIN (pcSearchData INNER JOIN pcSearchFields_Products ON pcSearchData.idSearchData=pcSearchFields_Products.idSearchData) ON pcSearchFields.idSearchField=pcSearchData.idSearchField WHERE (pcSearchFields_Products.idproduct=" & pIdProduct & " AND pcSearchFields.pcSearchFieldName LIKE '" & pcStrFieldName & "');"
set rsCS=Server.CreateObject("ADODB.Recordset")
set rsCS=connTemp.execute(query)
IF not rsCS.eof THEN
  pcStrFieldValue = rsCS("pcSearchDataName")
  pcStrFieldValue = "N/A"
set rsCS=nothing 

Practical Examples 

Showing inventory location on the ordered products pick list

You can print a Pick List for selected orders from the View All Orders page in the Control Panel. This can help your staff fulfill orders by picking products from where they are warehoused. Using a Custom Search Field, you can easily add a product's location in your warehouse(s) to your product database.

Here is a screen shot of the end result. Notice the “Location” column on the Pick List. You can test this feature on your own by using the Apparel Add-on demo store on our ProductCart Web site (Orders > View All Orders > Check Orders > Print Pick List).

Here are the simple steps to follow:
  • Create a new Custom Search Field that is only visible in the Control Panel. You can name it anything you'd like. For example, let's call it “Location”
  • Modify products to assign values to this new Custom Search Field. If you want to use the Import Wizard to quickly update multiple products at once…
    • Export a list of your product SKUs (and product names if you need them to recognize different products)
    • Open the exported document and add a new column to it that matches your new Custom Search Field. In this example, the column will be named “Location”.
    • Add values to that column (i.e. the inventory locations for the various products)
    • Use the “Update” mode within the Import Wizard to import the spreadsheet, mapping the SKU column to “SKU” and your “Location” column to “Custom Search Field (1)”.
  • Download this edited version of the Pick List file.
  • Unzip it and locate the correct file:
    • Use the “Without Apparel Add-on” version if you are not using the Apparel Add-on.
    • Use the “With Apparel Add-on” version if you are using the Apparel Add-on
  • Open it in Notepad or your favorite HTML editor. Locate the following code and make sure that the variable pcStrLocationFieldName matches your Custom Search Field name (in our example, it's called “Location”):
    '// Enter the field name
    Dim pcStrLocationFieldName
    pcStrLocationFieldName = "Location"
  • Save the file and upload it to your Control Panel folder (this file assumes you are using a store with Parent Paths Enabled).
  • You're done!

By using Custom Search Field, you can now also use the Inventory Location of your products as a search field whenever you are filtering products in any of the Control Panel features that use the search filters. You can also export this information by including Custom Search Fields in your export file.
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
Invalid characters found