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

Help Community
,
1

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?

2

Hi @AswinSridhar

Please use the select method as below!

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

Regards

3

Hi @pravin_calvin, thank you for the immediate response!

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

currentRow
currentRow920×257 13.8 KB

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

4

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

5

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

6

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

7

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

Getting Exception
Getting Exception853×255 13.1 KB

8

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
9

I updated the statement. Plase as the AsEnumerable part

10

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

image
image747×353 8.35 KB

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

    image
    image942×90 4.41 KB

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

    image
    image817×217 10.2 KB

    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
11

Thank you so much! @pravin_calvin this worked!!

12

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
13

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