6 Configure Search Options for Download

Configure the search options for a given layout. The configured search options are used when the user clicks Download in the Oracle Visual Builder tab.

The add-in provides search options that you configure for each layout. The properties that you can configure depend on the type of service that you integrate your Excel workbook with. For example, all properties are available to use when your workbook integrates with an Oracle business object REST API service; when you use Oracle REST Data Services, you can only configure Search and Search Parameters properties.

Configure values for the Search property when you want to provide users with the option to enter search terms to filter the data that they download from the Oracle business object REST API service or Oracle REST Data Services. Consider, for example, a data table that downloads data about employees. In this scenario, you can add the Department name business object field as a search field to the Search property, so that users can enter search criteria to download the employee records of those employees who belong to the department that matches the search criteria. You can enter multiple fields to the Search property so that users can enter multiple terms in their search.

Configure a value for the REST Finder property if the Oracle business object REST API service you connect to supports finders. If you select a value for the REST Finder property, the add-in uses it during download. A Finder that an Oracle business object REST API service supports may have parameters. If the Finder that you choose for the REST Finder property specifies parameters, the add-in prompts users to provide parameter values during download.

Add values for Search Parameters if the service that you download data from supports the addition of various parameters in the query portion string.

Depending on the service you use, you can configure one or more of these properties. If you configure values for all properties, the add-in prompts the user to enter the values supported by the REST Finder property before it prompts the user to enter values for the Search property. Once the user enters the requested input values, the add-in downloads data based on the values that the user input.

Tip:

If you are troubleshooting 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.

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 the add-in downloads to the workbook.

1. In the Excel ribbon, click Designer.
2. In the Query tab of the Layout Designer, 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 and click OK if you want to enable users to search on employees by department, as shown by the following image.
   Description of the illustration vb-search-fields.png

   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
   

   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.

4. Click OK.

Use REST Finders to Limit Downloaded Data

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

1. In the Excel ribbon, click Designer.
2. In the Query tab of the Layout Designer, click the Edit icon next to the REST Finder property, as shown in the following image.

   The service must be configured to expose the options that appear in the Available Finders window.

   
   Description of the illustration finder-table-property.png
3. Click OK to close the open windows.

For more information about configuring the Oracle business object REST API service that supports finders, refer to the following 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

You add search parameters to the layout to include in the request to download data from the REST service.

Many REST services support the addition of various parameters in the query string portion of the URL, such as the following example.

GET…/orderReleaseLines?q=ID=1234

In this example:

• "q" is an optional parameter supported by the orderReleaseLines service
• "ID" is the name of a field that supports query
• 1234 is the search value

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 (=).

The add-in does not know which parameters are useful for search. Likewise, the add-in does not know the proper syntax for the parameter values. So, you will need to 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.

There is no validation in this editor or at download time. If you enter invalid information, you may get a bad request error.

When the user clicks the Download Data button, the search parameters are added to the appropriate URL along with the other search options (if applicable).

The add-in applies URL encoding to the parameter value at download time. So, you should not enter URL-encoded values. The search parameter name is not encoded.

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)))

The Search Parameters property does not support "dynamic" search values. Using the above example, the value, 1234, is specified in the Layout Designer as a constant.

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.

To add search parameters to your layout:

1. In the Excel ribbon, click Designer.
2. In the Query tab of the Layout Designer, 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
   
   
3. Click OK to close the open windows.

Download Behavior in Layouts that Use Search and REST Finders

If you configured a value for the REST 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 REST 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 illustrated in the following image.

Description of the illustration excel-filter-choicelist.png