How to use For each to pass variable to Filter wizard of "Filter data table" activity

Hi Team, I have a use case to filter names from a datatable and keep the rows matching the filter key. I can able to achieve it by hard coding all the names in Filter wizard(Please refer screenshot). FilterDT

But I’d need to get inputs dynamically from Excel.
I tried to achieve it by using Read column → For each and pass each name into Filter wizard using variable, but ran into exception “For each: Specified cast is not valid”

Can someone please help me to get this resolved?

Hi @AswinSridhar

Please use the select method as below!

Dt.Select("[Name] ='"+Currentrow(Rowindex/"Columnname").ToString+"'").CopyToDatatable

Regards

Hi @pravin_calvin, thank you for the immediate response!

I tried implementing the Select method, but getting the below error

my Data table name is “DT” and Column name is “Name”

Hi @AswinSridhar

You need the Name John,Andrew, Dacia as separate Dt right

Try the expression below!

YourReadDt.Select("[Name] =’"+currentrow(index or Columname).ToString+"’).CopyToDataTable

Note : use index eg : currentrow(1) or columnname with doubleQuotes eg :currentrow(“Name”)

Or else Try with the filter Data table with creating newDt in Output and in filtercondition
Use Currentrow(1).toString

Try with the above and Let me Know!

Regards

depending on the numbers of name, we maybe can adopt.

ReadIn Excel with all names in Column:Name into a datatable with read range: dtNames

assign Activity:
left: arrNames | String()
right:

dtNames.AsEnumerable.Select(Function (x) x(“Name”).toString.Trim).ToArray

For checking all names for filter do following:

Assign Activity:
left: dtFiltered
right:

(From d in DT.AsEnumerable
Where arrNames.Contains(d(“Name”).toString)
Select r=d).CopyToDataTable

Hi @pravin_calvin I’m facing this error. Should i create a variable?
NotDefined

Hi @ppr sir, I’ve added the names from excel to a new data table “dtNames” and created a new variable arrNames of type String. When tried to us the above expression, getting the following exception

Hi @AswinSridhar

Please refer to the xaml below as i created Filter based on select method and it will create file based on the filtered name!

Sample_Filter.xaml (10.0 KB)

Hope this what you re looking for!

If you no need to loop means try with @ppr post!

Regards

1 Like

I updated the statement. Plase as the AsEnumerable part

assume you have 2 excels, one is the main excel , one the the excel containing the names to filter by

this is how you can filter dynamically instead of hardcoding a filter datatable activity

1 read “test.xlsx” to dtMain variable

  1. read “NamesToFilter.xlsx” to dtNamesToFilter datatable variable

  2. initialize a “filterList” and filterString variable

  3. loop over dtNamesToFilter, in each iteraction add "[Name]='"+CurrentRow("Name").ToString+"'" to filterList


    3a. now filteredList looks like this
    image

  4. assign String.Join(" OR ", filterList) to filterString

  5. this should be the value of filterString
    [Name]='John' OR [Name]='Andrew' OR [Name]='Dacia'

  6. now simply assign dtMain.Select(filterString).CopyToDataTable to a new datatable variable: dtFiltered

  7. dtFilter will look like this
    image

Test.xaml (7.9 KB)
NamesToFilter.xlsx (8.3 KB)
Test.xlsx (8.4 KB)

1 Like

Thank you so much! @pravin_calvin this worked!!

Woww!! What a explanation @jack.chan. Thank you so much for a detailed explanation and the solution. Exactly what I was looking for!

1 Like

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