I want to update multiple rows in a data table based on the condition applied on columns

I want to update a data table based on certain conditions
I have a scenario where I want to put multiple conditions on different columns in where condition - and update multiple rows .I want to use invoke code activity with C#. Since ,I am a beginner I am trying the below but not able to get the desired output

DT.AsEnumerable().Where(r => r(“total amount”) <= Threshold AND r(“Status”)IS NULL)
.ToList()
.ForEach(r => {
r[“Status”] = “Completed”;
r[“Remarks”] = “XYZ”;
});

Hi @Yoichi ,

Can you please help.

Hi,

Can you try the following expression?

dt.AsEnumerable().Where(r => r.Field<double>("total amount") <= threshold && string.IsNullOrEmpty(r["Status"].ToString()))
.ToList()
.ForEach(r => {
r["Status"] = "Completed";
r["Remarks"] = "XYZ";
});

It’s assumed type of “total amount” column is double.

Sample20211013-2CSv5.zip (3.0 KB)

Regards,

Why?

This is what we call a black box - something that is difficult for others to interpret, making it more difficult in the future for your automation to be maintained and updated.

Why not just do it with activities? For Each Row on the dt, and update the values with Assign inside an If.

Hi @Yoichi ,

I am getting this error while executing the .xaml file , “Invoke code: Exception has been thrown by the target of an invocation” , there is no syntax error . Please help.

we can filter with LINQ and updating within a for each

so only the relevant rows will be edited

Hi,

Can you share $exceptionDetails in LocalsPanel when run in debug mode?

Regards,

Hi @Yoichi ,

I am taking the Threshold value from a config file so it is string but I am converting it to double in the code ,
The total amount field is also a string as I am reading it from excel and putting to a data table . I tried converting to double in the invoke code as below :

DT.AsEnumerable().Where( r => Convert.ToDouble(r.Field(“Amount”)) <= Convert.ToDouble(Threshold_Limit) && string.IsNullOrEmpty(r[“Status”].ToString()))
.ToList()
.ForEach(r => {
r[“Status”] = “Not Required”;
r[“Remarks”] = "Incurred Amount is less ";
r[“Processed Time”] = DateAndTime.Now;
});

But we are getting an exception as below:

RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> RemoteException wrapping System.InvalidCastException: Unable to cast object of type ‘System.Decimal’ to type ‘System.String’.
at System.Data.DataRowExtensions.UnboxT1.ReferenceField(Object value) at System.Data.DataRowExtensions.Field[T](DataRow row, String columnName) at UiPath.CodeRunner.UiPathCodeRunner_ccacd577fbb64faa885c0edcb2792e5e.<>c__DisplayClass3.<Run>b__1(DataRow r) at System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at UiPath.CodeRunner.UiPathCodeRunner_ccacd577fbb64faa885c0edcb2792e5e.Run(DataTable Reported_Data_iVOS_DT, String Threshold_Limit) --- End of inner exception stack trace --- at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at UiPath.Activities.System.Utilities.InvokeCode.CompilerRunner.Run(Object[] args) at UiPath.Activities.System.Utilities.InvokeCode.NetCodeInvoker.Run(String userCode, List1 inArgs,
IEnumerable`1 imps,
Object args)
at UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance,
ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor,
BookmarkManager bookmarkManager,
Location resultLocation)

Hi,

Can you try to add .ToString() inside ConverttoDouble like

Convert.ToDouble(r.Field("Amount").ToString())
Convert.ToDouble(Threshold_Limit.ToString()) 

For now can you add to both expression, although the error occurs in either one.

Regards,

Hi @Yoichi ,

The Amount column was saved as type decimal in the datatable explicitly , so we tried the below query and it worked :

DT.AsEnumerable().Where( r => r.Field(" Amount ") <= Convert.ToDecimal(Threshold_Limit) && string.IsNullOrEmpty(r[“Status”].ToString()))
.ToList()
.ForEach(r => {
r[“Status”] = “Not Required”;
r[“Remarks”] = " Amount is less ";
r[“Processed Time”] = DateAndTime.Now;
});

Thank you very much for your help ! :slight_smile:

2 Likes

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