Use the Search Editor to Find Required Data

You can configure each layout of the workbook to allow a user to specify search values for filtering the data Oracle Visual Builder Add-in for Excel downloads to the layout.

For example, you might have a Form-over-Table layout that displays a purchase order in the form and associated lines in the table. In this scenario, you'll want to create a search that allows business users to enter the order number for a specific purchase order that they want to view.

You can use the Search Editor to create a filter on two or more fields. So, you could create a search that returns employee records where the job title equals "software developer" and the salary is over " 95000". When you configure more than one condition, the add-in uses the logical AND operator between each one.

Search is supported for workbooks that integrate with ADF REST, ORDS, or NetSuite services.

Configure a Search for a Layout

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.

A search condition consists of a business object field, a query operator, and a value. A query 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.

Expressions can also 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.

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

There are two business object field settings in the Business Object Field Editor related to search: Searchable and Required for Search. If a field isn't searchable, it won't appear in the Available Business Object Fields window. If a field is required for a search, the business user must provide a value at download. Required fields are indicated by an asterisk (*) in the Search Editor. See Configure Business Object Fields.

To create a search query for a 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.