Write cells inside a For Each Row activity of a filtered datatable then unfiltered

Help Studio
#1

This is an extended question of 1st For Each Row in a data table

As you may know from the previous post, to perform the task, I filtered the original datatable (namely DT1) into a new filtered datatable (namely DT2). In DT2, I used For Each Row activity. The problems I have now are to 1) input the current date under Column D (i.e. Completion date) in each filtered row, and 2) finally add the completion date into the original Excel file which was output to DT1.

To explain more clearly, referring to the picture in the previous post, D13 should be input as 13/8/2020 (i.e. current date) after the action in the first row is completed (or we may say D2 instead of D13 if we’re referring to the new filtered datatable). And after actions in all rows are completed, the cells in corresponding rows under Column D should have all been input 13/8/2020. Then, I need to add the completion date into the original Excel file which was output to DT1.

Please let me know if I haven’t explained it clearly… thank you.

#2

So you can use the row from dt2 to get the row index from dt1.
For each row dt2
I.e assign excel Row Index = dt1.rows.indexof(row) +1

This will identify the row index which you are working on in dt2 and relate it back to the row in dt1, the plus 1 is for excel index as it starts from 1 not 0

#3

Could you explain it more clearly?

Assign Activity: RowIndex = dt1.rows.indexof(row) +1
and set RowIndex as Int32 type. Then? How to input date for each row correctly?

#4

You can then use that row index variable to update the row in excel using write cell activity, if the column is always the same (D) then in range it would be “D” + RowIndex.tostring

#5

I just tried… but it seems that it doesn’t start writing from the filtered row, but from the beginning, including the header. Moreover, it doesn’t stop at the end of the row (i.e. the last info Number row)… Please check the pic below. I have also uploaded the trial xmal with the sampled Excel file for your review. Thank you!

date
date661×576 8.8 KB

xaml.zip (13.4 KB)

#6

Ok so instead of using Dt1.rows.index(row) + 1 you can use

Dt1.rows.indexof(dt1.select("[Info Number] =’"+row.item(“Info Number”).toString+"’")(0))+1

This is assuming that info number is unique.

#7

Thank you for you method. But it doesn’t work on the sample xaml file. Moreover, there could be two or more identical Info Number as the data in the Excel sheet is accumulative.

#8

Hi @wth1993, may I know do u need to add completion date to DT1,

HOW IS COMPLETION DATE CACULATED

#9

Yes. For details, please check the attached file which contains the Xaml & Excel file.

xaml.zip (13.4 KB)

#10

xaml.zip (13.8 KB)

I have amended the logic as you said could be identical info number

It extracts the datarows that meed the criertia into an array of datarows.

You can then use the index of that item in the array to return the correct excel row :slight_smile:

If this has helped you, please mark as the solution.

Thanks,

Tim

#11

Thank you for your solution. It seems to work well. However, I have a question as follows:

I noticed that this flow (i.e. only one DT is needed, extracting the datarows that meet the conditions into an array of datarows) is fundamentally different from my previous logic (i.e. filtering DT1 with conditions and then perform For Each Row activity in DT2 (i.e. filtered DT1). Would this change in logic affect any activity inside the For Each activity? Because I have many activities inside it (including the use of other Data Tables and for each row activities).

The only effect I observed is that under the For Each activity, it runs from the smallest Info number and ends with the largest number. But for my previous logic, it runs from the first row where Info number exists and Column D is empty and ends with the last row where the 2 conditions are met. Would be

#12

Anyone please help… I would prefer completing the task under my previous logic as it works well except that I don’t know how to add the completion date into the original unfiltered Excel file…

#13

So the reason I used the array of datarows is that it captures the row index, whereas if you use the Filter DT it creates a new DT so makes it difficult to then refernce back to the correct DT.

Depending on what other activities you have inside that for each row in DT2, you can take the array of Datarows and convert it to a Datatable to complete those tasks if you want, or just directly use the datarow you are working on to complete the other activities.

#14

Hi Timk,

I just tried to replace the old logic with your one. There are a few unexpected errors I don’t know how to resolve… Could you please take a look?

  1. Error in Assign activity

    Assign.PNG
    Assign.PNG2538×694 157 KB

  2. Error in calling the info number

    call info number
    call info number1618×221 140 KB