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	ADF REST, ORDS, and NetSuite 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	ADF REST	Provides access to powerful, complicated searches defined by the service Allows the business user to provide values	Only available for ADF REST 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 Find Required Data

You can configure each layout of the workbook to allow a user to specify search values for filtering the data Oracle Visual Builder Add-in for Excel downloads to the layout.

Search is supported for workbooks that integrate with ADF REST, ORDS, or NetSuite services.

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.

   Tip:

   To change the order of the conditions, right-click the label and choose the desired option.
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.

Notes on Search

• For Boolean fields, the only acceptable values are "true" and "false", regardless of the current language preference. If desired, you can use a local data source (LDS) to convert Boolean values to other labels.

Use Row Finders to Limit Downloaded Data

For workbooks that integrate with Oracle ADF REST Resource 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 a Oracle ADF REST Resource 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 ADF REST Resource 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 configure search parameters for each layout in a set of dependent layouts. See Filter Data for a Set of 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
   
   
3. Select Allow expressions in Parameter Value if you include an expression that references a workbook parameter in the Parameter Value field. See Use Workbook Parameters in a Search.

   Selecting this check box ensures Oracle Visual Builder Add-in for Excel evaluates the value as an expression.

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

Use Workbook Parameters in a Search

You can create a search query that references workbook parameters stored in your workbook. Workbook parameters are name-value pairs that are embedded in your workbook programmatically, typically by a web developer.

An organization may choose to embed different values in an integrated workbook—for example, when it is downloaded—based on the context. Let's consider an example. Suppose your organization has a web app that includes an Employees page and that this page includes a button for downloading an integrated workbook. The web developer could use a server-side mechanism to write the same filter values to the workbook used by the business user in the web app.

For example, a business user may filter the employees list in the web app to show only employees in the Sales department with a salary greater than or equal to $7000. If this user then clicks the download button on the page, the web server embeds the filter values— Dept=Sales and MinSal=7000—in the integrated workbook before sending it to the user.

If the workbook has a search parameter that references these workbook parameters, then the user sees the same data as in the web page when they open the downloaded workbook and click Download Data.

The search parameter for a "q" query that references these workbook parameters might look like this:

DepartmentId={ Workbook.Parameters['Dept'].Value } AND Salary >= { Workbook.Parameters['MinSal'].Value }

During download, Oracle Visual Builder Add-in for Excel constructs the GET URL by resolving the expressions in the Search Parameter value property. If the values of the Dept and MinSal parameters of a workbook are "80" and "7000" respectively, then the resulting GET request URL would look like this:

.../Employees?q=DepartmentId=80 AND Salary >=7000

Note:

The specific GET URL query string syntax is governed by the REST service. Consult with your service owner if required.

How these workbook parameters are written to the workbook is up to you. However, if your organization plans to take advantage of this technology, your web developers will need to know where to write these workbook parameters and in what format. You can point them to Embedding Workbook Parameters in a Workbook for these details.

To create a search parameter that references workbook parameters, see Create a Search Using Embedded Values.

Create a Search Using Embedded Values

If your workbook includes workbook parameters, you can create a search query based on the values stored in these parameters.

For example, an Employees workbook may include a couple of workbook parameters for storing department and salary values: Dept=Sales and MinSal=7000. If these workbook parameters are included, you can create a search query to return items based on these values.

To reference a workbook parameter, use this syntax: Workbook.Parameters['name'].Value where name is the name of a workbook parameter.

To use a "q" query that returns sales employees with salaries greater than or equal to $7000, configure the search parameter like this:

DepartmentId={ Workbook.Parameters['Dept'].Value } AND Salary >= { Workbook.Parameters['MinSal'].Value }

Your parameter must be within curly braces ({ and }). To use the curly brace characters as literals in the property value, escape them using a backslash (\). For more information, see About Expressions.

To use a workbook parameter in a search parameter:

