Sharepoint List Library VBO - Odata queries

[SwatiGupta]

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
------------------------------

[BimalSebastian · Accepted solution]

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
------------------------------

[RyanGodsey]

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 "*"

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.

------Original Message------

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
------------------------------

[BimalSebastian]

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
------------------------------

[RyanGodsey]

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
------------------------------

[SwatiGupta]

Thank you Ryan and Bimal. It's working now

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

[HuyTrinh]

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
------------------------------

[BimalSebastian]

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
------------------------------

[SankarSarma]

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
------------------------------

[SwatiGupta]

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
------------------------------