Variable expression in return Nothing ("") from each row in datatable

[Irfan_Musa]

Hello UiPath Community,

I am facing a problem where I keep getting return “” from a variable even if the the data manage to be captured properly in the data table.

I use read range activity (excel) to save them in lets say DT9.
Then I would use “For each row” in DT9 with Item name = SCRRow. Inside, I assign variable as below:

SCR_EXIST = If(
(SCRRow(“Type”) IsNot Nothing AndAlso Not String.IsNullOrWhiteSpace(SCRRow(“Type”).ToString.Trim) AndAlso SCRRow(“Type”).ToString.Trim <> “N/A” AndAlso SCRRow(“Type”).ToString.Trim <> “Missing”) OrElse
(SCRRow(“Manufacturer”) IsNot Nothing AndAlso Not String.IsNullOrWhiteSpace(SCRRow(“Manufacturer”).ToString.Trim) AndAlso SCRRow(“Manufacturer”).ToString.Trim <> “N/A” AndAlso SCRRow(“Manufacturer”).ToString.Trim <> “Missing”),
“TRUE”,
“”
)

The purpose of this variable is to check if column “Type” or “Manufacturer” is not empty or “Missing” or “N/A”, if it has any value apart from the previous condition, it will return “TRUE” else it will return “”

when debug it shows it manage to capture DT9:

But when I check the variable value its showing “”

This is from the excel file, it has some empty cells below because my read range covers more than just the 2 rows having values.

Maybe my variable expression is wrong?

Appreciate any help and suggestion regarding this matter.

[Sanjay_Bhat]

Hi @Irfan_Musa

I just ran the code and your SCR_EXIST variable is working fine, since you are using for each row the the vaue of of SCR_EXIST will be true only for 2 rows and rest it will be null that is “”
so the value of SCR_EXIST will be “” after the loop has ran
Check the log for each row

It would be better if you say what is the overall workflow you need!

If this helps you do mark it as a solution
Thank you

[Irfan_Musa]

My final output for the variable are if the column “Type” or “Manufacturer” contains string value that are not Missing, N/A, empty spacess, null. The variable SCR_EXIST should be output as TRUE else ignore.

[V_Roboto_V]

Hi @Irfan_Musa

The issue is: You have empty duplicate rows in your DataTable. You need to first remove these empty rows.

Your Datatable:

Solution: Use the following assign to remove empty rows before your SCR_EXIST expression.

dt_Data = dt_Data.AsEnumerable().Where(Function(row) Not row.ItemArray.All(Function(field) IsDBNull(field) OrElse String.IsNullOrWhiteSpace(field.ToString()))).CopyToDataTable()

The Output:

Note: Since there were empty rows, the “Type” and “Manufacturer” are also “Null” or “Empty”. This is the reason your SCR_EXIST would return “” from these 3 rows.

If this solves your issue, Do mark it as a Solution.
Happy Automation

[Sanjay_Bhat]

@Irfan_Musa

Then use this assign statement outside the for each loop

SCR_EXIST = If(dt_input.AsEnumerable.Any(Function(x) 
   (x("Type") IsNot Nothing AndAlso Not String.IsNullOrWhiteSpace(x("Type").ToString.Trim) AndAlso x("Type").ToString.Trim <> "N/A" AndAlso x("Type").ToString.Trim <> "Missing") OrElse
   (x("Manufacturer") IsNot Nothing AndAlso Not String.IsNullOrWhiteSpace(x("Manufacturer").ToString.Trim) AndAlso x("Manufacturer").ToString.Trim <> "N/A" AndAlso x("Manufacturer").ToString.Trim <> "Missing")), 
   "True", 
   ""
)

Hope this helps

[Irfan_Musa]

@V_Roboto_V I think you explain it well, I did not know that empty duplicate rows would cause this issue. I thought when the variable I write in for each row would filter this, turns out the data table needs to be cleaned in advance.

Thank you so much, it works now, the for each is working as intended.