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

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

Your email address will not be published. Required fields are marked *