Check if specific value form dt1 exist in dt2

Help Studio
, ,
1

Hello,

I have two DataTables:
dt1
dt2

In both of them is a column “Order Number”
I would like to take Each “Order Number” from dt1 and check if it exist in dt2

If exist-> take next “Order Numer”
If not exist → write comment in dt1 (Column “Comment’)” This order does not exist in dt2

How to do it?

2

Hello, @aleksandra.plichta5

You can use For Each Row in Datatable in Dt1 and put into if condition to search Order Number in dt 2 and use this expression:

dt2.asEnumerable.Where(Function (x) x.item(“Order Number”).ToString.ToUpper.Trim.Equals(curretRow(“Order Number”).ToString.ToUpper.Trim)).Count = 0

Basically, get Order Number and lookup in dt2. If Order Number not found in any row in dt2, Count return = 0.

Then in True branch, put assign activity to currentRow.item(“Comment”) = “This order does not exist in dt2”

3

Hi,

FYI, another approach: using LINQ with Invoke code.

image
image483×617 16.3 KB 

dt1.AsEnumerable.Where(Function(r) dt2.AsEnumerable.All(Function(r2) r2("Order Number").ToString<>r("Order Number").ToString)).ToList().ForEach(
Sub(r)
    r("Comment")="This order does not exist in dt2"
End Sub
)

Sample20230126-1aL.zip (2.9 KB)

Regards,

4

Let us mention this recommendation as well:

grafik
grafik739×108 3.47 KB

So we rewrite to any with predicate
…Any(Function (x) …)
Or
…Where(Function (x)…).Any()

In case of another optimization is needed also have a look here:

we took benefits from lists/dictionary keys

1 Like
5

Very thanks for advice.

I took notice and I will sharing in another oportunities