DataTables - update a row based on another datatable

hi all - hoping that someone can help me with something

i have a work flow that has two data tables. if a field “FileCode” is in both data tables i need it it to updated the the processed flag in the other to Y

so one big data table DT1

one small data table DT2

As file code 5555 is in both i would need the processed flag in DT2 to be set to Y

not certain how to do this - a join will not work as the second row in DT2 will not be picked up

any help greatly apprecaited


full xaml also attached

Main.xaml (13.3 KB)


can you try this in invoke code activity

For Each r As datarow In dt2.AsEnumerable
If dt1.AsEnumerable.Any(Function(a) (a("FileCode").ToString).trim.equals(r("FileCode").ToString.Trim)) Then
End If


that is great that worked for the solution j have here - always amazed that i can spend days trying to solve something. put the issue here and it is solved in minutes. now to try it on the bigger \ real problem. will update you again shortly

1 Like


If you want to maybe use a LINQ statement with the Join statement, you can give it a try with the following:

Dim query =
            From row1 In dt1.AsEnumerable()
            Join row2 In dt2.AsEnumerable() On row1.Field(Of String)("FileCode") Equals row2.Field(Of String)("FileCode")
            Select row2

        ' Execute the query to get the matching rows and update the "Processed" column
For Each matchedRow In query
    matchedRow.SetField("Processed", True)

Use it inside an invoke code passing the datatables as IO arguments.


Could be handled by a full join

lets exclude more details on trimming, harmonizations when e.g. working with the Join DataTable

that worked great on the larger version as well

thanks again

1 Like

Hi @adrian_sullivan

You can use the LinQ Expression to update the processed column with Y, If condition got validated.
→ Use Read range workbook activity to read the Excel1 and store in a datatable variable called dt1.
→ Use another Read range workbook activity to read the Excel2 and store in a datatable variable called dt2.
→ After read range use the for each row in datatable activity to iterate the each row in dt1.
→ Inside for each insert the assign activity and give the expression as below,

CurrentRow("Processed") = If(dt1.AsEnumerable().Any(Function(dr) dr("FileCode").ToString = CurrentRow("FileCode").ToString), "Y", CurrentRow("Processed").ToString)

→ Outside foreach insert the write range workbook activity to write the dt1 datatable to excel.

Check the below workflow for better understanding,

Workflow -
2024.xaml (15.8 KB)

Output -

Hope it helps!!

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