====== How Custom Search Fields are Stored in the ProductCart Database ======

===== Overview =====

Custom Search Fields, structurally updated in version 3.5, 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 [[:productcart:managing_search_fields|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 =====

==== pcSearchFields ====

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, up to 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)

==== pcSearchData ====

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

==== pcSearchFields_Categories ====

Used for the [[:productcart:search_fields_widget|Drill-down navigation]] feature, where custom search fields are associated with categories.

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

==== pcSearchFields_Products ====

  * idSearchFieldProduct = unique identifier for the table
  * idProduct = Product ID from the //products// table
  * idSearchData = Custom Search Field value ID from the //pcSearchData// table

===== 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.

<code>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</code>

==== 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.

<code>
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
</code>

===== 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 [[http://www.earlyimpact.com/demos/apparel/pcadmin/resultsAdvancedAll.asp?fromdate=12%2F26%2F2007&todate=1%2F8%2F2009&otype=0&PayType=&B1=Search+Orders|Apparel Add-on demo store]] on our Early Impact Web site (Orders > View All Orders > Check Orders > Print Pick List).

{{:developers:inventory_picklist_screenshot.gif|Inventory Location on the 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)".
  * {{:developers:batchprintpicklist.zip|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"): <code>
<%
'// INVENTORY LOCATION
'// Enter the field name
Dim pcStrLocationFieldName
pcStrLocationFieldName = "Location"
%>
</code>
  * 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.

===== Related Topics =====

  * [[productcart:managing_search_fields|Managing search fields]]