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 the workbook to enable a user to specify search values to limit the data that Oracle Visual Builder Add-in for Excel downloads to the workbook.

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.

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. See Configure Business Object Fields.

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 add-in ignores the field when retrieving data.

All searchable fields are available for you to choose from. You can adjust which fields show by selecting or deselecting Searchable in the Business Object Field Editor. Make sure that the service supports searching on that field.

1. In the Excel ribbon, click Designer.
2. In the Layout Designer's Query tab, click the Edit icon () next to the Search property to open the Available Business Object Fields window.
3. 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
   

   Note:

   You can only specify a single value for each field.
4. To add more fields for complex searches, click Add Field. For example, you might want to search for employees with a job title of Software Developer, but only those who earn more than 8000. 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
   
   

   When searching using multiple fields, the add-in always inserts the logical AND operator between each clause.

   Note:

   If the field is required for search, the business user must provide a value to start the download. If the field is not required and no value is provided, the corresponding condition is omitted from the search.
5. Select Editor enabled at download to allow business users to edit the search when downloading data. Clear 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, 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 there is exactly one parameter, a prompt appears that allows the user to specify a value for the parameter.
• If the finder has more than one parameter, 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