Use Row Finders to Limit Downloaded Data
For workbooks that integrate with ADF REST services, you may select one of the predefined row finders if any are associated with the layout's business object.
- In the Excel ribbon, click Designer.
- In the Layout Designer's Download tab, click the Choose 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 - Select a row finder and click OK.
Once you've selected a row finder, you can use the Edit icon (
) in the Layout Designer to open the row finder in the Row Finder Editor. For more information about 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.
During a download, the row finder configured here is used to filter data based on the finder's criteria. If the row finder requires input from the user, the business user is prompted to provide values in the Search prompt.
If a value is not provided, that variable is not included in the REST request to the service.
See Download Data to the Workbook in Managing Data Using Oracle Visual Builder Add-in for Excel.
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:
Configure Row Finders for a Business Object
If an ADF REST service includes row finders, you can view and configure them through the Finders tab of the Business Object Editor.
Row finders are predefined filters available through services that allow you to download a specific subset of records. For example, your service may include a row finder that filters expense reports by "unapproved" status and assigned to the "current user". In this case, a business user could use the row finder to download only those expense reports that require their approval. See Use Row Finders to Limit Downloaded Data.
Some row finders include row finder variables that act as arguments or parameters for the finder.
Note:
Row finders are defined through the service. You can't add row finders or row finder variables through Oracle Visual Builder Add-in for Excel. Nor can you change properties like the finder's ID or a variable's data type on the service. Any changes you make must be compatible with the service. If your changes are incompatible, you will likely see errors during a download.To configure a row finder:
- Navigate to the Row Finder Editor for the row finder you want to edit:
- In the Excel ribbon, click Designer.
- In the Layout Designer, click the Edit icon () next to the Business Object.
- Click the Finders tab in the Business Object Editor to view a list of available row finders. Remove row finders if required.
Note:
The Finders tab is only displayed for ADF REST services. - Select a row finder from the list and click the Edit icon ().
- Edit the settings on the General tab as required. See
If a row finder supports variables, they'll be listed on the Variables tab. Variables act as arguments or parameters for the finder.
- Click the Variables tab, to view a list of variables.
You can reorder how row finder variables appear in the Search prompt or remove unwanted row finder variables so that they don't appear in the prompt at download time.
Variables are shown in the order they appear in the Search prompt. To change the order of the variables, drag a variable to another location in the list.
You may want to reorder variables if you are configuring lists of values with dynamic filters. Consider a row finder, "Employees By Country, State, and City" that includes three row finder variables in this order: "CityId", "CountryId", and "StateId". You'll want to move the CityId variable to the bottom so that the business user first selects a country, then a state, and finally a city.
Tip:
You can also right-click a variable and select an action (Move Up, Move Down, and so on) from the popup menu. - To open a row finder variable, select it from the list and click the Edit icon ().
- Edit the variable as required.
- Click Done until you return to the Layout Designer.
Once you are done making changes, the row finders become available to you as an option to limit downloaded data, as described in Use Row Finders to Limit Downloaded Data.
Row Finder Properties
Use the Row Finder Editor to modify the row finder's title as well as add help text and a description. Open the Row Finder Editor for a row finder from the Finders tab of the Business Object Editor.
From here, open a row finder in the Row Finder Editor and configure how it appears in the add-in.
| Property | Description |
|---|---|
| ID | The ID for the row finder as assigned by the service.
Caution: Take care not to change the row finder's ID. |
| Title | Give the row finder a more readable title. This value can be translated. See Manage Workbook Translations. |
| Help Text |
Provide a useful description of the row finder for your business users. This text is displayed in a popup next to the row finder title in the search prompt. Help Text values can be translated. See Manage Workbook Translations. |
| Description |
Provide a description of the row finder to be used internally. This property is intended for workbook developers. It is displayed only in the designer UI, like the Finders tab of the Business Object Editor. You can use this property, for example, to provide technical details of the row finder to workbook developers. |
Row Finder Variable Properties
- Give row finder variables a more readable title.
- Define default values for row finder variables.
- Set row finder variables for either basic or advanced searches.
- Configure a list of values for a row finder variable.
- Configure a validation rule for a row finder variable.
- Add some help text to help your business users understand what should go in a row finder variable.
Open the Row Finder Variable Editor for a variable from the Variables tab of the Row Finder Editor.
The General Tab
This image shows the General tab for the Row Finder Variable Editor for a "Bind Sourcing Only Flag" variable of the "FindByRequisitionBU" finder.
Edit the properties as required:
| Property | Description |
|---|---|
| ID | The ID for the variable as assigned by the service.
Caution: Take care not to change the variable's ID. |
| Title | Give the row finder variable a more readable title. This value can be translated. See Manage Workbook Translations. |
| Help Text |
Provide a useful description of the variable for your business users. This value is displayed as a popup next to the row finder variable in the search prompt. Help Text values can be translated. See Manage Workbook Translations. |
| Description | Provide a description of the variable to be used internally. This property is intended for workbook developers. |
| Default Value |
Set a default value for the row finder variable. For example, you might have a row finder, "FindByBusinessUnit", for downloading expense reports by business unit. This finder includes a variable, "BusinessUnit", where the business user can specify their business unit, such as "Manufacturing". In this case, you can set the default value to "Manufacturing". When a business user downloads data, the Search prompt displays the default value for the row finder variable. Default values are not supported if a list of values is configured for the row finder variable. Default values can use expressions such as Note: Reserved words are not supported with one exception. You can use expressions that reference workbook parameter values in the default value for row finder variables. See Use a Workbook Parameter Value for a Row Finder Variable. |
The Constraints Tab
This image shows the Constraints tab for the Row Finder Variable Editor.
Edit the properties as required:
| Property | Description |
|---|---|
| Required | Select this check box to force the business user to provide a value at download. |
| Advanced Search Only |
Select this check box to include this variable only in advanced searches. Deselect this check box to include this variable in the basic search. During a download, the add-in opens a search prompt that includes only basic search variables. If a business user wants to perform a more refined search, they can click the Advanced button to display both the basic and the advanced variables. If the business user proceeds with a basic search, the advanced variables are not included in the download request. Note: When editing a row finder's variables, make sure to leave at least one basic variable to display in the search prompt. |
| Validation Rule | Enter a validation rule for the variable. See Validation Rules for Row Finder Variables. |
| Validation Failure Message | Provide a custom error message to display when validation fails. Use this field to provide a brief explanation for your business users of what values are expected for this field. This value can be localized. |
If required, configure a list of values for a row finder variable from the List of Values page in the Row Finder Variable Editor. See Configure a List of Values with a Business Object.
Validation Rules for Row Finder Variables
These expressions are supported for the Row Finder variables:
| Expression | Use |
|---|---|
{ this.Value } |
Provides the value of the current variable |
{ this.Finder.Variables['<VariableID>'].Value } |
Provides the value of the row finder variable, VariableID. |
Consider a row finder with two variables, "Category" and "Item", where a value is required for at least one of the two variables.
If both are empty, download is blocked from proceeding. If either variable has a value, the download proceeds.
To achieve this goal, configure a validation rule for each variable that references the other as shown here:
- For the Category variable:
{ this.Value != null || this.Finder.Variables['Item'].Value != null } - For the Item variable:
{ this.Value != null || this.Finder.Variables['Category'].Value != null }
An appropriate validation failure message might be " Provide a value for either the Category or the Item finder variable".
Keep in mind:
- Standard operators and functions are supported.
- Validation rule expressions must evaluate to True or False. Any other value is considered a configuration error.
- Value returns null if the variable is empty in the prompt.
- The validation rule is evaluated when a business user types a value for the variables in the Search prompt. If the rule evaluates to anything other than True, the variable value is considered invalid and download cannot proceed.
Use a Workbook Parameter Value for a Row Finder Variable
Let's say the service has a row finder, "FindByBusinessUnit", with a variable, "BusinessUnit". Instead of setting the variable's default value to a static value—like "Manufacturing"—you may want to write the user's business unit to a workbook parameter and use an expression that references this parameter.
When a business user downloads data, they are prompted to provide the business unit for the row finder, which then returns expense reports matching this value.
If you set the default value dynamically based on where the workbook is retrieved from, you would need to embed the value as a workbook parameter while it is being retrieved from the web application. Workbook parameters are name-value pairs that are embedded in your workbook programmatically, typically by a web developer. For help writing values to workbook parameters, see Embedding Workbook Parameters in a Workbook.
To use a workbook parameter value as the default value, enter an expression like this: { Workbook.Parameters['<para_name>'].Value } in the Default Value field of the Row Finder Variable Editor. See Workbook Parameters in Expressions.
In this example, the expression references a workbook parameter, BusUnit which stores the name of the business unit.
Oracle Visual Builder Add-in for Excel does not validate expressions in the Row Finder Variable Editor. You should test your expression before distributing the workbook.
Keep in mind the following when testing:
- If the expression result cannot be successfully converted to the appropriate data type for the row finder variable, the row finder variable value will be blank/empty. There is no error message.
- Workbook parameters do not have a data type associated with them. Expressions that use them treat them as string data values.
There are no conversions for different cultures within the expression.
- If you change the default value in the editor after downloading data, be sure to clear the layout to test the new default value.
- The expected format of the workbook parameter values is "invariant":
- Dates: ISO 8601 "1992-06-15"
- Numbers: decimal point; "3.14"
- Date-time: ISO 8601 UTC: "2017-04-19T12:33:19Z"
- Boolean: "True" / "False" (not localized)
BusUnit workbook parameter when downloading data.