DX Problem Solving Assets

problem_solving_assets_banner.png
Expand all | Collapse all

Sharepoint List Library VBO - Odata queries

  • 1.  Sharepoint List Library VBO - Odata queries

    Posted 08-29-2019 17:09
      |   view attached

    Hi
    I am trying to utilize Sharepoint List Library VBO from Digital exchange. I tried to update few records/items in sharepoint but I am facing issues with customized Odata query. I tried with different HTTP methods in my query as well PUT, POST and PATCH but it didn't work. Can you please help us to share how we can update sharepoint data and what will be customized Odata query syntax?

    Here are few sample queries to pull/read data from sharepoint:
    getbytitle('Test List 1')/items?$filter=(FName eq 'FNameABC')
    getbytitle('Test List 1')/items?$filter=ID eq 5

    But for update, no luck. Attached is the screenshot of sharepoint.
    Update scenario: Update any item under FName column.




    ------------------------------
    Swati Gupta
    Senior Developer
    Tyson Foods
    Asia/Bengaluru
    ------------------------------


  • 2.  RE: Sharepoint List Library VBO - Odata queries

    Posted 09-04-2019 15:04
    Edited by Bimal Sebastian 09-04-2019 15:07
    Hi Swati,

    OData is generally used with Gets since its a query. 

    Inorder to run an update, you will need to split you API call into two pieces. Post API calls.

    Part 1: Getting the "ListItemEntityTypeFullName". This is needed to update items in a list. The reference implementation on how to get this value is available in the sample process in the DX along with this skill. Refer the Create Item Page where there is an action added to get this value. you will not need to create a new APi for this, since this is already present. you only need to use the same action by referring to the create item page in the reference process.

    Part 2: Once you have this value, you will need to create a request in the same lines as the Create items APi call. The only changes that will be needed are, the Request endpoint,  Headers of the request and the parameter "[Item Fields and Values]". The template and all other values will remain same as the create items request.

    The changes needed are below,

    A. Request End Point (notice the item id, ensure you have the right id)

    http://site url/_api/web/lists/GetByTitle('Test')/items(item id)

    B. Headers(these are not present for create items, but you will need it for update)

    "IF-MATCH": etag or "*"    "X-HTTP-Method":"MERGE"

    C. [Item Fields and Values] : Keep everything same. in the value field just make sure the updated values are present.

    ------------------------------
    Bimal Sebastian
    Consultant
    Blueprism
    Asia/Kolkata
    ------------------------------



  • 3.  RE: Sharepoint List Library VBO - Odata queries

    Posted 09-04-2019 16:08

    Bimal,

     

    Thank you for the info.  Independently from Swati, I had gotten as far as creating an Update Item API as you've described.  However, we are getting the following error.

     

    Internal : Unexpected error Error during Web API HTTP Request

    HTTP Status Code: 400

    HTTP Response Content: {"error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The type SP.ListItemEntityCollection does not support HTTP PATCH method."}}}

     

    Here is the set up:

     

     

    I have tried both "etag or *" and simply "*"

     

     

     

     

     

    Tyson Logo

    Tyson Logo

    Ryan Godsey, Sr. Developer Applications
    Applied Technology & Innovation, Automation

    Tyson Foods
    319 E Emma Ave, Springdale, AR 72764
    479-290-1097
    ryan.godsey@tyson.com

     

     


    This email and any files transmitted with it are confidential and intended solely for the use of the addressee. If you are not the intended addressee, then you have received this email in error and any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. Please notify us immediately of your unintended receipt by reply and then delete this email and your reply. Tyson Foods, Inc. and its subsidiaries and affiliates will not be held liable to any person resulting from the unintended or unauthorized use of any information contained in this email or as a result of any additions or deletions of information originally contained in this email.





  • 4.  RE: Sharepoint List Library VBO - Odata queries

    Posted 09-04-2019 16:53
    Hi Ryan,

    It appears you have gotten almost all things right. 
    I believe the error message is sort of generic for multiple types of errors in sharepoint. (I hate that about sharepoint.)
    Anyway, over the top of my head i can think of the following potential reasons,
    1. Missing form digest value in header ( i know it may not be needed for create item, but sharepoint is sort wierd that way. try this) 
    2. May be there are quotes missing in the http request formed. Try quotes between the itemId, or in the header values like say "MERGE" rather than just MERGE.

    Have you tried capturing the request data through fiddler or other tools. If, so can you paste the raw http msg here.

    I will try this independently at my side when i get some time. Will keep this thread posted if i find something.


    ------------------------------
    Bimal Sebastian
    Consultant
    Blueprism
    Asia/Kolkata
    ------------------------------



  • 5.  RE: Sharepoint List Library VBO - Odata queries

    Posted 09-04-2019 21:53
    Adding the Form Digest Value header and an adjustment to how we pass the field/value pairs in did the trick!  

    Where create wanted this format with the double quotes surrounding the whole thing ("'Title' : 'API Test 1', 'FName' : 'FName 1', 'LName' : 'FName 2', 'Gender' : 'Male' "), update wanted us to pass it with double quotes around the field/value parts and no quotes around the entire string ("Title": "API Test1", "FName": 'FName 1").

    ------------------------------
    Ryan Godsey
    Sr. Developer Applications
    Tyson Foods
    America/Chicago
    ------------------------------



  • 6.  RE: Sharepoint List Library VBO - Odata queries

    Posted 09-05-2019 16:26
    Thank you Ryan and Bimal. It's working now

    ------------------------------
    Swati Gupta
    Senior Developer
    Tyson Foods
    Asia/Kolkata
    ------------------------------



  • 7.  RE: Sharepoint List Library VBO - Odata queries

    Posted 09-20-2019 17:35
    Ryan, Bimal and Swati - Would you mind sharing how you determine your REST authentication method for this skill in your solution?​ Looking at the documentation, there are 2 methods available: ACS based authentication and Azure AD Certificate. They seemed to require you to have admin access to the root site of your company.

    The list I'm looking to update belongs to a subsite in SharePoint. I doubt our enterprise SharePoint admin would be willing to give me access to the root site of the company which host all other sites within the company. Does bot need to have same privileges as developer in order to execute the REST calls? What's the sufficient access to develop and execute in this context? Are there any dependencies you need to maintain separately for production, code & environment?

    Maybe it's just I'm ignorant on this topic. Hope to get your thoughts soon.




    ------------------------------
    Huy Trinh
    Software Engineer II
    Metlife
    America/New_York
    ------------------------------



  • 8.  RE: Sharepoint List Library VBO - Odata queries

    Posted 26 days ago
    Hi Huy Trinh,

    You really dont need access at the root site level. You can have the privileges maintained at the subsite level for both authentication modes.
    They key difference between both the auth mode is that Sharepoint ACS is a legacy mode of authentication, which will soon be depricated by Microsoft. The Azure AD based auth is more modern and can be managed through Azure and I recommend this model.

    Now, the sufficient access really comes down to the action you intend to perform. If its only reading lists for example, then a read only access would suffice. If you intend to upload files, then you will need a Write access.

    However, key consideration here is, API access to sharepoint will give permissions to the ClientId holder at site or sub site level only unlike UI based automation, where you could limit access at folder level as well.

    Thanks
    Bimal

    ------------------------------
    Bimal Sebastian
    Consultant
    Blueprism
    Asia/Kolkata
    ------------------------------



  • 9.  RE: Sharepoint List Library VBO - Odata queries

    Posted 2 days ago
    Hi Ryan - I tried with your additional steps, but its same error.

    My request looks like below.

    { '__metadata':
    { 'type': '[List Item Type Name]' },
    "'LineItemNumber':'Closed'"
    }

    I am passing header as below.

    IF-MATCH etag or *
    X-HTTP-Method MERGE
    X-RequestDigest [Form Digest Values]


    ------------------------------
    Sankar Sarma
    RPA Consultant
    Lion Co
    ------------------------------



  • 10.  RE: Sharepoint List Library VBO - Odata queries

    Posted 2 days ago
    Edited by Swati Gupta 2 days ago
      |   view attached
    Hi Sankar,

    The header looks like the attached file.Share the error message. Ryan has shared all the screenshots and your request should looks like 

    { '__metadata':
    { 'type': '[List Item Type Name]' },
    [Item Fields and Values]
    }

    Thanks,
    Swati 
    Tyson Foods



    ------------------------------
    Swati Gupta
    Senior Developer
    Tyson Foods
    Asia/Kolkata
    ------------------------------



  • 11.  RE: Sharepoint List Library VBO - Odata queries

    Posted 2 days ago
    Edited by Sankar Sarma 2 days ago

    Hi Swathi - Here is my setup.

     



    Hard coded the item/value pair to show the format.

     

     

     

     

    Error Message

     

     

     

     

     

     

     

     

    Sankar Sarma

    RPA Analytics Leader


    Please consider the environment before printing this e-mail.

     






  • 12.  RE: Sharepoint List Library VBO - Odata queries

    Posted 2 days ago
    This issue is fixed now, problem was unwanted space in the body. also I am passing the item key/value with out double quotes.

    ------------------------------
    Sankar Sarma
    RPA Consultant
    Lion Co
    ------------------------------



  • 13.  RE: Sharepoint List Library VBO - Odata queries

    Posted 16 hours ago
    Thanks so much for sharing the solution!

    ------------------------------
    Melanie Giuliani
    Analyst
    CA Technologies
    America/New_York
    ------------------------------