11 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 this example hierarchy of business objects, where purchaseOrders is the parent, lines is the child, schedules is the grandchild, and distributions is the great-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 and each of these schedules may include one or more distributions for managing accounting or project details.
To create a set of dependent layouts, create a Form-over-Table layout for the first two levels (purchaseOrders and lines) and separate Table layouts for each subordinate level (schedules and distributions). Then link each dependent layout to its direct parent layout.
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 grandchild layout, 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 ignored.
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 the grandchild business object (schedules, in our example), the next one displays the great-grandchild business object (distributions), and so on.
Note:
Sibling relationships (two or more layouts with the same parent layout) are not supported.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.