Visual Builder

Get Involved. Join the Conversation.

Topic

    Divya Sharma
    Upsert data to Business Object in batch upload
    Topic posted September 25, 2019 by Divya Sharma, tagged Business Objects, Composite Component, Custom Code 
    65 Views, 11 Comments
    Title:
    Upsert data to Business Object in batch upload
    Summary:
    Unable to create batch request(upsert) for custom business objects
    Content:

    I need to perform insert or update operation based on the user input received while processing batch upload. I have the prototype working for single request but need multiple records to be inserted in a single user action. I have created a business object function to update/insert a record to a business object. When I am using the newly created POST endpoint to update/insert the entry to the BO, it is working fine for single request. However I am unable to create a batch request for the same.

    Version:
    19.1.3

    Comment

     

    • David Konecny

      According to this documentation only "create, update, get, delete, invoke custom methods and describe requests" are supported. Not upsert.

      -David

    • Divya Sharma

      I am looking for a solution that can be used to upsert data to BO using batch uploads. It will be a difficult user experience to provide separate batch operations for inserts and updates.

      • David Konecny

        What makes you think it will be more difficult without upsert support? One Batch REST call can contain combination of create, update and delete parts. And while Upsert makes it a bit easier you can replace it easily by using create or update based on presence of ID field value of object you need to store - ID is set -> update; ID is not set -> create

        -David

         

    • Divya Sharma

      Checking for whether the unique key column is already present in the BO or not while creating a payload will cause a lot of performance overhead. Do you suggest creating a Business Object Function to mark out the records that need to be updated as against the ones that are new insertions? Please suggest if there is an efficient way to achieve this,

      • David Konecny

        Could you please describe in more detail what you are trying to build? What is your usecase you are solving and how does Batch REST support and object functions going to be used?

        Batch REST support is useful, for example, when you load multiple records into memory and modify them and want to store changes as one transaction (for example Invoice and InvoiceLine scenario, that is parent with multiple child records). But in such case, because all data were fetched into memory first, you have full knowledge of what exists in the backend (and will need to be Updated) and what are new entries (which will need to be Created) purely based on whether (using earlier example) whether currentInvoice.ID and currentInvoiceLine[index].ID are set or not.

        -David

    • Divya Sharma

      Hi David,

      The use case that I need to address is there is a table on the page which maps to one Business Object, which has a unique key column. There will be an upload button on the page with which the user can upload a csv file. Once the csv file is uploaded, the file data will be split into batches and uploaded into the database table. While processing, in case the data present in csv maps to data in the unique key column then that entry will be updated else a new entry will be created in the BO.

      • David Konecny

        Thank you Divya, now I understand. You are synchronizing VB data with some external DB (using CSV import). For that, Upsert is needed because data are coming from external DB. One way to workaround lack of Upset support in Batch REST could be this: when you split records into multiple batches you could for each batch create a REST call like this: 

        /resources/data/YourVBBusinessObject?fields=id&q=id in (id1, id2, id3, id4)&limit=sizeOfYourBatch&onlyData=true

        that is, collect all IDs from external records and try to fetch corresponding VB records for them in single call. The call would return array of records which exist in VB and which needs to be updated instead of created. Whether this is suitable for your usecase depends on number of records being synced. If it is thousand or so this may work fine.

        -David

    • David Konecny

      Divya,

      I just heard from the development team that in spite of the documentation link I sent you the "upsert" is supported in the Batch REST, for example:

      {
          "id" : "part2",
          "path" : "/departments",
          "operation" : "upsert",
          "payload" : {
            "Deptno" : 81,
            "Dname" : "ENGINEERING",
            ...
          }
      

      Sorry for confusion and taking so much time to figure this one out,
      -David

    • Divya Sharma

      Hi David,

      Thanks for your suggestions. When I am trying to build the payload using operation as upsert, there are two behaviors that I am observing.

      1. Incase there is a unique key column defined in the table, I am getting the error: "ORA-00001: unique constraint (SP1551379345_VB_E5V1ES7ERRJ.UK_SAMPLEBO_STRINGCOL1) violated\n"

      2. Incase there is no unique key column defined in the table, the data is getting inserted . However the existing row does not get inserted, rather a new row is created with similar data.

      The upsert operation is working if the correct id of the record being updated is provided in the payload.

      {
        "parts": [
          {
            "id": "part1",
            "path": "/SampleBO",
            "operation": "upsert",
            "payload": {
            "id":2,
              "stringCol1": "Row1Col1", 
              "stringCol2": "111", 
              "numberCol3": "1234", 
              "dateCol4": "2019-05-30"
            }
          }
        ]
      }
       
      However, using the id of the record for uploads will not be a possible option at all times. Please let me know in case there is a way to work around this issue.
      Divya.
      • David Konecny

        The constrain which is failing is "UK_SAMPLEBO_STRINGCOL1" which is not primary key constraint (assuming "id" is the primary key field) but unique value constraint.

        Upsert does not do any magic - it decides whether to Insert or Update based on presence of primary key value (the "id" property). If the id value already exists in DB table it updates the record. Else it is insert. Your CSV batch import needs to contain correct IDs in order to update existing records. Otherwise your solution cant be implemented.

        -David