2 for each loop with if condition

Capture

I have 2 spreadsheet that I want to read. if my secondary spreadsheet contains new invoice number that is not in my masterlist spreadsheet. I wan to perform some actions based on the new invoice number.

how do I do it?

Hello @Popo

Let’s use join data table for this.

Read the secondary sheet to DT1 datatable
Read master sheet to DT2
Now use join datatable activity. In the activity for datatable one, use the secondary data table. And for the other, use the master.
The join type should be left join

The output of this will be another datatable that has columns of all two datatable.

The resulting datatable will have null values for the records that do not have a master record

So now, you can use a for each row activity to loop through the result
Within it, use a if condition to check whether the column coming from master DT is null or empty for that row. If it is null or empty, that’s the record you are looking for :slightly_smiling_face:

1 Like

my uipath is using an old version due to company policy and do not have join datatable activity. moreover, the column name is different as well.

can I use 2 for each loop to perform the task? if yes. need your advice

You may want to try this

You can use the horizontal merge activity of this component if your company policies allows it…

Additionally, yes you can use two loops for this. But it wouldn’t be efficient as the above two.

In case you want to use the for each use the below steps

Read the data into two datatable s
Have another boolean variable assigned to false by default

Use a for each row activity to loop through your secondary datatable first
Within the for each row loop, add another for each row loop to loop through the master.
Within that, have an if condition to check whether the row of the secondary datatable has a match with the row of the master. If there is a match, assign the variable to true, and add a break to break out of the inner loop.

Now add another if condition below the inner loop check for the condition for true of the variable, if not true perform the tasks. And assign it back to false before the outer loop starts again.

2 Likes

could u provide me an example of xmal file using for each loop?

At the moment I cannot share a workflow as I’m outside to get few things done. Will take a while to get back. but I found a similar scenario for you…

This has a screenshot of the same thing that I explained… little bit of tweaking to get the boolean check is what is required additionally

2 Likes

Hi @Popo

Please check out the workflow as attached.
Invoice.zip (20.3 KB)
If you have any queries please feel free to ask me.
If this was the correct solution, do mark it as Solution.

Thanks!

1 Like

hi sir,

my current version in my office lappy is unable to view. I will go back and use my own lappy to view the workflow you have done for me! :slight_smile:

hi sir,

you method seems to be working. however, it is only looping through the first row.

Can you share some screenshots of what you did? That’ll help to locate the problem :grinning:

1 Like

here is the screen shots


Hi @Popo,

I’ll suggest you to extract unmatched records seperately and have a datatable of those records, then using for each row on that datatable you can do further process.
For that create one more datatable variable DT3
Take Assign like this,

DT3 =

DT2.AsEnumerable().Where(Function(row) Not DT1.AsEnumerable().
Select(Function(n) n.Field(Of String)("Invoice Number")).Any(Function(x) x = row.Field(Of String)("Invoice No."))).CopyToDataTable()

—> by this you’ll get the datatable DT3 having new invoice no. which is not there in DT1.
So now, by using For Each Row on DT3 you can able to perform required actions.
:blush:

1 Like

hi sir,

will this code extract all the information for that row if the invoice is not in DT1?

yeah, its like filtered datatable :blush:. having unmatched dataRows. @Popo
make sure that, column names should be correct in this query.

hi sir,

any idea why it does not prompt me to type AsEnumerable after my DT2.?

@Popo
may be it’s because DataTableExtensions Namespace is not imported in your workflow—> System.Data.DataTableExtensions

is it showing error, after copy-pasting query?
I’ll suggest you to just copy-paste this query in your assign, and change dataTable variable names properly.

thanks sir. looks like I can work from the new DT.

1 Like

hi sir, if I have 2 columns (invoice number and invoice date) that I need to compare, how do I edit the code

1 Like

@Popo

Okay, If you want combination of invoice number and invoice date to be compare,
Then your query should be like this,

DT3 =

DT2.AsEnumerable().Where(Function(row) Not DT1.AsEnumerable().
Select(Function(n) n.Field(Of String)("Invoice Number")).Any(Function(x) x = row.Field(Of String)("Invoice No.")) OR Not DT1.AsEnumerable().
Select(Function(n) n.Field(Of String)("Invoice Date")).Any(Function(x) x = row.Field(Of String)("Invoice Date"))).CopyToDataTable()
1 Like

hi sir
the result is not what I needed.
for example, if DT1 and DT2 contains the same invoice number but have different invoice date, I need the code to extract that row as well.

only when invoice number and invoice date ties, then not required to extract

thanks much for your patience

1 Like