Added information in the return line of the lookup datatable

[Luan]

Hi! Guys, do you have a flow where you can use the data lookup activity to store information in the row where the lookup found it?

Example; in the datatable search flow I put the name and return age, then I put an ok in the next column referring to the search found.

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Hi Luan,

Please follow the below steps to achieve your requirement.

1. Use workbook read range to read your input names excel and store the data into name Input DT.

2. Use one more workbook read range to read the values from your age excel which is having the names and age. store these values into Age DT.

3. Now Use For each row in datatable activity and input your Nameinput DT.

4. Inside For each row loop get the NameInput with the following expression CurrentRow(“NameInput”).ToString

5. Now the important part use the lookup data table activity and your target column name is your age column and lookup column should be Name and lookup value is your name input. the cell value is the output for this activity.

6. We have to use if condition and check the following expression CellValue<>“” which means that you got some age value for your input name

if → Condition satisfies → use assign activity → CurrentRow(“SearchFound”)= “OK”

Else part → use assign activity → CurrentRow(“SearchFound”)= “NOT OK”

7. After for each row loop ends at the ending use workbook write range and write the updated Name Input DT to the same name input excel so that you will get all the values with Ok and Not OK.

Please try the above steps and let us know. and also i am attaching the sample work flow. please use it for your reference. thanks.
DataLookupDemo.zip (15.2 KB)

[ignasi.peiris]

Hi @Luan !

You can make use of the function:
DataTable.Rows(RowIndex)(“ColumnName”) to do what your goal is

Lookup Data Table will search for the value stored in var_Name, on the column “Name”, and will return its index , meaning in my example, that John was found at row 0:

After that, with the expression explained above, you can force the value on that row, but on the 3rd column to be "OK:

Assign:
var_DT.rows(var_Index)("Status") = OK

Where var_Index is the output from the Lookup DataTable activity.

Therefore, the datatable, where your user was found, will have an “OK” assigned to it!

• You can place this code inside a loop, and keep on changing the var_Name value to find different names if needed!

DemoLookup.zip (56.1 KB)

Hope it helps!

[Luan]

Hello, I’m not able to use the DataTable.Rows(RowIndex)(“ColumnName”) = “ok” function to register in excel, I want to use it in the Write Cell activity but in its format it is not declared.

[ignasi.peiris]

Good afternoon @Luan !

Got your point there

You have then 2 options:

• Option 1: Work with the datatable, as my previous example, and then at the end when everything is filled, just use a Write range to type it back to excel

Or

• Option 2: I did modify the workflow a bit to work with excel directly, also did add a dynamic selection of Columns in case your “Status” column is not always the 3rd one (or column “C”) for example.

Option 2 explanation:

1- Changed to excel input

2- Added an assign to find the position of the Column “Status”, in this example we want a “3” since it’s the 3rd column.

Array.IndexOf(var_DT.Columns.Cast(Of DataColumn).Select(Function(x) x.ColumnName).ToArray,“Status”)+1

With this we need to convert “3” to “C”, so that we can tell the bot to write for example, on Cell “C2”

To do that, I re-used a code from Bianca , that you can find on this post: Convert integer to Excel column - #10 by bianca

With that, the only left to do is to place a write cell activity, and combine the var_index that we had before (+2, since we now have headers row + 0-index based) with the column letter:

With this, if we search for Marc as an example, it will return “C3”, and it will write an “OK” on that cell:

Code updated:
DemoLookup.zip (64.7 KB)

Hope it helps now
Best Regards!

[Luan]

Hi, @ignasi.peiris function var_DT.rows(var_Index)(“Status”) = “ok” worked!
sorry for the confusion, for my lack of knowledge, I didn’t know it was possible to use var_DT.rows(var_Index)( “Status”) inside an assign without having to declare, now I can use var_DT.rows( function var_Index )(" Status" ) = OK to write in excel, I have another question, in this function var_DT.rows( Status") = “OK” can I put more than one result of “ok” “nok” in this function?

[ignasi.peiris]

Hi @Luan, great to hear that!!

Yes, the “=” on the expression would be either side of the assign

You have multiple options, lets say that you want to put “OK” if some logic has happened, and “NOK” if it did not happen.

• Easier solution would be to have 2x assigns, one on each part of an If (“Then” and “Else”)

Meaning: - If this happened write “OK”, Else write “NOK”.

2nd option (clean, but might be harder to understand) is to include the “IF” condition inside the Assign:

Assign would change to the following:

• Yellow = if condition is True
• Green = if condition is False

The assign itself would decide which one to type based on the Condition variable (boolean)

hope this is what you need!

Best Regards,
Ignasi Peiris

[Luan]

@ignasi.peiris I found another solution with if, working on top of Rowindex, whenever value is false it returns -1 to not block the var_DT.rows(var_Index)(“Status”) function.

Thank you very much!