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.

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.

Take the scenario where there are four layouts: a primary Form-over-Table layout for purchaseOrders (parent) and Lines (child), and three dependent Table layouts for Schedules (grandchild), Attachments (grandchild), and Distributions (great-grandchild). Following a download, you would see that the first worksheet with the primary layout displays the purchase order in the form that matches the query and all associated child lines (say, three Lines) that match a configured query (if applicable) in the table.

The second worksheet displays all child schedules associated with the downloaded lines again based on any configured query. If each of the three Lines had three matching child Schedules, the Schedules table would download 9 schedules. And so on for the other worksheets. You can then update all of these lines, schedules, distributions, and attachments and upload all the changes together.

Once the download is complete, the Status Viewer displays any notifications including how many rows were downloaded at each level, as shown in this example:

Description of the illustration dependentlayout_download.png

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 your changes 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:

Description of the illustration dependentlayout_upload.png

Click each dependent layout to view additional details of the operation.

In some cases, your workbook may be configured to upload all changes to a parent row and its descendent rows (child rows, grandchild rows, and so on) in a single request. If this is the case for your workbook, then the add-in can only successfully upload your changes if there are no errors in any of the rows.

If there are one or more errors in the set of changed rows, all the rows are marked as failed. To troubleshoot the error, review the errors in the Status Viewer for each form, table, and descendant table in your workbook. Once you've fixed the errors in the pending changes, you can retry the upload operation.

Note:

You may have to fix and retry the upload more than once before it succeeds. The REST service may not report all errors that occur during a single upload operation.