I believe a solution can be implemented with two Power Automate flows, structured as follows:
Flow 1: Retrieve Distinct Event BookingIDs
This flow collects the unique BookingIDs from your 'Booking People' list. For example:
- Event-B001: 5 attendees
- Event-B009: 3 attendees
- Event-B041: 7 attendees
The flow returns a list of distinct BookingIDs: B001, B009, and B041.
Flow 2: Retrieve PersonIDs for Selected Event
When an event is selected (e.g., Event-B009), this flow retrieves a list of PersonIDs associated with that event. For instance:
- Event-B009 → PersonIDs: P023, P104, P009
Implementation Outline
1.Triggering the First Flow:
- In my test environment, I used a button’s OnSelect property to trigger the first flow.
- The returned event BookingIDs are stored in a collection (colBookingID) and used to populate a dropdown menu for selecting an event.
- Alternatively, for better user experience, you could move this code to the screen’s OnVisible property, preloading the event data when the screen loads.
2.Triggering the Second Flow:
- Once an event is selected from the dropdown, its OnChange property triggers the second flow.
- The second flow retrieves the associated PersonIDs and stores them in a collection (colBookingPeople).
3.Displaying Data in a Gallery:
- The gallery can be filtered using the colBookingPeople collection to display information about the attendees. Something like:
AddColumns(
RenameColumns(
colBookingPeople,
PersonID,
X_PersonID
),
X_PersonDetails,
LookUp(
'Person Details',
PersonID = X_PersonID
)
)
Here is a ScreenShot of my test environment:
- "noBookings" is the button to collect the BookingIDs, the "13" next to it is the number of uniques BookingIDs.
- Then you see a dropdown to select one of the thirteen available bookings, in this case, "B007", which returned 15 people who attended that event with their information from a SharePoint List called 'Person Details'.
- A gallery of the people who attended event "B007"
- The "15" at the bottom is a count of the rows in the gallery.
- Please keep in mind, I set the Data Row Limit in my test environment to "8", showing these collections go past the delegation limits.
- The limitation now is from SharePoint at 100,000 records.
I do want to warn you the second flow responds within a reasonable amount of time, usually a few seconds.
But the first flow, has to go through many records to get its data, so it took somewhere between 40-60 seconds to return data.
The first flow could be eliminated if there were a List of events, that maybe stored "BookingID", "EventName", "Facilitator", "Location", etc.. Then you could use that list without the first flow to get event attendees.
Next Steps
If Power Automate is an option for you, I can provide a more detailed step-by-step guide to implement this solution. Let me know how you’d like to proceed!
