You can configure a search query for a worksheet using the Search Editor available from the Layout Designer. Using the Search Editor, you can create search conditions on one or more fields in the layout.

1. Select the layout you want to create the search for, then click Designer from the Excel ribbon.
2. From the Layout Designer's Query tab, click the Edit icon () next to the Search property.

   If no search conditions have been defined, the add-in opens the Available Business Object Fields window.

   If you've already defined a search condition, the add-in opens the Search Editor instead. From the Search Editor, click Add field to add a new condition. This opens the Available Business Object Fields window.

3. From the Available Business Object Fields window, select the business object field that you want to enable users to enter search terms for.

   For example, select Department Name if you want to enable users to search on employees by department, as shown here:

   
   
   Description of the illustration vb-search-fields.png
   
4. Select a query operator from the list beside the field name. For example, select "equals" to return employees from a department that matches the value in the value field or "not equals" to return only employees from other departments.

   The available operators depend on the data type. For example, string fields have filters such as "starts with", "contains", and "ends with".

   Note:

   For Boolean fields, the only acceptable values are true and false, regardless of the current language preference. If desired, you can convert Boolean values to other labels by creating a list of values for the field that uses a local data source (LDS). See Use a Local Data Source for a List of Values.
5. If required, select or type a value in the value field you want to use to match items on download. For example, to download employees from the research department, select "Research". You can only specify a single value for this field.

   You can leave the value field blank if you want your business user to provide a value. If you do provide a value here, the business user can always change or clear the value when downloading.

   The search function also supports expressions in the value field with some restrictions. For example, you could use the "Today" function ({ Today() }) to return rows based on today's date. This sample condition for an Employees layout finds employees hired in the past 90 days:

   
   

   

   
   

   Note:

   Keep in mind that the add-in does not validate expressions in the Search Editor. You'll need to test your expression as described in step 10.
6. To add more fields for complex searches, click Add Field, then select a field from the Available Business Object Fields dialog.

   For example, you might want to search for employees with a job title of Software Developer, but only those who earn more than $95,000. In this case, first select Job Title, then click Add Field and select Salary. Finally, define the search parameters as follows:

   
   

   
   Description of the illustration search_fields_complex.png

   
   

   Note:

   When configuring your search, make sure the field your dynamic list of values is based on is added before the field with the dynamic list. So, to filter the Job Title list based on the Department value, add the Department field before the Job Title field in the Search Editor. See Configure a Filter with a Dynamic Parameter.

   Tip:

   To change the order of the conditions, right-click the label and choose the desired option.
7. Select Allow expressions in condition values if you have used an expression in any of your configured conditions. Otherwise, the add-in treats the value as a constant and won't evaluate the expression.

   Note:

   Once you allow expressions in search conditions, the workbook can no longer be used with add-in versions prior to 4.0.
8. Select Editor enabled at download to allow business users to edit the search when downloading data. Deselect this check box to provide a simplified Search prompt instead.

   If this check box is selected, the business user can add and delete search fields as well as change search filters, as shown on the left:
   
   Description of the illustration search-edit-filters.png
   

   If the check box is unselected, only the values are editable, as shown on the right.

9. Click OK.
10. Test your configured search before releasing the workbook. Do this by invoking a download and checking the Search prompt and download results.

    If you need to change your search settings, make sure to click the Clear Layout icon before you re-test your search. The add-in stores the query definition with the layout and uses this cached query for subsequent downloads. Clearing the layout ensures the add-in uses the latest search definition and evaluates any expressions anew during the next download.

    Here are a couple of common failure scenarios:

    • If you used an expression but the value field is blank, it may be because the expression result cannot be successfully converted to the appropriate data type for the condition. There is no error message.

      Make sure the expression matches the field's data type as configured in the Business Object Field Editor.

    • If an error occurs and download fails for a search that references a workbook parameter, make sure the parameter is present in the workbook.

When a search is configured, your business users are presented with the Search prompt during download. If a search condition contains an expression, this is evaluated at this time. Our sample expression, { Today() - 90 }, is converted to a value 90 days before today's date, as shown here:

The add-in stores the details of the business user's query definition with the layout. This cached query is used for subsequent downloads.