You can create search conditions on one or more fields in the layout using the Search Editor. A search condition consists of a business object field, an operator, and a value.

An operator determines how items are matched based on the given value and is based on the field's data type. For example, available operators for strings include "starts with", "contains", and "ends with". Those for numbers include "greater than" and "less than". For dates: "before" and "after".

To download data for employees making over $95,000, for example, you'd choose the "Salary" field, select the "greater than" operator, and type "95000" in the value field.

If you configure more than one condition, the add-in always inserts the logical AND operator between each one. So, if you have two conditions such as "Job Title = Software Developer" and "Salary > 95000", the add-in downloads only those items that match both these conditions.

You can also use expressions for the condition value. For example, you could use the Today() date function along with an integer ({ Today() - 90 }) to find employees with hire dates in the last 90 days. See About Expressions.

You can configure a search for a worksheet using the Search Editor available from the Layout Designer. Using the Search Editor you can create one or more search conditions.

1. Select the layout you want to create the search for, then click Designer from the Excel ribbon.
2. From the Layout Designer's Download 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 an 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 Editor 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.

   Expressions can reference workbook parameters. So, if the workbook parameter, MinSal, is set to "95000", the condition, Salary > { Workbook.Parameters['MinSal'].Value }, returns employees making over $95,000. See Use Workbook Parameters for Download.

   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 performing a download and checking the Search prompt and download results.

    If the search value is unexpectedly empty at download time, review your expressions. For example, you may experience a problem if the expression result cannot be successfully converted to the appropriate data type for the condition. In this case, there is no error message. Make sure the expression matches the field's data type as configured in the Business Object Field Editor.

    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 search definition with the layout and uses this cached definition for subsequent downloads. Clearing the layout ensures the add-in uses the latest search definition and evaluates any expressions anew during the next download.

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 search definition with the layout. This cached definition is used for subsequent downloads.

You can configure a list of values for a field so that, when the business user is presented with the Search prompt, they can select an appropriate value from a list. See Configure a List of Values with a Business Object.

Searches even support cascading lists of values with dynamic filters. Let's suppose you want to download data for employees based on department and job title and have added the Department and Job Title fields to your search in order to do so. If the Department field has a LOV, you can choose the required department from the list. If the second field, Job Title, has a dynamic filter based on the Department field, the Job Title list shows only those job titles that are associated with the department you selected in the first field. See Configure a Cascading List of Values.

Oracle Visual Builder Add-in for Excel supports the "in" comparison operator in searches for integer and string data-type fields. This operator is available for ADF REST, VBBO, and NetSuite (integer fields only) services .

The "in" operator allows you to return items with a field value that matches a value in a list you provide. The service treats the "in" condition like a series of OR conditions.

Suppose you have an Employees workbook and want to download employee rows for Sophie Ren, Dave Brown, and Julia Nayer. You could use the "in" operator on the Last Name field and include a list of the last names, like this:

Description of the illustration search-editor-operator.png

The add-in returns all items matching any of the last names provided.

When entering your list of values, enclose string values in single quotes like this: 'Ren', 'Brown', 'Nayer'.

You can also use the "in" operator on an integer-type field and provide a list of the integers you want to match. Do not enclose integer values in single quotes.