Vadim Tabakman

When you have a large number of items in a list and you need to query them, sometimes querying the whole lot using the Query List action is not the best practice. Depending on the number of items, you could potentially get a timeout on the workflow that will process each one.  The best practice maybe to query the list using paging.

Paging is best described as processing in pages or blocks of data.  In this case, we are processing a number of items at a time.

eg.  Let's say you have 25 items.

You could process these in lots of 10.

It's end up looking like this:

Process - 10 items

Process - 10 items

Process - 5 items

Currently, the Query List action allows you to set the row limit of your results.  But this will always return that number of items from the beginning of the result set.  There's no way of getting the next set of results using that action.

To solve this conundrum, we can instead call the SharePoint Lists.asmx web service, specifically the GetListItems web method.

GetListItems and Paging

GetListItems supports not only RowLimit but also Paging.  In the QueryOptions node of the CAML query, there is a <Paging> node that has a "ListItemCollectionPositionNext" attribute.  For the first call to GetListItems, you can leave this empty.  But the data you get back will contain a ListItemCollectionPositionNext attribute, and you can use that in the following call to GetListItems.

With each call to GetListItems, retrieve the ListItemCollectionPositionNext and use it in the next call, etc, etc.

Solution

To make the usage of GetListItems more reusable and to not require workflow designers to know about Web Services or know about high level credentials, we can hide this functionality into a Nintex Workflow User Defined Action.

The User Defined Action will take 3 input parameters:

1. ListName - name of the list you want to query

2. RowLimit - number of items you want to get back per page

3. Paging - text used to get to a specific page of data

The User Define Action will also take 2 output parameters:

1. PagingOutput - this is the value of ListItemCollectionPositionNext that is returned from the web service call

2. XMLResult - the data returned from the web service call

 User Defined Action

The User Defined Action will make the web service call.  It will also parse the results from the call to extract the ListItemCollectionPositionNext so that we can make further calls.

When there is no more data to retrieve, this attribute will be empty. 

The Web Service SOAP packet looks like this:

<?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>{WorkflowVariable:ListName}</listName>
            <viewName></viewName>
            <query></query>
            <viewFields></viewFields>
            <rowLimit>{WorkflowVariable:RowLimit}</rowLimit>
            <queryOptions>
              <QueryOptions>
                <Paging ListItemCollectionPositionNext="{WorkflowVariable:Paging}"></Paging>
              </QueryOptions>
            </queryOptions>
            <webID></webID>
        </GetListItems>
    </soap:Body>
</soap:Envelope>

The Nintex Workflow we created to call this user defined action, will go into a loop and continue retrieving data, parse the data and keep doing this until it finishes processing all the data.

Call the UDA looks like this :

Call UDA

If you have any questions regarding how the workflow works, shoot me a message.

Downloads 

Copyright Vadim Tabakman
Rate this Content 0 Votes

Comments

Wednesday, November 7, 2012 7:54:59 AM
Dave Kuehling

re: Nintex Workflow - Querying a List with Paging

Hi Vadim!

Frist, great post! This is just what I needed to work through a large list of 500k items. And while it takes a bit of time processing 4500 at a time, it does the job!

I have been doing some testing with this list, and used what you started to work though that list and then "update" a subset of about 32,000 items.

Now, I have a few questions for you relating to large lists and workflows. For starters, do you have any guidance on if it's better to use a lower number such as 50 or 100 for the RowLimit input or something larger like 2500 or 4500? I am trying to test different values, but working with such large numbers the process is a bit slow between attempts. My last was with 4500, now I am trying 50.

Additional, for this workflow the first 32,000 items go into three different collections based on their ID range. The first collection contains 5,000 items, the second 9,000, and the third 18,000. I can test this and get the counts and contents back fine. I then "attempted" to process the updates for these items in parallel. Each collection was processed through about the first 150 items, then the workflow failed. I am rerunning it now with the collections being processed in series to see if I get different results. Do you know if:

Its possible (or advisable) to run updates on the same list in parallel?There is a collection item limit?

Thank you in advance for your time. And thanks again for great posts like this one!

Regards,

Dave Kuehling

Wednesday, November 7, 2012 3:58:23 PM

