Visual Builder

Get Involved. Join the Conversation.

Topic

    Andy May-Coates
    How to handle multiple nested collections in Visual Builder...Answered
    Topic posted November 18, 2019 by Andy May-CoatesBronze Trophy: 5,000+ Points, last edited November 18, 2019, tagged Business Objects, Layout, REST 
    52 Views, 13 Comments
    Title:
    How to handle multiple nested collections in Visual Builder add-in for Excel
    Summary:
    How to handle multiple nested collections in Visual Builder add-in for Excel
    Content:

    In the Oracle Visual Builder add-in for Excel, a form-over-table layout can be used to support parent-child business object relationships e.g. supplier and supplier site.

    However, we have a REST API where there are multiple nested child collections, and properties need to be supplied at each level to be of any use. For example:-

    https://***.oraclecloud.com/fscmRestApi/resources/11.13.18.05/financialProjectPlans

    This has several nested levels of child collections, and properties from each level need to be included to bulk update a financial project plan resource assignment. For example:-

    Project level detail would come from the top level "financialProjectPlans"
    Task and resource detail would come from the next level "ResourceAssignments"
    Currency and plan amount totals come from the next level "PlanningAmounts"
    Periodic plan amounts come from the lowest level "PlanAmountDetail"

    What we really need is a table layout containing properties from all four levels above, where parent properties would simply fill down/duplicate.

    On a related note, when we import the swagger for the above service, the plugin only shows the top three levels (see image below). This seems to be the case for other REST APIs. Is this a product limitation? Is there any workaround to this either?

    Any help greatly appreciated.

    Version:
    Oracle Visual Builder Add-in for Excel 2.0.0.20643
    Image:
    Document:

    Best Comment

    Alex Davis

    Andy, the fix is available now. Please visit Doc ID 2349497.1 for more information.

    Hope this helps!

    Comment

     

    • Alex Davis

      Great questions, Andy. Thanks!

      What we really need is a table layout containing properties from all four levels above, where parent properties would simply fill down/duplicate.

      With the current version of the add-in, you need a new endpoint from the SCM team that flattens out the object hierarchy (denormalizes) into a nice rectangular structure that fits well into an Excel worksheet.

      If you cannot get a new endpoint, one alternative is to use multiple layouts. So, you could have resource assignments on one worksheet and planning amounts on the next one. Version 2.0 supports tables bound to parameterized URLs. So, when you pick PlanningAmounts, you can still select table. Of course, you need to coordinate the parent keys and upload each layout separately. But, it should work fine.

      We'll take a look at your swagger doc and see what happened to plan amount detail. Stay tuned...

      • Andy May-Coates

        Thanks for the swift reply Alex. I can raise Ideas in the relevant Idea Labs of CCC to flatten the services, but given this type of nested structure is common to nearly all FSCM APIs, I think we will need to try and accommodate as-is.

        Thanks for the excellent suggestions regarding the multi-sheet/table approach; do you have any documentation, MOS note or examples you can point me at?

        Cheers,

        Andy.

        • Alex Davis

          Just go to Sheet 1 and create a table with financialProjectPlans.

          Then, go to Sheet 2 and create a table with ResourceAssignments

          And so on...

          When you download Sheet 2 (with ResourceAssignments), you will be prompted to type/paste the PlanVersionId

          That's all there is to it.

          • Andy May-Coates

            Thanks, I'll try that.

            Given the PlanVersionId is a surrogate key, we're hoping to make something a little more user-friendly e.g. download financialProjectPlans based on a business key (Project Name), then move to sheet 2, and download all the ResourceAssignments for the corresponding PlanVersionId, the same then with Plan Amounts and Plan Amount Details. So we only need to pass the PlanVersionId to each sheet, plus any path keys (multiple) as required. I'll have to see what we can do with that and whether any of this is possible.

            I suppose the fall-back position is to do something more sophisticated with VBA, using post-download macro capability from the add-in.

            Cheers,

            Andy.

    • Alex Davis

      While we review that swagger doc, you may want to try starting with this URL:

      https://***.oraclecloud.com/fscmRestApi/resources/11.13.18.05/financialProjectPlans/describe

      I'll be curious to see if you get a different (better?) result.

    • Alex Davis

      Nevermind. That new URL produces the same result. Need to dig some more to find the root cause...

      • Andy May-Coates

        I tried the swagger file and API describe, but as you have found, they yield the same results. I’ve noticed the same for other ERP Cloud APIs, hence me beginning to think it might be a current limitation of the add-in?

        Cheers,

        Andy.

        • Alex Davis

          Yes. The missing great-grandchild BO, PlanningAmountDetails, is simply a bug. It should be fixed in the next release. Thanks for reporting it.

          • Andy May-Coates

            Ah, glad I could help ;)

            So the big question then (given we go live in January), when is the next version of the add-in generally available?

            Andy.

            • Alex Davis

              I'm afraid I can't make any public comments around future release dates. Sorry. Hope you understand.

              I can say that our release cycles are relatively short in most cases.

            • Alex Davis

              Andy, the fix is available now. Please visit Doc ID 2349497.1 for more information.

              Hope this helps!

    • Andy May-Coates

      I'll raise an Idea in the Ideas lab for handling nested collections (without having to rely on Fusion Apps product development to flatten all of their services).

      I think if the query parameter feature of the add-in could reference Excel named ranges/cells (like a bind variable), then we could create something a little more dynamic when downloading different collection levels to separate sheets.

      Andy.