How Custom Search Fields Are Stored [Migration Draft]

td{border:1px solid black;padding:2px;} table{width:100%;} 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.

Table Name: pcSearchData

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

Table Name: pcSearchFields_Products

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
    pcArr=rs.getRows()
    set rs=nothing
    intCount=ubound(pcArr,2)
    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>"
    Next
  END IF
  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")
 else
  pcStrFieldValue = "N/A"
END IF
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”):
```
<%
'// INVENTORY 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.