re: Nintex Workflow - Querying a List with Paging

Hi Dave,

I'm not sure if there's an ideal number of rows to return using the query.  I think it might be environmental, so you would have to test it out and see how it behaves in your environment.

Also, for making so many changes, I'd recommend doing this outside of business hours.

As to the error, I guess I'd have to see what the error is. If it's something vague, take a look in the SP logs for "Workflow Infrastructure" error messages around that time.  Hopefully that'll point you in the direction of the cause and how to fix it.

If you are updating differet items, then I don't see an issue with that.  But if you are trying to update the same item, with SharePoint batching, you may come across failures.

cheers,

Vadim

Tuesday, December 18, 2012 2:35:23 AM
Jay

re: Nintex Workflow - Querying a List with Paging

Hi,

I encountered below issue. Please advise.

12/18/2012 5:32 PM Comment Error operating on collection variable. Index property is greater than the number of items in the collection. Error
12/18/2012 5:32 PM Error System Account An error has occurred in GetListItems Paging Test Workflow.

 

Tuesday, December 18, 2012 11:05:18 PM

re: Nintex Workflow - Querying a List with Paging

Hi Jay,

I'd say that querying for the data is not returning anything and you're trying to do something to a collection variable at a position where there aren't that many items in the collection.

cheers,

Vadim

Sunday, January 27, 2013 2:12:18 AM
Alex

re: Nintex Workflow - Querying a List with Paging

Hi, Vadim.

Why did you use "Set condition" with one branch instead of "Run if" action?

 

Thanks

Sunday, January 27, 2013 1:40:56 PM

re: Nintex Workflow - Querying a List with Paging

Hi Alex,

for the workflow I was working on at the time, I had plans to handle the No condition.  Other than that, yeah you would use the Run If action to simplify this a little.

Cheers,

Vadim

Thursday, May 23, 2013 6:27:46 AM
unknown

re: Nintex Workflow - Querying a List with Paging

Kindly can u attach that workflow.because i m not getting it how to do

Thursday, May 23, 2013 7:38:49 AM

re: Nintex Workflow - Querying a List with Paging

Hi,

the workflow is attached to this post.  It's in the orange box and is the last link there.  Just right-click and save the NWF file to your machine.

Cheers,

Vadim

Friday, August 9, 2013 1:10:15 PM
Juli

re: Nintex Workflow - Querying a List with Paging

Hey, hoping that this will help me with a conundrum that I have.    I have a list of about 6500 items that was create from Access.  These are active records.   We need to get this into a form library.  What I was hoping to be able to do was to create a workflow that would query the list and then using a 'for each' action update the fields in the form library from each record in the list.  I am not familiar with caml queries or coding them so I need some assistance.

Please tell me I am not bashing my head against a tree here!

Tuesday, August 13, 2013 10:44:30 AM
Juli

re: Nintex Workflow - Querying a List with Paging

I downloaded the nwf file to get a good look into this but I can't look at the GetListItem Next Page UDA.   When I go into configure it gives me an error screen and tells me that SharePoint foundation has stopped working.

 

Wednesday, August 14, 2013 8:46:49 AM

re: Nintex Workflow - Querying a List with Paging

Hi Juli,

download the .uda file, go to Manage User Defined Actions and import it there.  Then try to open the UDA and see if that works.

cheers,

Vadim

Sunday, November 16, 2014 8:09:31 PM
Reddy
Gravatar

re: Nintex Workflow - Querying a List with Paging

Hi Vadim,

Each time I call the UDA, i get the same ListItemCollectionPositionNext value  "Paged=TRUE&amp;p_ID=129" from the web service response. Looks like parameter "paging" taking blank value even though i pass "Paged=TRUE&amp;p_ID=129"  this value.

Please help me.

Thanks,

Reddy

Wednesday, November 19, 2014 8:39:47 AM

re: Nintex Workflow - Querying a List with Paging

HI Reddy,

Sorry for the delay.  Have you figured out what was causing this?

I'm afraid I don't know what is causing that error.  I've never seen it before.

cheers,

Vadim

You must sign in to this site to post comments.
Already Registered?
Sign In
Not Yet Registered?
Register





 

Statistics

  • Entries (246)
  • Comments (1725)

Categories