Updating a lookup field referring to another site using HTTP Web Service in a SharePoint Online workflow – revisit

Updating a lookup field referring to another site using HTTP Web Service in a SharePoint Online workflow – revisit

Today I had to diagnose why a workflow created using SharePoint Designer was not working as expected. I ended up fixing the issue so I decided to document my steps for my own use or for anybody else who can find it useful.

A bit of background:

We have a contacts list called Clients, a calendar list called Events, and on a subsite we have a document library called Invoices. Each client can have mutiple related events, and each event can have multiple related invoices. On the Clients list Display form we wanted to be able to list related invoices using the Related List webpart. To do this, we needed to have a lookup field on the Invoices library to relate it directly to the Clients list, even though it’s aleady indirectly related by virtue of the event that is assigned to the invoice.

So the idea was to use a workflow to update the Clients lookup on the Invoices library:

  1. From the current list’s Event lookup field value, get the lookup Id of the event.
  2. Using the event lookup Id from step 1, get the lookup Id of the client from the Client field value on the events list that exist on the parent site.
  3. Update the Client field in the current list to the value from step 2.

Seems easy enough…

Right away things didn’t work as expected when I tried to get the value of the Event field in the current item. Because this lookup field was pointing to the list on a parent site, I got this:

The field ‘EventLookup’ is not supported in query. The lookup list is in another web.”

So, as a workaround, to get the value, I used the HTTP Web Service action:

Workflow1

 

Now I needed to get the value of the Client Lookup id from the Events list located in the parent site. Because the list was on another site, I had to use HTTP Web Service action inside of the App Step. To enable the App Step I had to activate the “Workflows can use app permissions” feature in the site settings. By default workflow app has permissions to access only a current site, so I also had to give it permissions to read from another site. To do that, go to the Site Settings->Site App Permissions and copy the app id only:

SiteAppPermissions

Then go to the http://<SharePointWebSite>/_layouts/15/AppInv.aspx page, enter this app id and click Lookup. (Make sure you’re in the site where your workflow is). Info about your app should get displayed. In the Permission Request XML textbox enter the following and click Create:

<AppPermissionRequests>
      <AppPermissionRequest Scope=”http://sharepoint/content/sitecollection” Right=”Read”/>
</AppPermissionRequests>

This ensures that our workflow has Read permissions on another site.

Now back to the workflow. Add the App Step to the workflow and use HTTP Web Service to get the Client Lookup Id value:

WorkflowAppStep

To update the lookup field, again, we’ll have to use HTTP Web Service, otherwise we’ll get the “The lookup list is in another web” issue if updating the lookup field in a current item.

Here is the final step:

Workflow2

 

headerForUpdateDict variable contains the following values:

accept string  application/json;odata=verbose
 content-type  string  application/json;odata=verbose
 IF-MATCH  string  *
 X-HTTP-Method  string  MERGE

metadataDict has the following values:

type string SP.Data.InvoicesItem

The value for the type parameter we can found by executing a REST call to get information of the list we are trying to update: https://<SharePointWebSite>/_api/web/lists/GetByTitle(‘yourlisttitle’). In the result find a value for the ListItemEntityTypeFullName.

parametersDict  variable has the following values:

__metadata dictionary Variable: metadataDict
 ClientLookupId  Integer Variable: ClientId

The field ClientLookupId is the internal name of the lookup field we’re updating with “Id” appended.

Make sure HTTP method is set to Post. Run the workflow and verify that the Lookup field is updated.

That’s it! A useful workaround to get around the issues that result from having a lookup field that refers to a list on a parent site.

See more on updating lists using REST: https://msdn.microsoft.com/en-us/library/office/dn292552.aspx#ListItems

On app permissions: https://msdn.microsoft.com/en-us/library/office/fp142383.aspx