How to update the data table column value by identifying a specific with the condition

Help Studio
, ,
1

Hi There,

I have a master data table. I am filtering the data with unique column value and now i have a filtered DT.

I am looping through the filtered DT and performing some activities on the values. Based on the outcome i need to update the column value of that particular row.

How to ensure that the value is updated in the master data table as well.

Thanks in Advance.

2

Hello @ramya_anbazhagan ,
Can you send the dummy data of how the input and filtered data looks like, so that I can be more clear on your query.
Regards;)

3

Hi Akshay,

Thanks for the reply.

Master data table,

image

I am filtering the master data table with unquie transaction code, for example my filtered DT is as below,

image

Now i am looping through this filtered DT,
If the transaction amount is greater than 10000, i am updating the Transaction status as “Valid” or updating it as “invalid”

The current row which i am looping is inside the filtered DT, but how can i ensure the same value is getting updated in the Master DT as well?

Thanks in advance.

1 Like
4

@ramya_anbazhagan,

You can update this directly into master datatable using LINQ query something like below in Invoke Method

Dt.AsEnumerable.Where(function(x) CDbl(x("Transaction Amount").ToString) >=10000).ToList.ForEach(sub(r) r("Transaction Status") = "Valid")

Dt.AsEnumerable.Where(function(x) CDbl(x("Transaction Amount").ToString) < 10000).ToList.ForEach(sub(r) r("Transaction Status") = "Invalid")

image

Sample Code:
Workflow.xaml (6.6 KB)

Make changes as per your requirements.

Thanks,
Ashok :slight_smile:

5

Hi Ashok,

Thank you so much for your reply!

I understand your query. But the example i have provided is just for the understanding.

My business case is like, i will get some of the output values only when i loop through the filtered data table.

Example, i will login to the SAP system and will download the files related to that transaction, and i need to get the count of the no of files downloaded and then update it to the master DT.
It is not as straight forward as the above. Please find the samples updated below,

Master data table

image

Filtered DT

image

Once after i loop through each row and download the relavant files, i will have a the count of number of files downloaded for that particular row,

image

In this case, how can i update the values in the same rows of master DT.

Thanks in advance!

1 Like
6

Hello @ramya_anbazhagan ,
So for instance, in the master tables Transaction status column of other rows than AMK, will be left blank since it is not there in filtered DT, Am I right?
Regards;)

8

Hello @ramya_anbazhagan ,
I have attached the Code According to your Requirement:

(from row in dt_Input
let x=if(dt_Filtered.AsEnumerable.Any(Function(x) x("Transaction Date").ToString = row("Transaction Date").toString AndAlso x("Transaction Code").ToString = row("Transaction Code").toString),if(CInt(row("Transaction Amount"))>10000,"Valid","Invalid"),"")
Select dt_Input.Clone.Rows.Add(row.itemarray.take(3).Concat({x}).toarray)).copytodatatable

On the left hand side use your Master DataTable Variable, so that it can assign the values to the Master Datatable.
The output will look something like this:
image
I hope this Helps you out. Please mark me as a solution as this can help me too.
Regards
Akshay B;)

9

Hi Akshay,

The flow will be like

  1. I need to get the unique transaction codes from the master table.
  2. Filter the master table by the transaction codes one by one.
  3. Loop the filtered data table and should perform the download by looping the datarows.
  4. Update the outcome back on the master data table.

Thanks for your help! :slight_smile:

1 Like
10

Hello @ramya_anbazhagan ,
For the Step 1 you can do the GroupBy method in Linq based on the Columns according to which it should be unique:
If you could give me the Demo data according to which column it should be unique, I can help you with the step1
Regards:)

11

Your DataTableName.Select.Where(function(x) x.Item(“column name”).ToString.Contains(“value to be matches”).ToArray(0).Item(" paste column name which has to be updated")) = “put value over here which to be updated in that column”

image
image1045Ă—477 15.2 KB

image
image1072Ă—345 11.4 KB

12

have a look here:
[HowTo] Overview on different options for grouping data and processing the groups - News / Tutorials - UiPath Community Forum

later it will discuss the updatings on a subset of rows

we have the options:

  • LINQ Where filtering and returning the result as a collection (list/array of DataRows)
  • DefaultView approach as described

As long we do not use Statements/Activities that are changing the DataTable identity - DataRow link we can filter and update directly on the Master

13

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