Filter Datatable Column With Dynamic Values

Good morning,

I currently have a datatable that contains a column called Customer Tax ID and Customer Name:

Customer Tax ID Customer Name

123 John
456 Smith
78911 Jill
8796 George

Now I have a comma separated string that contains dynamic Customer Tax IDs, meaning that this comma separated string could have different values across different periods of times. For example, it could be “123,456” or “546,245” or “123,8796,456”.

I am looking through a way to filter the datatable based on the comma separated values that are in the Customer Tax ID string variable.

What I have tried so far is the following:

1- I grabbed the comma separated string variable and converted it to an array of strings
2- I then converted the array of strings to a list of strings
3- I then used the below formula to try and filter the datatable based on all of the values that are in the initial comma separated string

(from r in DT.AsEnumerable() Where LIST_TAXID.Contains(r.Field(Of String)(“Customer Tax ID”)) Select r).CopyToDataTable → I can’t seem to get this to work as its returning NULL values.

In addition to this, I tried to use the .SELECT() on the datatable and that partially worked with hard coded values but I could not get it to work with a variable (because I am drawing the comma separated string from an asset).

If I hard code the values as such, it works → DT.Select("[Customer Tax ID] IN (123,456)").CopyToDataTable()

but if I put a comma separated variable instead of what is inside the IN operator then it fails → DT.Select("[Customer Tax ID] IN (’"+COMMA_SEPARATED_VARIABLE+"’)").CopyToDataTable()

Any suggestions and help would be appreciated. Thank you.

Use IN, not Contains.

1 Like

Hi Postwick,

Thank you for your reply. I am not sure that I am following you. If you notice in the second method of where I use the .SELECT() on a datatable, I am using an IN operator but it doesn’t want to work on a comma separated string variable, it only wants to work on hard coded values.

That’s because you’re giving it “value1,value2,value3” instead of “value1”,“value2”,“value3”

1 Like

So I tried putting single and double quotes and that still does not work.

Actually , I think I just figured it out after further experimentation.

So DT.Select("[Customer Tax ID] IN (’"+COMMA_SEPARATED_VARIABLE+"’)").CopyToDataTable() was not working because I had 2 single quotes before and after the comma_separated_variable.

So instead of doing ’"+COMMA_SEPARATED_VARIABLE+"’ , it needs to be “+COMMA_SEPARATED_VARIABLE+” , and now I can feed a comma separated string into that variable and the .SELECT() method with the IN operator works.

1 Like

Excellent. Lots of trial and error with RPA. Thanks for coming back and posting what you figured out - it’ll help someone else in the future.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.