Updating of count of transaction with certain condition for each employee

Help
1

Hi, I’m trying to figure out how to do a count for number of approved and pending claims for each employee in sheet 1 and update the count number in sheet 2 in the attached images. I’m stuck in counting the total count and update in only row 1 instead and I got more confused.

image
image333×509 43.4 KB
image
image679×509 49 KB

Appreciate any advice how to do it. Thank you.

2
  1. Read only Employee IDs from sheet2 into a data table DT1.
  2. Read the data from Sheet1 into a data table DT2.
  3. For Each row in DT1
    {
    count = DT2.Select(“[Employee ID]='” + row.ToString + “’ AND Status=‘Approved’”).CopyToDataTable.Rows.Count

use write cell activity to write this count to sheet2
}

3

For Each row in DT1
{
count = DT2.Select(“[Employee ID]=’” + row.ToString + “’ AND Status=‘Approved’”).CopyToDataTable.Rows.Count

Do I do an assign to count first? Then write the count to sheet2?

I’m quite a beginner here and might not get it. Thanks for the patience.

4

@junnieset

  1. Declare two integer variables and say ‘count’ and ‘Index’

  2. In Assign, count = DT2.Select(“[Employee ID]=’” + row.ToString + “’ AND Status=‘Approved’”).CopyToDataTable.Rows.Count

  3. In Assign, Index = DT1.rows.indexof(row) + 2

  4. use Write Cell activity inside Excel Application Scope activity and pass below:

Sheet Name: “Sheet2”
Range: “C”+Index.Tostring (For approved claims)
Value: count.Tostring

Same you follow for pending claims also. If you face any issues then let me know.

1 Like
5

@junnieset,

I have created sample workflow for you. Can you please check and let me know.

CountOfColumns.zip (12.3 KB)

Thanks,
Arunachalam.

1 Like
6

@junnieset

Can you please use write range for sheet 2. I have updated workflow for you. Please check and let me know.
CountOfColumns.zip (19.8 KB)

Thanks,
Arunachalam.

7

I followed the above steps and got an exception.
image

This is my workflow (Main.xaml (8.9 KB)), wonder if you can see where I had gone wrong?

Thanks so much.

8

Hi, thanks for helping. I’m trying to do a count for number of approved claim and number of pending claims for each employee. How do I convert the update in the workflow to number of claims instead?
I had edited to updatestatus.count in the IF activity and ran the workflow. The count is still not correct. How to I get the count? This is the file I edited using your workflow. (CountOfColumns.zip (19.4 KB))
Thanks so much for your help.

9

@junnieset,

Can you please check this updated workflow. Here count will increase for each customer Approval and Pending Status.

CountOfColumnsValues.zip (21.1 KB)

Thanks,
Arunachalam.

11

Hi, thanks for the workflow. I added some entries to try. How about if I have additional approval for the same customer? What should I do with the count? For example, after adding some more names, Arun should have 1 approval and 1 pending and happy should have 1 approval not 3. I attached the workflow for your help.CountOfColumns.zip (19.7 KB)

Appreciate your guidance.

12

bump

13

@junnieset,

May I know still you’re facing that problem. if not please make as a solution.

Thanks,
Arunachalam.

15

Yes, I’m still facing some problem as mentioned above. Another question is, what if there is another header such as this (junniest.xlsx (10.6 KB)
), how to do the count? Appreciate your help. Thank you.

16

Hi, wondering if you manage to see my post for reply as I have further questions to ask but my reply doesn’t seem to reach you. Thank you very much for your help.

17

Hoping someone can help me out with this. Thank you very much.