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.