I’ve been looking for ways to filter multiple values in a datatable but I’m only led to one solution, the Select() method. For those who were sold on UIPath being a drag-and-drop-no-coding-required solution to automation, this could be intimidating. Heck, if I didn’t know about SQL I’d be dead beat right now. Yup! The only way (at least for now) to select multiple values from a column in a datatable is by using the DataTable.Select method.

Thankfully, it’s not that hard.

Related: Helpful DataTable methods and queries for better sorting, filtering, extracting

Say I have this datatable called rulesDT,

And I want to get all rows where Home Location is either London, Manila, or Tokyo

If only UIPath had a feature like this in its Filter DataTable activity, it would be easy right?

UIPath wishlist: Allow IN operation

But since there’s none at the moment, here’s what worked for me.

Using DataTable.Select()

var rows = new DataRow[]; 'Array of DataRow 
var outputDT = new DataTable; 
assign rows = rulesDT.Select("[Home Location] IN ('London','Manila','Tokyo')"); 

'Check if there are results 
if(rows.Count > 1){
    outputDT = rows.CopyToDataTable(); 
} else { 
    'Do something 
}

The pseudo code above, when applied as a UIPath workflow, will return a datatable with rows that satisfy the query, in this case 5 rows.

It’s similar to running a filter rule on MS Excel.

The reason we need to assign the results to a DataRow[] first instead of directly putting the output into DataTable is to avoid running into this exception in case there are 0 rows found. 

You cannot copy 0 rows to a DataTable, that’s why we need to check first if the Select query returned at least one result.

Example for querying multiple columns

I want to get Partners between the ages 30 to 40 years old

rulesDT.Select("[Occupation] = 'Partner' AND [Age] >= 30 AND Age <= 40")

There is no BETWEEN operator so we have to use two comparators.

Querying dynamic values

If you want to select multiple dynamic values from a column, you need to build the parameters first then include it as a variable.

•  Using a list of integer

var rows = new DataRow[]
var dynamicIntArr = {20,30,40}
var join_dynamicIntArr = String.Join(",",dynamicIntArr) 'Result: 20,30,40
rows = rulesDT.Select("[Age] IN (" + str_dynamicIntArr + ")")

•  Using a list of string

This requires more manipulation as you have to wrap each value inside single quotes. It would be best to prepare the string with single quotes beforehand for easier handling.

var rows = new DataRow[]
var dynamicStrArr = {"'One'","'Two'","'Three'"}
var join_dynamicStrArr = String.Join(",",dynamicStrArr) 'Result: 'One','Two','Three'
rulesDT.Select("[Age] IN (" + join_dynamicStrArr + ")")

•  Using a variable as value

I want to get all rows where Home Location equals ‘Tokyo’ but it’s stored in a variable

var loc = "Tokyo";
var rows = rulesDT.Select("[Home Location] = '" + loc + "'")

Just plug in the variable within the query, making sure to surround it with single quotes.

Very helpful links

• Where you can learn about DataTable.Select() .net method
  https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable.select?view=netframework-4.8
• Where you can learn about the filter expression syntax in C#
  https://www.csharp-examples.net/dataview-rowfilter