Configure a List of Values with a Business Object
Configure a list of values that uses values from another business object. Lists of values are supported for business object fields, custom action payload fields, and row finder variables.
When you create a list of values, you can associate the selected field with the values from another business object. For example, you may have two business objects: Employees and Jobs. If the Employees layout includes a JobId column, you may want to add a list of values that references the jobId field from the Jobs business object.
When a business user selects a cell from the Job Title column in the Employees layout, a search-and-select window shows a list of values drawn from the Jobs business object for the user to choose from.
If you have a hierarchy of business objects, you can configure a list of values that uses a child or lower descendant business object as your data source. See Use a Child Business Object as a Data Source.
You can configure a list of values to show more than one display field in the search-and-select window. The add-in shows all the display fields, in separate columns, in the search-and-select window but only those you configure are shown in the Excel cell. If you choose to show more than one field in the cell, the values are concatenated.
For business object sources, you can configure a filter to restrict the results to a given subset of values. You can also configure it to let business users filter the list based on a search term they type in a search box. See Configure a Filter for a List of Values.
If the catalog is missing the desired business object, you can add a new business object to an existing catalog (see Add Business Objects to an Existing Catalog).
If you want to reference a business object with a different base path, you can create or import a business object into the current catalog and then override the base path (see Override a Business Object's Base Path).
Note:
This procedure takes you through the steps to create a list of values for a business object field used in a form, table, or search. The steps are the same for custom action payload fields and row finder variables except for the navigation. To open the List of Values page for a custom action payload field or row finder variable, open the Business Object Editor and go to the Custom Action or Row Finder page. From there, select the appropriate field or variable.
To create a list of values based on a local data source instead, see Configure a List of Values with a Local Data Source.
To create a list of values:
- From the Layout Designer, click the Edit (
) icon next to the Business Object field.
- From the Business Object Editor, click the Fields tab, then select the business object's field.
-
Click the Edit (
) icon to open the Business Object Field Editor, then click the List of Values tab.
- Select the Enabled check box on the List of Values page.
- Click the Edit () icon next to the Data Source field, then pick an appropriate data source. Only business objects from the current catalog are displayed in the Choose a Data Source window.
This data source provides the display values for the corresponding identity values.
- Click the Edit () icon next to the Identity Field field, then choose the appropriate identity field from the data source.
This is the field used to look up the display values for the identity values in the current field. The data type of the identity field must match the data type of the field on which you are configuring the LOV.
- Click the Add Field (
) icon to open the Available Business Object Fields Editor, then choose the desired display field.
These fields come from the data source and are shown instead of the identity values where this field is used in a layout.
You can choose multiple display fields for one list of values. Repeat this step to add additional display values.
- For each display field, select either Picker and cell or Picker only from the Display Type list.
If you configured only one display field, use Picker and cell to display the value in both the Excel cell and the search-and-select window. For additional display fields, use Picker only if you don't want to display the value in the Excel cell.
Note:
When configuring the display values, make sure the information in the cell is unique and meaningful for your business users. Take for example a Contact field in your layout. To ensure your business users have enough information to determine the right contact for a purchase order, you may want to include the contact name, company, and email as display fields in the Excel cell. In this case, ensure that the display type for these display fields are set to Picker and cell. - To configure a filter, click the Add Query Parameter () icon next to Filter Query Parameters, then set a name and parameter value.
If the parameter requires a search term, select Omit if SearchTerm is empty.
If you want the add-in to retrieve the latest query parameter definitions from the service metadata on refresh, deselect Preserve on metadata refresh. See Refresh Parameter Definitions for a Lists of Values.
Repeat this step to create additional parameters.
In this image, a list of values is configured for the jobTitle field that is populated with values from the
positionfield of the Job business object. A filter query parameter has been configured that allows a business user to return positions that start with the business user's search term. - Click Done.
Once you define a list of values, the choice list will appear wherever that business object or payload field appears. For the row finder variable, the choice list will appear wherever that row finder variable appears during download.
On first download and before showing all or filtered values in the search-and-select window, the add-in may send requests to download data from the data source. This downloaded data is cached.
After you modify the configuration of any list of values, clear the cached data by clicking Clear List of Values Cache from the Advanced menu. See Clear Cache for a List of Values.
Use a Child Business Object as a Data Source
Let's take a look at an example. Suppose you have a hierarchy of business objects for purchase requisitions, where Purchase Requisitions is the parent, Lines is the child, Distributions is the grandchild.
In your integrated workbook, you have a Table layout for Purchase Order Line Distributions that includes a RequisitionDistributionId field the business user uses to enter an appropriate requisition distribution Id. To make this easier, you'd like to create a list of values for this field that displays the available distributions from the Distributions grandchild business object.
When you create this list of values, you'll need to provide the full path to the Distributions business object. You'll do this by referencing the appropriate field in the layout using expressions. See About Expressions.
The collection path for this business object is:
/purchaseRequisitions/{purchaseRequisitions_Id}/child/lines/{purchaseRequisitions_lines_Id}/child/distributions
Note:
The add-in does not support child business objects that use complex keys for the path parameter values since these keys cannot be written using the add-in expression language.To create this list of values:
- Enable the list of values, then click the Edit () icon next to the Data Source field to open the Choose a Data Source window.
In this image, the available business objects are displayed in a nested tree. To find the business object you want, use the arrows to expand or collapse a node or type in the Filter box to find matching entries.
- Pick an appropriate data source—in this case, Distributions—then click OK.
The List of Values tab now includes a new Path Parameters section below the Data Source area:
- In the Value fields, enter expressions for the required paths that reference the appropriate fields.
The appropriate fields are:
Required Path Referenced Field {purchaseRequisitions_Id}RequisitionHeaderId{purchaseRequisitions_lines_Id}RequisitionLineIdAssuming these fields are all in the same layout, your expressions would look like this:
Note:
These expressions must reference field values in the current business object or any ancestor business object. Those fields must be present in the layouts.
