Lookup field - multiple entries

  • 22 November 2021
  • 1 reply
  • 269 views

Badge

Hi,

I am struggling with a lookup field and multiple entries using collection operation - hope someone can support me.

At a sharepoint list there is a lookup field to select one or more group names. The related list contains group names and members. I use "collection operation" to get all group names for current item. Stored them as "Lookup Values, Comma Delimited" to have only names in this collection (at this time there is already a semicolon at the end). Example: "name1,name2;"

 

Afterwards I am using regular expression to split entries by "," and getting "name1;name2;;"

 

Problem seems to be that "query list" during a "For each" does not like ";;" so my result is just members of group name "name1".

 

Question: I guess removing the semicolon at the end would be the solution? Are there other ways to solve this problem? If I use string in collection then I get "14;#name1;#15;#name2;" back.

 

Thanks for your support in advance


1 reply

Badge +7

So this is a bit messy but I got it to work for me. This approach modifies the CAML query under the hood of the Query List action.


I have a list Athletes with a lookup column to Sports:



 


I've built a collection variable (collChoices) that contains Football and Basketball. If I print the collection variable it looks like Football;Basketball (similar to what you have).


I perform a Join in a Collection Action like this:



The Delimiter is </Value><Value Type="Lookup"> and it gets saved to a multi-line variable, strQueryFragment.


I then use a Build String action to build the remainder of the filter and save it to another multi-line variable strQuery:



Not sure how clear these images are. That text is:


<In>
<FieldRef Name="Sport" />
<Values>
<Value Type="Lookup">{WorkflowVariable:strQueryFragment}</Value>
</Values>
</In>

strQuery after this looks like:


<In>
<FieldRef Name="Sport" />
<Values>
<Value Type="Lookup">Football</Value><Value Type="Lookup">Basketball</Value>
</Values>
</In>

I then build a Query List action specifying the fields I want and adding some simple filter criteria (doesn't matter what—it's going to be replaced):



Then switch to the CAML Editor view. I want to modify this Where tag to use my filter:



I've replaced the Eq tag with my variable, strQuery:



And that's it. The thing is working for me—returning all the records that have either Football or Basketball in my list. 


 


Hope this helps.


Reply