Visual Builder

Get Involved. Join the Conversation.

Topic

    Stephen Bryant
    upsert based on another field
    Topic posted June 27, 2019 by Stephen BryantBronze Trophy: 5,000+ Points, tagged Business Objects, Custom Code, REST, SaaS Integration, Validation 
    69 Views, 10 Comments
    Title:
    upsert based on another field
    Summary:
    Posting to Business Object from ICS
    Content:

    Hello,

    I am extracting data from our SaaS system via an OTBI Report.  This works nicely.  However, I have an identifying field in the report called, CCID.  This is an id present in the SaaS system that I would like to use as the field for upsert to check.  So, is there a way to base upsert on another field?  The id generated in the Business Object will not work in this case.  If there is another way to check not using upsert, that works too.

    Thanks,

    Steve

    Version:
    19.1.3

    Comment

     

    • Shay Shmeltzer

      Yes you can use a service method for this see this blog:

      https://blogs.oracle.com/vbcs/service-methods-for-business-objects-in-visual-builder

      • Stephen Bryant

        Shay,

        I am attempting to do a bulk create/post in POSTMAN based on your video here: https://blogs.oracle.com/shay/working-with-batch-rest-calls-to-business-objects-in-visual-builder.  The difference is I would like to do a bulk post operation but I am not sure how to structure the JSON.  Here is my JSON:

        {

        "parts": [

        {

        "operation":"create",
        "path":"glBalances",

        "payload": {

        "period": null,
        "concatenatedSegments": null,
        "fund": "2018.2018.0100D",
        "account": "411900.02",
        "program": "000000",
        "activity": "0000",
        "object": "000000",
        "bfy": "2018",
        "org": "000",
        "begBal": null,
        "drBalance": 2500111,
        "crBalance": 0,
        "ccid": "1232453900"

        }

        },

        {

        "operation":"create",
        "path":"glBalances",

        "payload": {

        "period": null,
        "concatenatedSegments": null,
        "fund": "2018.2018.0100D",
        "account": "411900.02",
        "program": "000000",
        "activity": "0000",
        "object": "000000",
        "bfy": "2019",
        "org": "000",
        "begBal": null,
        "drBalance": 2500111,
        "crBalance": 0,
        "ccid": "1232453900"

        }

        }

        ]

        }

        Thanks,

        Steve

      • Stephen Bryant

        Shay,

        Yes, I knew that and figured it was the issue but what I need to do is send the entire payload once and have it bulk update the Business Object (from Integration Cloud).

        Can I do that from integration without having to pass an id?  This is simply a create to test out the bulk update.  I just need a little guidance on bulk creates.

        • Shay Shmeltzer

          Just to clarify the id here is an id for an operation - it is not the id of the record.

          Just come up with a unique id for each operation.

           

      • Stephen Bryant

        Thanks Shea.  That helps.

      • Stephen Bryant

        Shay,

        Here is the payload I am using.  Upsert-Mode is True and the content-type is for batch.  Postman returns a 200OK but the business object is not updated.

        Any ideas?

        {

        "parts": [

        {

        "id":"part1",
        "path":"/glBalances/98",
        "operation":"update",

        "payload": {

        "period": null,
        "concatenatedSegments": null,
        "fund": "2018.2018.0100D",
        "account": "411900.02",
        "program": "000000",
        "activity": "0000",
        "object": "000000",
        "bfy": "2018",
        "org": "000",
        "begBal": null,
        "drBalance": 2500111,
        "crBalance": 0,
        "ccid": "1232453900"

        }

        },

        {

        "id":"part2",
        "path":"/glBalances/99",
        "operation":"update",

        "payload": {

        "period": null,
        "concatenatedSegments": null,
        "fund": "2018.2018.0100D",
        "account": "411900.02",
        "program": "000000",
        "activity": "0000",
        "object": "000000",
        "bfy": "2019",
        "org": "000",
        "begBal": null,
        "drBalance": 2500111,
        "crBalance": 0,
        "ccid": "1232453900"

        }

        }

        ]

        }

         

      • Stephen Bryant

        Shay,

        Okay.  It works if I use operation=update and pass the record ids in the path.  However, with Upsert-Mode set to "true" and operation = upsert and ids in the path, I get an "invalid operation for the resource.  It works though if I use operation=create or operation=upsert with no ids.  

        Upsert basically works as a create only and will not update if an id is provided.

        Thanks,

        Steve

        • Shay Shmeltzer

          Upsert is using the POST operation - which is basically a create operation.

          If you provide an existing id of a record in the body of the message it switches to do an update.

          Documented here:

          Updating or Creating a Business Object Item (Upsert)                               

          • Stephen Bryant

            Shea,

            Okay, I figured it out!   The id is not provided on the URI but in the body of the payload like this:

            "parts": [
             
            {
             
            "id":"part1",
            "path":"/glBalances", -----------not here
            "operation":"upsert",
            "payload": {
             
            "id":1,         ------------------------------here
            "period": "May-19",
            "concatenatedSegments": "2
             
            This allows upsert to switch between create and update.  That was not clear in the documentation.
             
            Thanks for your help,
            Steve