Sharepoint flow working for n-1 records

[venka91]

Hi there,

I have share point list and there are totally 78 rows in the list. I am using Get Item in Flow to get all records and increment a variable based on Choice column.

But the flow is looping for only 77 records. can any please tell why ? I have exported it to csv and veritfied it has 78 records. But flow in not going through all record.

[Pstork1]

Are you doing this in an Apply to Each loop or a Do Until loop?  If its a Do Until loop it might just be the way you are incrementing the variable.

[venka91]

But for some reasons i deleted the action and now I see the count. But now I am trapped into big issue and do not know how do I derive my use case.

I am trying to send an email to department managers when the tickets are overdue for my helpdesk application. 

So in get items i am using a filter for due date and status which are open and in progress.

I have 10 departments in org. So when i want to check the open and in progress ticket which are overdue and send the count of overdue items to manager by email.

So I initialized 2 variables as varOpen and varProgrss.

After get item, i am using switch control for department.

On department switch action, i am using one more switch control for open and in progress, and increment ing the variable count.

Now how do I send email for departments managers ?

If i add send mail action outside switch i am not getting the correct count for specific department.

If i add with switch email are looping for each variable increment.

Please help to handle this situation.

[Pstork1]

Are you ordering your Get Items by Deparment?  If you do that it will group each department's records together.  Then you can set a variable for current department and only send the email after that department changes.  Inside the switches save the info for the email in an array and convert that to a table just before you send the email when the department changes.

[venka91]

Do you mean i should create variables for each department and store them in array and convert table and send as an message.

I am not very clear about your message. Is that possible you could give me flow for one department and i would do them for all please .

[Pstork1]

Sorry, no.  Its a difficult pattern to explain, but its needed frequently.  Let me try to draw it out more graphically

1) Get Items Sorted by Department

2) Initialize Variable to hold Current department - set value to "NoDepartment"

3) Initialize Array to hold information that will be emailed.

4) Apply to Each loop

    a) Condition does department = current 

        No) Transform Array to HTML Table and send in Email

                Clear Array

                Set department variable to current record department

                Process values and add to array

        No) Process values and add to array

5) After loop exists process final department

         Transform Array to HTML Table and send in Email

Hope that makes it a bit clearer.  The key is to process each department until it changes.  When it changes send the email and start processing the new department.  When you exit the loop you will have just processed the last record for the last department so you still need to send that last email.

[venka91]

Somehow, this becoming complex for me. I am not used flow that much. Since i needed an escalation i trying to achieve it using flow for automated email. I made a sample.. but flow goes very long for two department itself. I am able to achieve the result, but i cannot emails to specific department managers alone. I can able to send only consolidated email to all managers.

Is there any other better approach ?

[Pstork1]

The steps I listed called for the use of two variables, one value and one array.  You have used 9.  Why all the extra variables?  Also can you provide a screenshot of what is inside the loop and the Get Items. I don't think you are following the steps I provided above.

[venka91]

Yes, i am not clear or i do not know because i do not have that much exposure to Power Automate.

Please see my get items.

[Pstork1]

The get items looks correct.  Can you also show what you are doing inside the loop?

[venka91]

My Apply to each as below

and below one level again using switch to count inprogress and open

In general as below

But i need may variables for each department to get the increment value and send mail.

Is this only the way ?

[Pstork1]

Since you are sorting the input by department you don't need to keep each departments output separate.  But you do need to check at the top of the loop whether the current record is the same department as the last record processed.  If it is just add that record to the running totals.  If its not then send and email, clear out all the fields, and add the new record to the running totals.  You don't need to check which department is the current record, just whether its the same as the last one processed.