Query between Site Collections using Nintex-SharePoint web services
I've 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:
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: http://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.
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, Lets update them in List using Nintex's Update List Item action.
MSDN Reference for GetListItems : http://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 http://gskinner.com/RegExr/
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.
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: http://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="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <GetListItems xmlns="http://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="http://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, Lets update them in List using Nintex's Update List Item action.
MSDN Reference for GetListItems : http://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 http://gskinner.com/RegExr/
No comments:
Please Login and comment to get your questions answered!