Filter expression in data row

[BOTFORCE (Robert)]

Dear friends,

I have an urgent issue and hope you can help me out.

I’m struggeling with the creation of a value-expression which I need to remove data rows from a data table. In this value expression I want to select the rows from a data table collectedDataDT where the first column (Entry_ID) matching the same values from column (Entry_ID) of data table called joinEntIDDT. → like a lookup

What I am trying to do:

1:
I assigned “data_to_del” (as System.Data.DataRow) to a value expression which I do not know how to write.

2:
then I use “For Each” activity to loop trough the items of data_to_del. In the body of this “for each”-loop I will remove data rows from a data table. (see screenshot)

Question: How to write this filter expression in the assign activity.

Many thnx and best regards,
Robert

[supermanPunch (Arpan)]

@BOTFORCE Do you want to remove some rows from a Datatable which matches a Condition ?

[BOTFORCE (Robert)]

@supermanPunch:

yes, and the condition should be:
"where entry_id from data table collectedDataDT = entry_id from data table joinEntIDDT

thank you!
Robert

[supermanPunch (Arpan)]

@BOTFORCE You want to remove the rows from CollectedDataDT or the joinEntIDDT ?

[BOTFORCE (Robert)]

@supermanPunch

Collected

from the CollectedDataDT

[supermanPunch (Arpan)]

@BOTFORCE You can use this Linq Query to get the Non Matching Records :
Use the Below Expression in an Assign Statement in this way:

CollectedDataDT = CollectedDataDT.AsEnumerable().Where(function(row) Not joinEntIDDT.AsEnumerable().Select(function(x) x(“entry_id”).ToString).Any(function(y) y = row(“entry_id”).ToString)).CopyToDataTable()

You won’t be needing the Data Row Variable or the For Each to Remove the Rows

[BOTFORCE (Robert)]

@supermanPunch

CollectedDataDT = CollectedDataDT.AsEnumerable().Where(function(row) Not joinEntIDDT.AsEnumerable().Select(function(x) x(“entry_id”).ToString).Any(function(y) y = row(“entry_id”).ToString)).CopyToDataTable()

Thanks for helping, but I get the following error message:

[supermanPunch (Arpan)]

@BOTFORCE You might need to Check this Post for that Correction

Compile Error: "AsEnumerable" is not a member of "System.Data.DataTable"

@VegitlX_HuNteR Follow these Steps and Check : Open your Main.xaml File in Notepad or Notepad++ or any other Editing Tool. Then Add the Highlighted Reference as an Assembly Reference in the Assembly Reference Section as Shown in the Below Image. [Capture] Make sure to Close your xaml and then after the Edit open it again.

[BOTFORCE (Robert)]

@Compile Error: "AsEnumerable" is not a member of "System.Data.DataTable" Studio

Main.xaml File in Notep

Thx, I made the entry in my Main.xaml file, but got now an operator error:

Thx. Robert

[supermanPunch (Arpan)]

@BOTFORCE

@supermanPunch

CollectedDataDT =

Remove this and put the variable in the Left Hand Side in place of data_to_del

@supermanPunch

You won’t be needing the Data Row Variable or the For Each to Remove the Rows

[BOTFORCE (Robert)]

Oh man! How can I send you flowers? It really worked out!
Thank you so much!!

[supermanPunch (Arpan)]

@BOTFORCE Actually there is an additional Step involved in it, Which I believe I have not said. When the CollectedDataDT has no rows Corresponding to entry_id which is matching in Other DT , it gives out an Error.

There are mainly two ways of Handling that error:

1. You can use a Try Catch Method, Where you enclose the Assign Statement in Try and use another assign Statement in Catch Block to assign an Empty Datatable. You can perform thsi as shown in the Screenshots below
   
   

2. The Other method is to use an Array of DataRow variable. You can Declare an Array of DataRow variable, say dtRow_array. Instead of the Query above you can use this Query :

dtRow_Array = CollectedDataDT.AsEnumerable().Where(function(row) Not joinEntIDDT.AsEnumerable().Select(function(x) x(“entry_id”).ToString).Any(function(y) y = row(“entry_id”).ToString)).ToArray

Then Implement as Shown in the Below Screenshots: