Query SharePoint List using a Person field/ checked out user

Here is what I am trying to accomplish

1.) Query all the list items that are checked out.

2.) Get the checked out user names and store in a collection variable

3.) Email to that user with the name of the items checked out to this user only. One email is going to contain all item names that is checked out to one user only. There might be multiple items checked out by the same user multiple times.

Well, it appears to be quite simple and straight forward given the Nintex actions we have, but despite that it took me some real good time to achieve this. So, I’m going to share my pain area in this task and the solution.

Assuming the ‘Shared Documents’ library has documents checked out to users.
Step 1 – Query List with filter condition “Checked Out To is not null” and save the field ‘Checked Out To’ in a collection variable ‘collCheckedOutTo’
Person2

Step 2 – The outcome of query list returns a collection of users with duplicate values since each user may have checked out multiple files. So, we need to remove duplicates using Collection Operation
Person3
Save the outcome in a collection variable ‘collCheckedOutToUniqueUsers’

Step 3 – We extract individual users from the unique collection using For Each and save the username in a workflow variable ‘userCheckedOut’ of type ‘Person or Group’
Person4

Step 4 – The value from for each block is in the format of domain\username, hence let us get the display name by setting the variable.
Person5

Step 6 – Now that the we have collection of unique users and we are in the for each user block with the display name, we query the ‘Shared Documents’ library once again and get the ‘Name’ field in a collection variable ‘collFileNames’
Person6

This should return the file names and now I shall send out the email to my user.

But hold on, what is this? The query is not returning anything.

In fact, even though if I try querying using the ‘Created By‘ or ‘Modified By‘ instead of ‘Checked Out To‘, the query is not returning any value. So the problem is that my query fails if I try to filter using a field of type ‘Person’.

That is because we want to filter the list using the user name or display name.
Now if you go to the CAML editor instead of Query builder, we can see the below query

 <Query>
 <Lists>
 <List ID="{3F6D13BE-4A48-4871-9EF1-FA07B28C77DC}" />
 </Lists>
 <ViewFields>
 <FieldRef Name="LinkFilename" />
 </ViewFields>
 <Where>
 <Eq>
 <FieldRef Name="CheckoutUser" LookupId="TRUE" />
 <Value Type="User">{WorkflowVariable:userCheckedOut}</Value>
 </Eq>
 </Where>
 </Query>
 

The mistake is we are trying to query using a SharePoint User Id instead of only name (you can see in the query LookupId=”True”).
Remove LookupId=”True” and execute the query. It will work now and return the file name. We can send out the notification to the user with this collection of file name.

Mission accomplished 🙂

Advertisements

7 thoughts on “Query SharePoint List using a Person field/ checked out user

  1. Hi, good article. I resolved the filtering issue in Query List by setting Initiator as variable with Type set to Person/Group and Return type to User ID Number.

  2. hi, Good article.
    I am facing a similar problem. Any help/leads on this front would be appreciated.

    Problem: While copying an item from one list to another within the same site, for the ‘person or group’ field only the first employee name is copied. It ignores the remaining employee names.
    Using: Nintex Workflow 2010, Action: Create New Item.
    Note: Copy item is not available in my farm.
    I tried two ways, but the result is same: copies the first name, ignores remaining names.
    Source List Field to Target List Field
    List Field to Workflow Variable to Target List Field

    • Hi Alankar,

      The only thing that can lead to the result you’re getting is to set the person or group field in your target list to “not allow multiple selections”.

      Ensure that you have allowed multiple selection in the target list also. The copy item action as well as the create item, both work to copy all names.

      • Thanks Manisha. I had been struggling for past few weeks before you helped me. Please continue the excellent and selfless work of sharing your expertise with poor souls like us.

        Wishing you a very happy new year..

        Regards,
        Alankar.

  3. Hi Manisha, Thank you for this great how to article. I am trying to do something similar but am encountering an issue from the get go. Instead of querying a list for the ‘checked out’ column, I am querying the list for items that have not been modified in the past 7 days. I have a calculate date action set to ‘today’s date -7 days’, then my query to select only items when the modified date is less than or equal to the calculated date. But when I run the test in Nintex, there are no results from my query. Do you know if there’s something special I need to do when querying using the modified date column?

    Thank you!

  4. Nice Article! Not for the Nintex Faint-of-heart.

    I think I should be able to use this technique to take a Person field which could have many users. (Stuff it into a collection variable.) Then do a foreach on it using a Person Variable for each item and send individual emails or write a list item using that user.

    Or atleast, I’ll try.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s