SEARCHING TEXT FIELDS

I'd like to conclude this chapter with an example that draws on several previously covered concepts: parameters, calculated fields, text functions, and filters. Let's create a search box that allows you to look up customers by name, even if you don't know the whole name or the exact spelling.

Start by creating a new parameter. Call it Search, or something similar, set the data type to String, and leave the current value blank (see Figure 4.12).

Figure 4.12 Setting up a string parameter with an empty current value.

After the parameter has been created, add the associated parameter control to the view. Then create a new calculated field, call it Match, and enter the following formula:

CONTAINS(LOWER([Customer Name]), LOWER([Search]))

Here, you use the text operator CONTAINS to test whether the current value of the parameter is contained within the customer name. To increase the hit rate, you convert both the customer name and the search text to lowercase...