Configure a Filter for a List of Values

Configure a filter for your list of values to determine which items from the business object used as the data source are included in the list. A filter is a set of one or more URL query parameters that are appended to a REST request to the referenced business object.

Filters are not available for lists of values that use a local data source.

The filter query parameters are added to all requests to the referenced business object. These include the request to fetch the initial set of values as well as the one sent when the business user clicks the Search icon after entering a search term.

You should always configure a filter with a search term parameter since the search option is always available to the business user.

There are a few basic filter scenarios you may want to consider when configuring a list of values:

• Search term parameter only: You don't need to use a filter parameter for the initial set of values if you want your business users to access all values from the referenced business object. In this case, just configure a search term parameter and let your business user filter the list based on a search term. See Configure a Filter for a Search Term Only.
• Filter and search term parameters: To limit the choices available from the referenced business object, consider using a filter parameter such as a finder. Also, add a search term parameter to let your business user search from within the list. See Configure a Filter to Limit Available Choices.
• Dynamic and search term parameters: A dynamic filter parameter limits the number of values in a list of values based on a field value in the current layout or search box. For example, you may filter a Job Title list of values based on the selected employee's department so that only job titles for this department are displayed. See Configure a Filter with a Dynamic Parameter.
• Cascading lists of values: A cascading list of values uses dynamic filters where the value selected in one list determines the range of values that users can select from subsequent lists. See Configure a Cascading List of Values.

See About Expressions for more information about expressions in lists of values.

Consult the API documentation for your service to determine the appropriate search syntax to use in the Value column. For example, if you use an Oracle ADF REST Resource service, consult Understanding Framework Support for Query Syntax in Accessing Business Objects Using REST APIs.

Configure a Filter for a Search Term Only

Configure a filter with a search term parameter to allow your business users to search for values using a search term.

For example, let's say you have a list of values that displays job titles for your company. To allow the business user to filter this list, you can use a "q" parameter value to return job titles that start with the business user's search term:

Note:

Select Omit if SearchTerm is empty to ensure this parameter is only applied when there is a value in the search box.

In this example, a business user can type "ma" in the search box and click the Search icon to display job titles such as "Manager" and "Marketing Specialist".

You should always configure a search term parameter for your filter since the search option is always available to your business users.

If you have more than one display field in your list of values, you can use the "OR" operand to search on each of the fields. For example, to match text to either the employee's first or last name, create a parameter like this:

When a user types in the search box and clicks the Search icon, the add-in displays all first or last names that match the string. In this example, the add-in would match the search string "Jo" to the first name "Joshua" but also the last name "Johnson".

Note:

The syntax for the filter query varies based on the REST service type and expected query syntax for a business object.

Configure a Filter to Limit Available Choices

Configure a filter with multiple query parameters to limit the choices that are fetched from the referenced business object.

For example, you may have a referenced business object that stores values for a number of different lists of values and you need a way to retrieve only the values for the current field.

Let's consider a business object, StandardLookupsLOV, that stores immigration details for a company's employees such as immigration status (I_Status) and type (I_Type).

You may want to use a finder, for example, to return just the immigration status (I_Status) values, such as "Not Applicable", "Pending", "Accepted" and so on, for your list of values.

You'll also want to create a search term parameter to allow your business users to find the value they are looking for.

To do this, configure your filter as shown here:

In this case, the initial set of values is determined by the finder configuration. If the business user types a search term and clicks the Search icon, the list is further limited to status values whose DisplayValue starts with the search term.

Configure a Filter with a Dynamic Parameter

You can configure a list of values that is dynamically filtered based on another value in a layout, search field, or row finder variable. To do this, configure a filter for your list of values that uses an expression that references another business object field or row finder variable.

Referencing Business Object Fields in a Dynamic Filter

Let's say you have a Table layout for an Employees business object that includes two columns: "Department" and" Job Title". You may want to configure a list of values on the Job Title column that includes a filter that filters the list based on the value in the Department cell in the same row. When configured, your business users will see only those job titles that are relevant for the department that they have selected.

Of course, you can also use these fields in a search. Again, when a business user selects a department, Oracle Visual Builder Add-in for Excel shows only relevant job titles in the Job Title field.

As you can see on the left in this image, the list of values (in this example, "Job Title") is based on the value of another field ("Department"). When used in a search as shown on the right, the Job Title list is filtered based on the value in the first search field ("Department").

Description of the illustration dynamic-filter-example.png

To configure a dynamic filter for the Job Title list, use a parameter that filters based on the department Id. If you have a row finder that filters job titles by department, you can use an expression like this:

