Filter Collection

[savanpatel]

I am having issue filtering a collection that has a column name with space. I get the following error: Internal : Could not execute code stage because exception thrown by code stage: Syntax error: Missing operand after 'Name' operator.   I have attached picture for reference.

[AmiBarrett]

Try enclosing First Name in single quotes as well.

[savanpatel]

Using single quotes does not work, it does get rid of the error message but it does not give the right output result.

[AmiBarrett]

Try making a new action with this as the code stage. I replaced that action with this one for ease of use long ago.

Inputs:
Collection - Collection
FilterString - Text
Column - Text

Outputs:
Sorted Collection - Text  

String filter = """";
if(FilterString!="""") 
{
	filter = ""`""+Column+""`"" + "" like '%""+FilterString+""%'"";
}
else
{
	filter = ""`""+Column+""`"" + "" = ''"";
}
DataView dv = Collection.DefaultView;
dv.RowFilter = filter;
Sorted_Collection = dv.ToTable();

[SukeshManthena]

Hello, Try using in this format in Filter Attribute in ""Utility - Collection Manipulation"" , as Coll1.First Name = ""White Shark"" and check it. Good Luck.

[sai_bhargavaraj]

Enclose that column name within square brackets.

[ChristianLobosc]

I had this problem many times until I found a simple and easy solution. When filtering with VB.net codes the column name is typically put in brackets. "[Column Name] LIKE 'text you want to filter'" The [ ] will allow you to use a space. Make sure Quotation marks are used before the [ as blue prism will think you are referring to a data item if you don't.  

[AmiBarrett]

Either [ ] or ` `. Basic SQL notation.

[PavelKrál]

​Hi all,

any Idea why ? wildcard is not working?

eg.: [Area] like '04? AB'

------------------------------
Pavel Král
RPA Specialist Senior
MANN+HUMMEL Service s.r.o
Europe/Prague
------------------------------

[DaveMorris]

I guess I haven't ever tried using a wildcard in the middle of a filter expression. You'd think I'd need that at some point. Go figure. 😃

Blue Prism is using the Select method on the datatable to filter the results (that is, if you go all the way into the code stage), and it looks like the Select method only supports wildcards at the beginning or end of the text such as '*this*'. I suppose the other issue here is that the Select method doesn't seem to support ? as a wildcard. I haven't looked into whether there's another single character to use instead, but at least when used in Blue Prism, the ? is read as part of the string.

You may want to use LINQ instead. It seems to be the go-to when performing queries on datatables (collections) in Blue Prism beyond what the existing actions provide.

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------