Filter Data Table w/o Filter Data Table

I’m preparing for the certification practical, and wanted to try some practices before I start. I’m using the Yearly Report exercise as a starting point, and modifying it some to get comfortable doing things outside the walk-through but I’m stuck on one thing so far:

I’m scraping the Work Items table into a data table, one page at a time just like the Dispatcher in the Yearly Report exercise. I want to filter the data table to only rows that meet a certain criteria, and then add the results to the end of an xlsx file. I don’t want to use the “Filter Data Table” activity - I am trying to put the code together myself to get more proficient.

First I tried just tweaking the existing solution, doing a For Each Row activity to loop through the datatable, and then an if block to test if the current row meets my filter criteria. I get stuck here because i’m not sure what to do with the current row. I tried adding a Remove Data Row activity to the Else block to remove the current row if it doesn’t match my fliter criteria - but apparently you can’t remove a row while you’re in an if block.
image

I tried using an Assign activity to assign the current data table to a new data table with a filter applied - and this basically worked but it threw an error in the log anytime the filter resulted in no rows being added to the new data table, and I think that’s just bad practice:
dt_Filtered = dt_WorkItems.Select(…criteria…).CopyToDataTable

I’ve tried several other things that I can’t quite remember. I feel like it should be relatively simple to do without having to leverage the built in Filter Data Table activity but can’t quite get there… probably been staring at it too long (and not sleeping enough).

Thanks for any pointers!

HI @pumrum

Based on my understanding, What you need to do is, if the current record meets the critiria you are looking for, you should be able to separate it from the rest right?

So… We can do something like this…

In the For Each Row activity, keep the IF activity just like you have it now…

In the IF activity, if the record meets the criteria, you can use a Invoke Method activity to add the particular row to another data table which you can use later to write to the excel file.

This is one example which I have done sometime back…

So we have two datatable variables.

  1. the one you are processing to filter data
  2. The one you use to hold the filtered data to be written to the excel

So, just before the For Each Row activity add a assign activity to clone the structure of the data table 1 to datatable 2.
THen use the For Each Row activity to process it…
Check the screenshots below…

image

I think this suits your requirement… Let know if that helps!!

2 Likes

That worked great, thanks. Now I have to read up on when to use Invoke Method. I still can’t imagine why you can’t just say “remove row” when looping through a data table, but I’ll just accept that as a limitation of .net.

One thing not explicitly called out in your post, is that you need to tell the Invoke Method about the arguments (this might be obvious for most, but in case not):

2 Likes

maybe once you have completed using the above method. you would like to try it out in linq instead since you said you would like to get more proficient.

1 Like

Removing a row while looping through the same datatable is not possible as I know. Imagine, you are looping a datatable and you provide a remove row command… That can actually cause errors because it is changing the row count in the middle of the iteration… I don’t think that’s possible.

By the way, you can do some reading on methods that can be used in Invoke Method…

https://www.tutorialsteacher.com/csharp/csharp-file

Once you do this, you can try out the same thing using LINQ as @garywjd91 mentioned…

https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/concepts/linq/

1 Like

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