The expression in the form, { this.BusinessObject.Fields['FieldId'].Value }, where FieldId is the ID property of another field in the current business object or search—in this case, DepartmentId.

Since this expression refers to another field in the currently selected row or search, make sure this field ("DepartmentId", in this case) has been added to your layout or added before the field with the dynamic filter when it is used in a search. See Use the Search Editor to Find Required Data for information about how to use these lists of values in a search.

The Oracle Visual Builder Add-in for Excel replaces this expression with the corresponding field value (specifically, the identity value) when sending the request for the referenced business object's list of values. When business users select the Job Title field for an employee in the marketing department, the list includes only job titles associated with Marketing.

Likewise when using a dynamic list of values in a search. When business users select the Job Title field, the list includes only job titles associated with the department they first chose from the Department field.

If the corresponding field value is missing or invalid, the expression evaluation fails and an error is reported.

Tip:

Remember to include a search term parameter, such as jobTitle LIKE '{ SelectWindow.SearchTerm }*', to allow your business users to filter the list to jobs based on a search term they provide.

Referencing Ancestor Business Object Fields in a Dynamic Filter

For a list of values that you plan to use in a layout, you can also refer to a parent or higher ("ancestor") business object in your expression using one or more Parent terms. For example, to create a list of values that refers to a field in the parent business object, you would use ByDeptFinder;DepartmentId={ this.BusinessObject.Parent.Fields['DepartmentId'].Value }.

To refer to a grandparent business object, use two Parent terms instead: this.BusinessObject.Parent.Parent.Fields['DepartmentId'].Value.

Note:

Do not refer to an ancestor business object if you plan to use your list of values in a search. Such a configuration will result in an error at runtime.

Tip:

When using a dynamic filter in a layout, it is recommended that you add an ancestor column for the field ("DepartmentId", in this case) to the current layout. The ancestor field must be positioned before the column ("Job Title") that references it. This makes it easier for the add-in to find the correct value during runtime and improves performance.

If there is no matching ancestor column in the current layout, the add-in uses other ancestor columns to map the current row to its parent row. It repeats this process until it finds the ancestor row and reads the field value. This can take some time if there are a large number of parent rows. See Add a Parent Column to Support Row Creation.

Referencing Row Finder Variables in a Dynamic Filter

Just as you do for a business object field, you can also define a list of values on a row finder variable and use it in a search. Like our business object field example, you can also define a parameter that filters the list of values based on the value selected from another variable in the same row finder.

Consider a row finder, "Employees By Country, State, and City", that filters employees by country, state, and city. This row finder includes three row finder variables: CountryId, StateId, and CityId.

You may want to configure lists of values on the each of these variables. For the first one, CountryId, you don't need a dynamic parameter. But for the other two, you would want to configure a parameter that filters the list of values based on the value selected in the previous variable. So, the values in the StateId variable would be based on the value of CountryId and those of the CityId variable would be based on the value of StateId.

Here's how this would look when configured:

Description of the illustration row_finder_lovs.png

In this image, the Search Editor uses our sample row finder and includes three fields for the row finder variables. The list of values in the State field is filtered based on the value entered in the Country field. In this case, the business user has selected "USA" from the Country list, and so the add-in is displaying only US states in the State list.

To configure a list of values on the StateId row finder variable, use a States business object as your data source, then define a "q" query that filters based on the value in CountryId variable, like this:

Description of the illustration finder_stateid_lov.png

The first filter parameter uses the expression, CountryId={ this.Finder.Variables['CountryId'].Value }, where:

• this represents the currently selected row finder variable (StateId, in our example);
• Finder is the row finder to which this finder variable belongs (EmployeesByCountryStateCity);
• Variables['CountryId'] is the finder variable (CountryId) whose value determines the available choices of the current finder variable; and
• Value is the value of the field.

This expression sets the value of the variable, CountryId, used in the q query to the value of the CountryId row finder variable—"USA" in our example. See About Expressions.

This value is then used to filter values from the States business object and populate the list of values with just US states.

Note:

A finder variable can only depend on the values of variables from the same finder. It cannot depend on values from the Form-over-Table or Table layout.

To use the row finder in a download filter, use the row finder as part of a search as described in Use Row Finders to Limit Downloaded Data. When configured, the business user will see the Search Editor when they click Download Data where they can select values for each finder variable.

Tip:

By default, row finder variables are added to the Search Editor in the alphabetical order (City, Country, and State in our example). For a better user experience, consider changing the order so that the dependent ones appear after those they depend on (Country, State, and City).

Configure a Cascading List of Values

