Searching - Multiple Databases (Relational)

WebCatalog can search multiple databases as easily as it searches a single database. Often you will want to look up information in a second database while displaying the results of a search. If the relationship is very simple (an exact match of text in one database field to the text of another database field) then you may use the faster [Lookup] tag. If the relationship is more complex, then you may need the more sophisticated [Search] context.

[Lookup] Example: Let's say you maintain a list of names and addresses, and you want to display the fully-spelled-out name of the state a person lives in. However, you only store the 2-letter code in the address database. Create a database that lists all 50 states by their 2-letter code, and put the fully-spelled state name into another field in that database:

Abbrev	FullName
CA	California
AZ	Arizona
FL	Florida

[Name], [Address], [City]
[Lookup db=States.db&lookInField=Abbrev&value=[State]&returnField=FullName]

Notice that the lookup looks through the States.db database for an exact match of the [State] field information coming from the first database. It then returns the text from the [FullName] field of the corresponding state name stored in States.db.

[Search] Example: Let's say you store all the orders created by your store in two databases. One of the databases stores the order# and Name/Address/Account information, while another database stores each individual SKU of the line items purchased during an order:

OrderNumber	Name	Address	City	State	Zip
1234	Grant	667 Corte	San Marcos	CA	90031
1395	John	12 Elias	San Diego	CA	90004

OrderNumber	SKU	Qty	Price
1234	Shoe1	1	12.95
1234	Shirt1	2	15.95
1234	Service	1	99.95
1395	Gift1	1	9.95
1395	Box12	3	74.95

To display a list of all the orders with associated line items, your search results template might look like the following:

  [OrderNumber], [Name], [Address], [City]
  [Search db=LineItems.db&eqOrderNumberdata=[OrderNumber]]
      [SKU], [Qty], [Price]

----Output from above----
1234, Grant, 667 Corte, San Marcos
	Shoe1	1	12.95
	Shirt1	2	15.95
	Service	1	99.95
1395, John, 12 Elias, San Diego
	Gift1	1	9.95
	Box12	3	74.95

Notice that the interior [Search] looks for all matching line items with the same order number as the exterior Orders.db database. This collects and displays all line items for that order underneath the proper header information.