12 Use Multiple Layouts for Multi-level Business Objects
When business objects in your REST service have a parent-child relationship of 3 or more levels, you can create a set of dependent layouts and then perform operations such as download data and upload your changes to all your layouts at once.
Consider an example hierarchy of business objects, where purchaseOrders is the parent, lines is the child, and schedules is the grandchild.
In this hierarchy, purchaseOrders is a collection of top-level purchase orders each with one or more lines for managing the details of each order. Each of these lines may include one or more schedules for tracking shipping details.
To create a set of dependent layouts, create a Form-over-Table layout for the first two levels (purchaseOrders and lines) on the first worksheet. Then create a Table layout for each subordinate level (schedules, in this scenario) on a separate worksheet. When you are done, link each dependent layout to its direct parent layout.
A hierarchy of business objects can go to even more levels ("great-grandchildren" and "great-great-grandchildren") or have more than one business object at each level ("siblings"). For example, the following hierarchy has "sibling" grandchildren (attachments and schedules) and a "great-grandchild" (distributions) under schedules.
"Siblings" (business objects at the same level) must be at the third level or deeper in your hierarchy for all of them to be linked to the parent. You can create a Table layout for a second-level sibling of lines such as poAttachments but you can't link it to the parent in the hierarchy since the parent in the primary layout is already associated with the other sibling, lines. You'll need to maintain the table layout for poAttachments separately.
Once the dependencies are established, download, upload, and clear operations act on all the linked layouts, starting from the parent layout, followed by the child layout, the grandchildren layouts, and so on.
If you allow your users to create new rows in a layout (create is enabled in the Table's capabilities), you must add one or more columns from the parent layout to uniquely identify the parent of the new child item. To create a new distribution record, for example, the user must be able to specify the correct line and schedule for the new distribution.
Download, Upload, and Clear Operations on Dependent Layouts
When you download, upload, or clear data 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.
If the layout is not part of a set of dependent layouts, the operation is performed on the active layout only.
Note:
Settings such as those for queries, macros, and pagination only apply to the primary layout and are not enforced on third-level and deeper dependent layouts.Downloading Data
On download, the add-in first checks all layouts in the hierarchy for any pending changes. If there are changes pending, the user is prompted to confirm the download operation. If the user chooses to proceed, all pending changes are lost.
During download, the add-in first retrieves the values for the primary layout from the REST service. After the primary layout is populated, the add-in makes the next worksheet in the hierarchy active and retrieves all the appropriate items.
All items for all rows from the parent layout are downloaded at each level. (Any search specifications, if configured, apply only to the primary Form-over-Table layout.) For example, when Sheet 1 in your workbook contains Purchase Orders as the parent and Lines as the child (containing, say, 10 Lines) and Sheet 2 contains Schedules as the grandchild, the Schedules table is populated with all Schedule items for all Lines. If each of the 10 Lines had two Schedules, the Schedules table would download 20 Lines.
The download operation proceeds through the rest of the table layouts in the hierarchy, retrieving all items for all rows from the parent layout.
When the operation finishes, the primary layout becomes active and the Status Viewer 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
Following a download, you can edit data much as you would in a Table or a Form-over-Table layout.
Uploading Changes
On upload, the add-in makes the primary layout active and sends updates first from the form and then from the table of the layout. The add-in then moves to the worksheet with the first dependent table layout active and uploads changes before proceeding to the next layout.
Pending changes may include creation of new items, updating of existing items, and invocation of actions on items. For rows pending Update, values in the parent column cells are not uploaded.
For new items at the third-level or deeper, values in the parent columns must match a row in the parent layout. For example, to create a new distribution, you must specify an existing line and schedule in the parent columns with which to associate the new item. Empty cells in the dependent layout or its immediate parent layout result in creation failing.
The match is performed across all parent column cells. If one row in the parent table matches, it is used as the parent row. If more than one row matches, the first matching row is used. If no rows match, then the row is marked as "Create Failed".
When the operation finishes, the primary layout becomes active and the Status Viewer shows results for the primary layout as well as a summary for each layout in the dependent hierarchy.
Clear
When the clear operation is invoked, data is cleared from all the layouts in the dependent hierarchy.
Create a Set of Dependent Layouts
Create a set of dependent layouts for a hierarchy of business objects to three or more levels and link the layouts together.
The primary layout is always a Form-over-Table, one that's based on the parent business object (in the form) and a child business object (in the table). In our example, the form displays the details of a purchase order and the table lists the associated lines for this purchase order.
Additional Table layouts, created on separate worksheets, are based on descendant business objects, relative to the parent business object. For example, the first Table layout displays a grandchild business object (attachments, in our example), the next one displays either another grandchild business object (schedules) or a great-grandchild business object (distributions), and so on.
Note:
"Siblings" are business objects at the same level. You can link only one second-level sibling, in this case lines, to the parent since the primary Form-over-Table layout permits only one dependent object in the table. However, you can link all siblings at the third level or deeper to the same parent.For each Table layout you create, you can include a column from the immediate parent layout to help your users keep track of the parent record for each row in the table. For example, if ScheduleNumber is displayed as a column in the schedules layout, you can add it to the distributions layout to see which distributions are associated with this schedule.
To enable the creation of items in third-level and deeper layouts in a dependent layout hierarchy, add one or more columns to the layout from the layout's immediate parent. Including parent columns in the layout allows your business users to specify a unique parent for any new items they create. See Support for Creating Items.
Let's use the example in this section to create a hierarchy of dependent layouts that mirrors your business object hierarchy.
Configuration for your dependent layout is complete. You can choose to configure the workbook further to limit the data that is downloaded to the primary layout. See Configure Search Options for Download.
Before you publish and distribute your workbook to users, test the workbook to ensure that download, upload, or clear operations work on all layouts in the hierarchy. See Manage Data in a Dependent Layout in Managing Data Using Oracle Visual Builder Add-in for Excel.
Support for Creating Items
To enable the creation of items in third-level and deeper layouts in a dependent layout hierarchy, add one or more columns to the layout from the layout's immediate parent. Including parent columns in the layout allows your business users to specify a unique parent for any new items they create.
The parent columns you choose must uniquely identify the parent row and must be exposed in the parent table. For example, to create a new distribution, a user must be able to specify an existing line and schedule with which to associate the new item. Therefore, you would want to add columns such LineNumber and ScheduleNumber to both the distributions and schedules layouts.
If no parent columns are configured, the table cannot support item creation and rows inserted into the table are ignored during upload.
Add a parent column to a dependent layout using the Add Parent Column icon () in the Layout Designer's General tab.
The field must be exposed as a column in the parent table. If you don't see the field you want to use in the Available Business Object Fields window, you'll need to add it as a column in the parent table.
Repeat the step to add additional columns from the parent layout and then redraw the layout when you're done. The parent columns appear after the Status column in the layout in the order you added them. The table header for the parent column uses the format "<field title> (<parent business object title>)" such as "Line Number (lines)".
To clear the set of parent columns, use the Clear Parent Columns icon () next to the Parent Columns property.