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:

1. varApproverMatrix -To hold the XML from web service
2. varApprovers – To hold the approver(s)
3. 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: 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, let’s update them in the 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/