Query and filter list if criteria is included in colletion output

  • 23 October 2017
  • 3 replies
  • 17 views

Userlevel 3
Badge +8

All,

On a SharePoint list you can filter a list by multiple of a particular field i.e. user names.

I can get the user names into a collection based on a certain criteria - Happy Days.

Yet I don't know how to query the list and filter the results if the output is included in the collection. 

There seems to be no 'contains' option when it comes to query/filtering a list.

I assume it would have to include a For Each in someway shape or form - yet I'm going around in circles.

Thanks

Andrew


3 replies

Badge +9

You can use 'Run if' action inside a 'For each' to filter the collection output.

210004_pastedImage_3.png

210003_pastedImage_2.png

210002_pastedImage_1.png

Userlevel 3
Badge +8

Does that mean then in each Run if action I have to manually input the value to check against? I was hoping to avoid that.

Userlevel 5
Badge +14

you do not need a loop. CAML support <In /> operator which you can feed with values to filter by.

see reference - In Element (Query) 

it's not directly clickable with query list's query builder, so you have to build the CAML query on your own.

first you need to convert collection of users into CAML's <Values /> elements.

this can be simply done with build string action

210016_pastedImage_2.png

for better reading the formula is

{TextStart}<Value Type='User'>{TextEnd}
fn-Replace({TextStart}{WorkflowVariable:Coll_Users}{TextEnd},
           {TextStart};{TextEnd},
           {TextStart}</Value><Value Type='User'>{TextEnd})
{TextStart}</Value>{TextEnd}‍‍‍‍‍

then this output can be simply used in Query list action

210026_pastedImage_6.png

<Query>
  <Lists>
    <List ID="{8BF2B7A1-A3BE-44D7-940C-FBF9EE867555}" />
  </Lists>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <Where>
     <In>
         <FieldRef Name='singlepp' />
         <Values>
                 {WorkflowVariable:ValuesList}
        </Values>
      </In>
   </Where>
</Query>






‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Note: this was tested with person field setting 'Show field' configured to 'Account' so users in a collection have to following that format and so have to be in format claim|domainloginname

Reply