6 Configure Search Options for Download

You can configure the search options for a layout, which are used when the user clicks Download in the Oracle Visual Builder tab.

Search Option	Available for	Benefits	Limitations
Graphical Search Editor	Oracle business object REST API and Oracle REST Data Services	Provides a graphical user interface for building a search Allows business user to provide values and modify the search	Some limits on search syntax
Row Finders	Oracle business object REST API	Provides access to powerful, complicated searches defined by the service Allows the business user to provide values	Only available for Oracle business object REST API services
Search Parameters	Any service type	Developers can use any syntax the service supports	Business users cannot provide values or change the search at download time

Use Search to Limit Downloaded Data

For workbooks that integrate with Oracle business object REST API services and Oracle REST Data Services, you can configure each layout of the workbook to enable a user to specify search values that limit 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 create a search for a layout by setting one or more search conditions. 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.

The add-in supports lists of values (LOV), including ones with dynamic filters, in the Search Editor. 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.

Note that there are two business object field settings in the Business Object Field Editor related to search:

• Searchable: If Searchable is selected for a field, the field is available for you to choose from. You can adjust which fields show by selecting or deselecting Searchable in the Business Object Field Editor. Before you set a field as searchable, make sure that the service supports searching on that field.
• Required for Search: If you want to require your business users to provide a value for a field when downloading data, select Required for Search for the field in the Business Object Field Editor. Required fields are indicated by an asterisk (*) in the Search Editor.

  If a field is required, the business user must provide a value before proceeding. If a field isn't required and the business user leaves it blank, the corresponding condition is omitted from the search.

See Configure Business Object Fields for more information.

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

3. To configure a search condition:
   1. From the Available Business Object Fields window, select the business object field that you want to enable users to enter search terms for. If this window is not open, click Add field from the Search Editor first.

      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
      
   2. 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".

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

4. 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.
5. 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.

6. Click OK.

Use Row Finders to Limit Downloaded Data

For workbooks that integrate with Oracle business object REST API services, you may select one of the predefined row finders if any are associated with the layout's business object.

1. In the Excel ribbon, click Designer.
2. In the Layout Designer's Query tab, click the Edit icon next to the Row Finder property to see the row finders configured for the service in the Available Row Finders window:
   
   Description of the illustration finder-table-property.png
   

   For details on modifying how row finders appear in the add-in (including how to add titles and help text for row finders and variables), see Configure Row Finders for a Business Object.

3. Select a row finder and click OK.

   When you download data to your workbook, the row finder filters the data based on the filter criteria. If the row finder requires input from the user, you'll be prompted to provide a value for the row finder parameter.

For more information about configuring the Oracle business object REST API service that supports finders, refer to these resources in Developing Fusion Web Applications with Oracle Application Development Framework:

• About RESTful Web Services and ADF Business Components
• Filtering a Resource Collection with a Row Finder

Use Search Parameters to Limit Downloaded Data

Create a search query that determines which data get returned from the REST service when a business user invokes a download. For example, if a user only needs to access records for a specific location, you can create a query that includes a search parameter for that location.

For example, to retrieve employee items where the managerId field is empty, you would create a search on the Employees business object with a search parameter of "managerId is null". The GET request would then include the following in the query string portion of the URL:

GET…/Employees?q=managerId+is+null

where:

• q is a query parameter supported by the Employees service
• managerId is the name of a field that supports the query parameter
• null is the search value

When the user clicks the Download Data button, the add-in appends the search query to the REST endpoint URL of the GET request. If search or row finder settings have also been configured, the user is prompted to provide values for the configured parameters that will further filter the returned results.

Note:

The Search Parameters property works in combination with the other two properties (where applicable). They are not mutually exclusive. However, some combinations may work where others may not. If you choose to configure multiple search options, you must ensure that the service supports that combination.

For example, some services such as Oracle business object REST API services do not support using more than one "q" parameter in a REST call. If you define a "q" search parameter for a layout, it may not work if you already have a search defined using the Search Editor.

If the service supports complex searches, you can create complex searches in the layout, as shown in the following example:

q=((firstName LIKE '*es*') or ((hireDate< "2001-01-13") and (department = 10)))

Each service defines which parameters can be used for search. Likewise, each service defines the required and supported syntax for the expression that appears on the right-hand side of the assignment operator (=).

There is no validation in this editor or at download time. The add-in cannot determine which parameters are useful for search. Likewise, the add-in can't determine the proper syntax for the parameter values. If you enter invalid information, you may get a bad request error. Consult the API documentation for the service you are using to identify whether to use "q" for the parameter name and how to formulate the search expression properly.

The add-in applies URL encoding to the parameter value at download time. Don't enter URL-encoded values. The search parameter name is not encoded.

Note:

You can create a separate query for each layout in a set of dependent layouts. See Create Search and Search Parameter Queries for Dependent Layouts.

To add search parameters to your layout:

1. In the Excel ribbon, click Designer.
2. In the Layout Designer's Query tab, click the Add or Edit Search Parameter icon next to the Search Parameters property to open the Search Parameter Editor where you add or edit search parameters.
   
   
   Description of the illustration excel-search-parameters.png
   
   

   Note:

   If you include an expression in the Parameter Value field, select Allow expressions in Parameter Value to ensure Oracle Visual Builder Add-in for Excel evaluates the value as an expression. See About Expressions.
3. Click OK to close the editor.

Tip:

To troubleshoot a particular combination of search settings, open the Network Monitor and download data. Then, inspect request details in the Network Monitor's window. This information may help you refine search settings.

Download Behavior in Layouts that Use Search and Row Finders

If you configured a value for the Row Finder property, the behavior of the add-in depends on the configuration of the finder exposed by the REST service.

If there are no parameters, there is no prompt. If the finder has one or more parameters, a prompt appears that allows the user to specify values for each parameter.

Note:

Only Oracle business object REST API services support row finders.

Description of the illustration finder-table-runtime.png

If you added search fields to the Search property, the add-in prompts users who download employee data using the Download Data button to enter values in the search field, as shown here:

Description of the illustration excel-filter-choicelist.png