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.
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
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
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!
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.
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
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…
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.
Anil_G
ashokkarale
Eric_Alvarado
Yoichi
Julian_Muhlbauer
SorenB
lbowen
ayumi.ouchi
lska
ppr