Just as you configure a single list of values with a dynamic parameter, you can also configure cascading lists of values for two or more lists using dynamic parameters. In cascading lists of values, the value selected in one list determines the range of values in the next list, and so on.

Let's suppose you have an Employees layout with three columns to capture an employee's location: Country, State, and City. To make sure only appropriate values are entered for an employee, you decide to use dynamic parameters to filter the State list of values based on the Country value, and the City list of values based on the Country and State.

If the service is an ADF REST service and has complete metadata for the cascading lists of values, the filter query parameters of the corresponding field's list of values are automatically configured.

If the service does not have complete metadata for a list of values but you know it supports search syntax that makes for cascading lists of values, you can manually configure the lists of values by adding filter query parameters, as described here.

Note:

You can configure cascading lists of values for row finder variables as well as for business object fields used in a layout or search. This task uses the Business Object Field Editor but the steps are the same for the Row Finder Variable Editor.

To configure a cascading lists of values:

1. Start by creating a list of values for the first field, "CountryId", with just a search term filter (Name LIKE '{ SelectWindow.SearchTerm }*'). See Configure a Filter for a Search Term Only.

   This filter allows the business user to type in the CountryId field to find the country they want.

2. Then, create a list of values for the second field, "StateId", that filters the list of values by the current country, as shown in this image:
   
   
   Description of the illustration bo_editor_stateid_lov.png
   
   

   This example uses a finder query, "ByCountryFinder", that filters based on the value in the CountryId field in the row. If the business user selects "USA" from the CountryId list, Oracle Visual Builder Add-in for Excel displays only US states in the StateId list.

   As with the first list of values, this list also includes a search term filter so that the business user can type in the field to find the state they want.

3. Finally, create a list of values on the third field, "CityId", and filter the list of values by CountryId and StateId, as shown in this image:
   
   
   Description of the illustration bo_editor_cityid_lov.png
   
   

   This example uses another finder query, "ByCountryAndStateFinder", that filters based on the values in both the CountryId and StateId field in the same row. Now, if the business user selects "California" from the StateId list, the add-in displays only cities in California in the CityId list.

   In a scenario like this with three fields ("fieldA", "fieldB", and "fieldC"), remember that fieldC depends on both fieldB and fieldA. So when you create the filter for fieldC's list of values, also include fieldA's value expression. In general, all of fieldB's dependencies must also be fieldC's dependencies.

   Note the following name-value parameters under Filter Query Parameters:

   Table 9-1 Example Name-Value Parameters in Filter

   Field	Name	Value
   StateId	finder	ByCountryFinder;CurrentCountry={ this.BusinessObject.Fields['CountryId'].Value }
   CityId	finder	ByCountryAndStateFinder;CurrentCountry={ this.BusinessObject.Fields['CountryId'].Value },CurrentState={ this.BusinessObject.Fields['StateId'].Value }

Now you are ready to use the fields or row finder variables with the cascading lists of values in your integrated workbook.

To use the fields in a layout, add and order them using the Form or Table tab of the Layout Designer. See Manage Fields in a Form or Table. To use them in a search, add and order them in the Search Editor. See Configure a Search for a Layout.

To use the row finder variables in a row finder search, first order the row finder variables in the Variables tab of the Row Finder Editor. See Configure Row Finders for a Business Object.

Then, select the row finder from the Query tab of the Layout Designer as described in Use Row Finders to Limit Downloaded Data.

Note:

Remember to order the cascading lists of values in the layout or search so that they appear in a meaningful order—Country, State, and City in our example.

Notes on Filters

Refer to these notes when configuring filters for lists of values:

• Oracle Visual Builder Add-in for Excel does not validate your filter configuration. If you provide invalid information, the add-in will make invalid requests that return errors.
• The query parameters are applied in the order that they are configured.
• The add-in applies URL encoding to the final resolved value of each filter query parameter before sending the request.
• Expressions like { this.BusinessObject.Fields['FieldId'].Value } can't be used in the filter for:
  • Row finder variable's list of values
  • Custom action payload fields if the field referred to is not added to the layout
• If your list of values includes more than one display field, you can configure a search query that returns matches from any of the display fields. See Configure a Filter for a Search Term Only for an example of a search term parameter that includes the OR operand.
• If you configure multiple values with the same parameter name, the last value that is not "omitted" (Omit if SearchTerm is empty is enabled) is used by default.

  For example, in this filter configuration, the first query parameter is used if there is no search term; the second parameter is used if there is a search term.

  
  
  
  
  
• Filters are not available for list of values based on local data sources