Counting Totals Across Multiple Sharepoint Lists?

[StevenP]

Hi all,

Happy new year (it's still acceptible to say that half way through January right?)

I am currently trying to do some reporitng on an internal app to total the amount of atendees to a range of classes, the data is spanned across 3 SharePoint lists.

Lists:

• Adults (Name, Gender, ID)
• Class (ClassName)
• Visits (ClassName.Value, AdultID-ID of adult patched in-)

The app allows you to go into a class and log a new visit for one of the Adults.

For my reporting I need to be able to check what genders attended which classes, however there is 40+ classes.

My current solution I have started on is a bit mental:

I have a HTML table, but I am using hidden galleries in order to return the amount of visitors to a class but then doing a dynamic lookup on each item in the gallery to lookup the gender value for that user. Seen as that data is stored in the Adult table, not the Visit table which the gallery is querying.

Text label on the gallery items:

LookUp(Adults, AdultID = ThisItem.UserID,Gender.Value)

My HTML table is then running a countrows on that gallery text input which is filtered to the class:

CountRows(Filter('gal.Class1'.AllItems,Gender.Text = "Female")

CountRows(Filter('gal.Class1'.AllItems,Gender.Text = "Male")

etc

I'm four hours into this and realized this is not a workable solution as I am going to end up with 50 hidden galleries and any additional classes added in future are going to require me to go in and manually add the class to the html table as well as a corrosponding hidden gallery.

Any help is appreciated as always 🙂

[BCLS776]

If I were doing this, I would build a Power Automate flow to run these counts separately from the app and return a table with the desired results. Doing this in an app looks like it will involve multiple, looping (and non-delegable) queries which run sequentially and slow the app. In a flow, we can leverage concurrency, parallel operations, and some table operations to speed this up significantly.

Hope that helps,

Bryan

[StevenP]

I'm not sure where I would start if I was to try and attempt this in flow truth be told, any pointers that I can go and lookup? 🙂 Thanks!

[BCLS776 · Accepted solution]

Yes, you'll get more specific Power Automate answers on its community forum, but start with the SharePoint connector "List Items" connector. You'll want to set up a filter query that brings in your desired Classes to start, and follow by that listing the Visits and Adults under each. You'll likely use the Select and Filter actions to further refine the table before using a Response action to send the results back to the app.

As well, the situation you're describing is a good example of the advantage of a relational database such as Dataverse over SharePoint Lists. Your queries can be done with a single, fast FetchXML query on Dataverse.

Bryan

[StevenP]

Thanks Bryan, I will take a look and see if I can put something together 🙂