Lesson 9 - Filter Wizard not filtering data table as expected

Learn Academy Feedback
7

Okay last quick question in the output dataTable your input is DT1 right?

8

That’s correct and my output is DTstring which is the text I ask the message box to display.

9

Hi @rachelfonseca
I tried it myself and it’s working fine
Main.xaml (12.9 KB)

10

Hi,

That’s interesting the issue must lie in the excel application scope/read range then as you have used the build data table instead

These are the settings for read range:

image
image1294×504 47.8 KB

1 Like
11

Try to make an output data table and message box of the output of the read range to make sure that you have the dataTable filled with data if this step is successful then it’s the same thing as my workflow for the rest, also check that the Headers are there for DT.

12

Hi reda,

I’m facing the same issue as rachelfonseca encounted and I did more testing for filter data table acitivity and believe this may be a bug.

Here is what I tested:

  1. the filter function is working if you use any string column, like “name” column
  2. the filter function wouldn’t work nor throwing any errors if you use number column, like “age” or “pay”
  3. but sort data table activity working with either string or number column

your code is working that is because you build a table and explicitly specify the type of column is int32, so filter data table activity knows the column ‘pay’ is a number and can compare with another number, however when filter data table activity get data from a read range activity it may mistreat the column ‘pay’ as a string, so it cannot compare a string with a number (this is what i guess) and causes the filter function is not working.

2 Likes
13

Hi sgchris,

Thanks for your comments. Interesting that you can get the filter data table activity to work with string columns - I have just tried and it will not filter those either for me.

I also tried some tests on the sort data table activity but it did not sort nor did it error on any of the columns- like it cannot read my excel at all.

Hopefully we can get to the bottom of this.

Thanks

14

I am having the exact same problem with Practice 2 (there are 2 columns - “Name” (string), and “ID” (Int32, I presume)). Filtering the table by ID just won’t work - the tests always fail, so I end up with the unchanged table if I choose to discard the filtered rows, or with an empty table if I choose to keep them.

Here are the things I’ve tried:

  • Switching the PreserveFormat checkbox on/off
  • Filtering the table by the “name” field (string) - works fine
  • Sorting the table - works for both fields. However, the ID column is clearly treated as string (the order is 11, 2, 21, 38, 5, and so on).
  • Playing with the column selection mode as suggested above
  • Tweaking the tests (“(not) empty” works fine for the ID column; all integer comparison tests fail, string comparison tests, oddly enough, fail too; I was told that explicitly specifying the type with Convert.toDouble(value) would work, and it didn’t; neither did explicitly typing in the value with a decimal point).
  • Explicitly defining the data types with a “Generate Data Table” activity before reading the xls - did nothing.

At this point, I’ve run out of things to tweak. I’ve written the whole thing off as a bug and skipped the exercise.

2 Likes
15

Pretty certain I am having the same problem within Lesson 9

16

Hi all,

I raised this through the official support too and have had the following response:

The main problem was with the data format of columns of DataTable which we have to filter. In case you create a datatable using “Build DataTable” activity, you can chose column dataType as Int32 or String, hence, filtering was working flawlessly.
Hence, we have picked each cell element and converted to the required data format & saved it to another DataTable as shown in attachment. This has returned the required result. We recommend you to perform the same. Also, the excel sheet you shared had formula for changing “Age” column, so we have kept fixed values to check the result.

Please note, changing data format at excel will not help, as all values are taken by default as generic.

image
image460×720 15.9 KB

The support guys said they would get the vid/workflow updated too.

3 Likes
17

That’s… far from elegant.

3 Likes
18

Agreed! not the most flexible solution either, seems like the entire for each activity could be built into the read range

2 Likes
19

So sad, because the filter tool seemed so slick. But as long as they get the video updated that would be great, but I can’t say I would have figured that out.

1 Like
20

I have found it much simpler to invoke VBA and run code to provide any advanced filtering of the excel sheet, then use read range and select the filter option. Let me know if you need any code samples.

2 Likes
21

Hi @nealross

Would be great if you have some samples? We will need to filter/sort/mainpulate lots of excel files throughout our automations so would be good to have an easier way to achieve it.

Thanks

22

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

23
24

Late reply but I have just joined the RPA community.
This may help for any future user encountering the issue.
This problem has been resolved in Modern Design Experience.
You can change the data format using Extract Data Table wizard available in Modern design Experience. So filtering on integers is direct and no more workarounds needed.
Hope this helps.

1 Like
25

Would you provide examples please?

26

In modern design you have the activity - Extract Table Data.
If you click on the property for this activity You have in “INput” a property called Table Settings, which takes XML as input.
So in that u can change the type .
Eg - Converting INvoice Number to Number instead of Text

=====================
Attaching a sample xaml file for the same -
WFLoadAcmeData.xaml (37.2 KB)

hope it helps