1. In the Excel ribbon, click Designer.
2. In the Layout Designer's Query tab, click the Add Search Parameter icon () next to the Search Parameters property to open the Search Parameter Editor.
3. Enter a parameter name and parameter value for your search parameter.

   In this image, the search parameter uses a q search parameter with a search value that references two workbook parameters, Dept and MinSal.

   
   

   
   Description of the illustration workbook-parameter-config.png

   
   
4. Select Allow expressions in Parameter Value to ensure the add-in evaluates the value as an expression.

   WARNING:

   If this check box is not selected, the parameter value is used as a verbatim string when the add-in creates the GET URL. In this example, this would result in a 400 Bad Request error.
5. Click OK to close the editor.

During download for the layout, Oracle Visual Builder Add-in for Excel resolves the expressions in the Search Parameter value property and builds the GET URL. If a workbook parameter is missing from the workbook or the syntax is incorrect, the add-in can't evaluate the expression. This will likely result in a 400 Bad Request error.

Before you distribute the workbook, it is recommended that you test the search behavior by opening the Network Monitor and clicking the Download Data button from the Oracle Visual Builder ribbon. The Network Monitor shows the URLs that the add-in constructs using the Search Parameters. See Network Monitor.

If you experience issues, try modifying the value of the workbook parameter and then clicking Download Data again. The next download will reflect any changes made to these parameters.

To view or modify workbook parameters for testing purposes, open the Edit Workbook Parameters window from the Advanced menu:

Note:

The Edit Workbook Parameters menu command is only available when the designer tools are enabled.

Description of the illustration edit-workbook-parameters.png

To change a value, select the workbook parameter from the list and click the Edit icon ().

Embedding Workbook Parameters in a Workbook

Write workbook parameters to a workbook using a custom solution such as a J2EE servlet or browser-based JavaScript. Workbook parameters are name-value pairs that are stored in a hidden worksheet of the workbook using a packed string-value format. Workbook developers can create search queries that reference these parameters.

Workbook parameters might be name-value pairs that correspond to URL arguments passed when the workbook is downloaded from a web app. They may also represent some state from a web app page. Or they might be arbitrary named values.

Oracle Visual Builder Add-in for Excel does not include functionality for embedding workbook parameters programmatically. An organization that wants to use this functionality must provide their own custom solution for embedding these parameters in a given workbook. This topic is intended for the programmer or web developer tasked with coding this solution.

Workbook parameters must be written to a specific location and in a specific format. Before you proceed, review the parameter requirements carefully to make sure you embed your workbook parameters correctly.

Sample Methods

Here are some possible methods to consider when planning a solution:

• A server-side mechanism using a J2EE servlet or filter to examine and update the workbook during download
• A client-side/browser-based (JavaScript) mechanism that can examine and update the contents of the workbook after it is retrieved from the server and before it is written to the local computer file system

Location

All workbook parameters are stored as a string on a hidden worksheet named _VBCS_WorkbookInfo at cell address $B$15.

Format

The format for the packed string is:

<paramName1>=<paramValue1>/<paramName2>=<paramValue2>/...

with these conditions:

• Each paramName and paramValue must be separately URL-encoded.
• Each name must be separated from its value using the equals character (=).
• When there are multiple name and value pairs in the string, each name value pair is separated from others by the forward slash (/) character.
• The equals (=) and forward slash (/) characters must not be encoded.
• The name portion must not be empty and must match the name used in the search parameter expressions. See Use Workbook Parameters in a Search.

Notes

• The total size for the packed string format for the set of embedded parameters is limited to 32,759 characters.
• The specific worksheet cell (_VBCS_WorkbookInfo!$B$15) may:
  • Be empty (no parameters)
  • Contain a packed string of 1 or more parameter name and value pairs
  • Contain the special placeholder string, $$VbafeWorkbookParameters$$

    This value is set by default when a workbook is first integrated using the add-in, and also when the workbook is published.

    The add-in treats this value, if found, the same as an empty string (no parameters available)