6 Manage Data for Multi-Level Business Objects
Sometimes business objects exist in a multi-level hierarchy. For example, a purchase order (PO) might contain a header and a set of lines. Each of these lines may contain one or more schedules and so on. For these hierarchies, Oracle Visual Builder Add-in for Excel supports operations across all "dependent" layouts.
In this example, purchaseOrders is referred to as the "parent" business object, lines is the "child", and schedules is the "grandchild". A workbook based on this hierarchy might use a Form-over-Table layout or a Table layout as the top-level, or "primary", layout.
If the workbook uses a Form-over-Table as the primary layout, the form displays the parent business object and the table, the child business object. If the workbook uses a Table as the primary layout, the table displays the parent business object.
In this diagram, a Form-over-Table layout on Sheet 1 displays the details of a purchase order in the form and all associated lines for this purchase order in the table. An additional dependent Table layout on Sheet 2 displays all the schedules associated with the PO's lines.
If, instead, the workbook uses a Table layout as the primary layout, then each level in the hierarchy has its own Table layout on a separate worksheet. In this diagram, Sheet 1 includes a Table layout for the parent business object showing the details for three purchase orders. Sheet 2 and Sheet 3 includes dependent Table layouts for the child and grandchild business objects, respectively.
Each of these dependent layouts is linked to its direct parent layout. Check the Status Viewer for a given Table layout's parent.
Description of the illustration status-viewer-parent.png
When you click Download Data, Upload Changes, or Clear for a layout in a dependent hierarchy, the operation takes effect on all layouts in the hierarchy, starting with the primary layout, progressing to the next layout in the hierarchy, and continuing down until the last level in the hierarchy. When the operation is complete on all layouts in the hierarchy, the worksheet with the primary layout is made active again.
Download Data
During a download operation, the add-in downloads matching items for each layout in the hierarchy, starting with the primary layout, progressing to the next layout in the hierarchy, and continuing down until the last level in the hierarchy. The items retrieved at each level are those that match any configured search queries. There may be a separate search query at each form or table in the hierarchy.
Take the scenario where the primary layout is a Form-over-Table layout for purchaseOrders and Lines and the second-level layout is a Table layout for Schedules. Following a download, you would see that the first worksheet with the primary layout displays the matching purchase order in the form and all associated lines (say, 10 Lines) that match a configured query (if applicable) in the table.
The second worksheet displays all schedules associated with the downloaded lines again based on any configured query. If each of the 10 Lines had two matching Schedules, the Schedules table would download 20 schedules. You can then update all of these Schedules and upload all the changes together.
If your layouts have ancestor columns configured, then a download will populate these columns automatically.
Following a download, edit data much as you would in a Table or a Form-over-Table layout.
Note:
If you do not see all of the expected rows in your dependent layouts, check with your workbook developer. These layouts may have been configured with a search parameter that limits the rows that are downloaded.Create New Rows
Create new rows on the primary layout as you would for any Form-over-Table layout or Table layout. See Create a Parent Row in a Form-over-Table Layout and Create New Rows to Upload to the Web Application.
For Table layouts below the primary layout, you need to associate new rows with the appropriate parent when you create them. For example, you'll need to specify the correct line for a new schedule and the correct schedule for a new distribution.
To ensure you can properly associate new rows with the correct parent, the layout may include one or more columns from the parent layout. For example, the distribution layout may contain columns for Schedule Id from the schedule layout.
So, when you create a new row in the Distributions table, you'll need to enter valid values in the parent column (Schedule Id, in our example) to ensure the new distribution is associated with the right schedule.
As soon as you do so, the add-in populates any other ancestor columns, such as Line Id and Line Description, with the correct values. If the entered schedule value doesn't match an existing schedule, the add-in does not populate the other ancestor columns.
You must enter non-empty values in the parent columns that uniquely identify an item in the parent table. If, during upload, the entered schedule value doesn't match an existing schedule, creation of the row fails.
You can't enter values for grandparent and higher columns. These fields are read-only.
Upload Data
When updates are ready to be uploaded, the upload operation submits all pending changes across the hierarchy of layouts. Pending changes may include creation of new items, deletion and updating of existing items, and invocation of actions on items, depending on how the layouts are configured and what capabilities are supported by the business objects.
You can view details of the operation in the primary layout's Status Viewer, which shows results for the primary layout as well as a summary for each layout in the dependent hierarchy, as shown in this example for a download operation: 
Description of the illustration dependentlayout_download.png
Click each dependent layout to view additional details of the operation.