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

Here are some things to keep in mind when configuring search options:

• The search 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 configure Search and Search Parameters properties only.
• 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 Row Finder property if the Oracle business object REST API service you connect to supports finders. If you select a value for the Row Finder property, the add-in uses it during download. A row finder may also have variables. If the finder you choose for the Row Finder property specifies variables, the add-in prompts users to provide variable 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 values supported by the Row Finder property before it prompts the user to enter values for the Search property. Once the user enters the requested values, the add-in downloads data based on the user's 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 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 and click OK if you want to enable users to search on employees by department, as shown here:
   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 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

You can specify additional search parameters to add to the REST endpoint URL on download.

Many REST services support the addition of various parameters in the query string portion of the URL, as in this example:

GET…/orderReleaseLines?q=ID=1234

where:

• 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 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
   
   
3. Click OK to close the open windows.

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