Query between Site Collections using Nintex-SharePoint web services
I have got a Global approval matrix (Just a SharePoint List with Approver name, Range & their respective Entity) in a site collection. For a workflow requirement, I had to retrieve the approver(s) and their E-mails based on Entity and Credit amount from Global approval matrix List in a different site collection.
Here is my global approval matrix List:
Again, Nintex and SharePoint web services comes to rescue.I designed the Nintex workflow to achieve this. and here, from the Nintex workflow, I’m giving the Query & Update chunks alone.
Lets Begin with these Nintex Variables:
I used 3 variables:
- varApproverMatrix -To hold the XML from web service
- varApprovers – To hold the approver(s)
- varEMails – To hold the approver’s Emails.
Query Approval Matrix List:
We’ll be using GetListItems SharePoint web service to retrieve List items of the another site collection. So Lets drop a Call web service action and enter the URL as: https://Your-SharePoint-Site.com/_vti_bin/Lists.asmx, and supply the user name & password, and then send the resulted XML to our variable: varApproverMatrix
We’ll update the SOAP XML for our requirement:
Click on Edit SOAP link and enter the below XML in it.
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:soap="https://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetListItems xmlns="https://schemas.microsoft.com/sharepoint/soap/">
<listName>1a4a5f23-c4e5-4950-87a9-e7d323168532</listName>
<viewName>3374CB21-1D43-4544-A4E5-18BD9D3B26DA</viewName>
<query>
<Query>
<Where>
<And>
<Eq>
<FieldRef Name='BP_x0020_Entity'/>
<Value Type='Text'>{ItemProperty:Entity}</Value>
</Eq>
<And>
<Leq>
<FieldRef Name='From_x0020_Amount' />
<Value Type='Number'>{ItemProperty:Credit}</Value>
</Leq>
<Geq>
<FieldRef Name='To_x0020_Amount' />
<Value Type='Number'>{ItemProperty:Credit}</Value>
</Geq>
</And>
</And>
</Where>
</Query>
</query>
<viewFields>
<ViewFields>
<FieldRef Name="Approvers" />
<FieldRef Name="EntityInbox" />
</ViewFields>
</viewFields>
<rowLimit>99999</rowLimit>
<queryOptions xmlns:SOAPSDK9="https://schemas.microsoft.com/sharepoint/soap/" >
<QueryOptions/>
</queryOptions>
<webID></webID>
</GetListItems>
</soap:Body>
</soap:Envelope>
Here the <
query
>
tag is the filter and <
viewFields
> tag
is for the fields we would like to retrieve.
Remove Extra Chars from User Name:
We need this step in order to replace some extra character’s in the Approver’s name. Internally, SharePoint stored the User names in this format: ID;#FirstName, LastName. So, lets get rid of ID;# using Regular expressions.
Query Approvers Field:
We retrieved the List data in XML format. Lets add a Query XML action to query using XPath and save the values to varApprover.
Update List Items:
Finally, we retrieved user names and E-Mails, let’s update them in the List using Nintex’s Update List Item action.
MSDN Reference for GetListItems : https://msdn.microsoft.com/en-us/library/lists.lists.getlistitems%28v=office.12%29.aspx
I used CAML Query builder to build the query in SOAP, For Regular Expression, I used https://gskinner.com/RegExr/