Filter expression in data row

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.

image

Many thnx and best regards,
Robert

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

@supermanPunch:

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

thank you!
Robert

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

from the CollectedDataDT

@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

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

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

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

Thx. Robert

@BOTFORCE

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

1 Like

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

1 Like

@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
    dtarray
    dtarray1

  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:


1 Like