Add Ancestor Columns to Dependent Layouts
When you create a dependent layout, you can choose to include columns from parent or higher layout. Columns from these higher-level layouts are referred to as "ancestor" columns.
You might choose to add at least one column from the layout's immediate parent if you want to allow your business users to create rows in the dependent layout. See Add a Parent Column to Support Row Creation.
You might also just add an ancestor column to a dependent layout to help your business users track which child rows are associated with which higher-level rows. See Add Ancestor Columns to Provide Additional Context.
Add a Parent Column to Support Row Creation
Which layouts need parent columns depends on the type of layout used for your primary layout. Let's start with the Form-over-Table case. Let's suppose you have a business object hierarchy where purchaseOrders is the parent, lines is the child, and schedules is the grandchild. For this hierarchy, you create a Form-over-Table with purchaseOrders in the form and lines in the table, as well as a separate Table layout for schedules.
When you download data for this set of dependent layouts, you download a single purchase order for the form, all associated lines for the Form-over-Table's table, and all schedules associated with these lines in the Table layout.
Now let's suppose you want to create a new line for the purchase order. Because the Form-over-Table layout shows only one purchase order in the form and only associated lines in the Table, any line you create is automatically associated with the selected purchase order. You don't have to enter a purchase order number to associate it with the selected one.
But what if you want to create a schedule and associate it with one of the lines? The Schedules layout may include schedules from different lines in the purchase order. To make sure the schedule you create is associated with the right line, you'll need to specify an existing line for the new schedule. Therefore, you need to have at least one parent column showing in the Schedules layout that uniquely identifies the line (for example, LineNumber).
Likewise for any descendant level below the second level. Suppose our sample hierarchy includes a business object, distributions that is the child of schedules. A Distributions layout will need to include at least one parent column (for example, ScheduleNumber) so that you can specify which schedule a new distribution should be associated with.
Note:
If you have a sibling business object at the second level in this scenario, you don't need to add fields from the parent level. Suppose our sample hierarchy includes an attachments business object that is the child of purchaseOrders and the sibling of lines. If you create a Table layout for attachments that you link to the primary layout, then the add-in only downloads attachment rows that are associated with the selected purchase order. If you create a new attachment, it is automatically associated with this purchase order.Now let's look at the second case: when the primary layout is a Table layout. In this scenario, the primary Table layout may display multiple purchase orders. The second-level Table layouts (attachments and lines) may then include rows for each of the purchase orders downloaded in the primary layout. Similarly, the third and fourth-level layouts (Schedules and Distributions) will include schedules and distributions from different purchase order and lines.
If you want to create a new line for a purchase order, you'll need to specify an existing purchase order. Therefore, you'll need to have at least one parent column showing in the Lines layout that uniquely identifies the purchase order (for example, the PO Header Id field).
Add Ancestor Fields to a Layout
To ensure a business user can add a row to a dependent layout, include columns from the parent layout to the dependent layouts in the set.
If you can't find a set of fields from the parent business object that can uniquely identify a new row, you can add ancestor columns that are already included in the parent layout to this child layout for parent row matching. The add-in can match the new row to a parent row by considering values from all ancestor columns in this child layout.
Take our example of set of dependent layouts: purchaseOrders (parent), lines (child), schedules (grandchild), and distributions (great grandchild). Suppose you want to create a new distribution row but the fields from the schedules business object cannot uniquely specify a single schedule. In this case, the add-in may be able to match a distribution row to a schedule using a combination of fields from other levels: "Buyer" from the purchase order business object, "LineNumber" from lines, and "Carrier" from schedules.
To support row creation, you would then add these columns to the distribution layout, as long as "Buyer" and "LineNumber" are added as ancestor columns and "Carrier" is a column in the schedules layout.
Here are a few things to keep in mind when adding ancestor columns:
- The parent column you choose must uniquely identify the parent record and must be included in the parent layout.
- If no parent columns are configured, the table cannot support row creation and rows inserted into the table are ignored during upload.
- If a desired parent field is already displayed as a column in the child layout—for example, a foreign key column—remove it and add that field from its original layout as an ancestor column in this child layout.
This doesn't apply to a field from grandparent or higher layouts that is required for create or update.
- If parent columns are not sufficient to uniquely specify a parent record, you can add non-direct ancestor columns that are already included in the parent layout to support row creation. In this case, all ancestor columns you choose must uniquely identify the parent record.
To add an ancestor column to your layout:
- Click the Ancestors tab from the Available Fields pane.
Available ancestor columns are grouped by business object. In this image, fields for the lines business object are shown under lines.
Oracle Visual Builder Add-in for Excel redraws the table in the layout to include the parent column. The table header for the parent column uses the format "<field title> (<business object title>)" such as "Line Number (lines)".
Refer to Manage Fields in a Form or Table for more information.
Add Ancestor Columns to Provide Additional Context
You can add an ancestor column to a dependent layout to provide information about the ancestor.
Let's suppose you have a set of dependent layouts where purchaseOrders is the parent, lines is the child, and schedules is the grandchild. When a business user is looking at rows in the schedules layout, it may not be readily apparent which schedule is attached to which line.
To help them sort out which is which, you could add a column from the parent layout (lines) to the child layout (schedules), such as POLineId.
Note:
The field must be included as a column or form field in the ancestor layouts. If you don't see the field you want, you'll need to add it to the ancestor layout.To add an ancestor column to your layout, open the Table Column Manager from the Layout Designer, then select an appropriate ancestor field from the Ancestors tab.
Tip:
To add an ancestor column before an existing column in the table, select the existing column in Selected Fields list, then select the ancestor column check box. For example, to display an ancestor column first in the table layout right after the Status column, select the first column.See Manage Fields in a Form or Table for more details.