Skip to main content

Notifications

Power Automate - Building Flows
Answered

Grouping records

Posted on by 151
Hi All,
 
So I am trying to group records and send the results out via email. I have tried many iterations and keep hitting a wall in the logic. 

Let me run through an example.

I have a SQL table with 1000 records of OverdueItems. Each record has the UserID, CourseTitle, DueDate, SupervisorEmail. The goal here is to send the supervisor a list of all their employees with the details. In my example I have 1000 records, but lets say 100 Supervisors. That means 100 emails would be sent out. Now on each email there may be 2 UserID's or 75 UserID's. It all depends how many are grouped for that supervisor. 

I have tried appending arrays and creating conditions, but I seem to get lost in the logic and i just hit a wall. 
 
If someone has done this before please let me know how you made it possible!
 
thanks,
Jake
  • Jakegolden Profile Picture
    Jakegolden 151 on at
    Grouping records
    @Zdenka_Cech

    Thanks so much for this! Worked like a charm. Perhaps I overread some of your steps, but the only thing I had to change was the filter array and i needed to use Items(Apply_to_each)?[SupervisorEmail]. 

    I was getting an error with Items(Apply_to_each). Unable to process a string vs object. But quick and easy fix! 
     
    thanks again!
    Jake
  • Verified answer
    Zdenka_Cech Profile Picture
    Zdenka_Cech 24 on at
    Grouping records
    Hi Jake 
    I had similar scenario. I suggest getting distinct list of the supervisorEmails, than you loop this list and filter the records in Overduesitems and within the loop send them emails.
     
    Actions should look like following
    1. from the source table you get to the powerautomate: ('OverdueItems') add Select action and switch map to text mode and add item()?['supervisorEmails'] only  # this will create simple array pulled all records from column named 'supervisorEmails'
        1.a for a complex array you can map as name 'supervisorEmail' with item()?['supervisorEmails']
        1.b than you need to add new action 'Compose' with  join(body('Select'), ',')     #this will turn to simple array you will use in next step for union
    2. Then use Compose let's name  'GetSupervisorEmails' and union the  emails to get distinct values of emails, expression looks like following: union(variables('Select'),variables('Select'))
    3. Next action is loop using the 'Apply To Each' with the outputs to loop of 'GetSupervisorEmails'
    4. in the loop you go and add 'Filter array' action with body of your source ('OverdueItems') with filter field 'SupervisorEmail' equals/contains the items('Apply_to_each')
    5. Optional - Still inside the loop add the table transformation you want (Create HTML table or create csv) from the 'Filter array' outputs #this is optional but comes handy if you want to have a csv attached in the email or just for your reporting.
    6. again within the loop send email to the items('Apply_to_each')  #this picks up current item processed in the loop - the 'SupervisorEmail'
     
    Let me know if you got stuck with the steps.
     
    *Zdenka
  • Chriddle Profile Picture
    Chriddle 7,086 on at
    Grouping records
    You can either do the grouping on the SQL server or within a flow using xpath.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,591

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 64,090

Leaderboard