1. Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel
  2. Configure Search Options for Download

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

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 vb-search-fields.png follows
    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 search_fields_complex.png follows
    Description of the illustration search_fields_complex.png

    Note:

    When searching using multiple fields, the add-in always inserts the logical AND operator between each clause.
  5. 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 finder-table-property.png follows
    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:

Use Search Parameters to Limit Downloaded Data

Create a search query that determines which data get returned from the REST service when a 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.

You configure search parameters for your workbook from the Query tab in the Layout Designer.

For example, to retrieve order release line #1234, you would create a search on the orderReleaseLines business object with a search parameter of "ID=1234". The GET request would then include the following in the query string portion of the URL:

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

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.

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.

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 excel-search-parameters.png follows
    Description of the illustration excel-search-parameters.png

  3. Click OK to close the open windows.

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 finder-table-runtime.png follows
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 excel-filter-choicelist.png follows
Description of the illustration excel-filter-choicelist.png