Cross Subsite lookup list

[James Burch]

Hi

We have an O365 SharePoint Online site collection with multiple sub sites of student data. Each sub site represents results of a set of students in a particular class from a particular year.

I'd like to create a lookup list at the parent site level that displays all the results gathered from all the sub sites for each student. We can then filter that list for each student or each year etc. Can anyone recommend a method of doing this?

I can't see how to easily query all the sub site lists and bring the data into a parent list.

Thanks in advance.

[#2]

@James Burch , what is the information (results) you are trying to aggregate from subsites? Is the information contains in SharePoint lists? Are you working in classic or modern UI.

Not having the details, if using modern UI normally you could:

1) Use Power Automate to sync the results at the Root site in a list. 

2) Use search to aggregate information from multiple sites (or subsites). This would requires to create managed properties in the search engine. To display results and have refiners (filters) you would be better of using the PnP modern search web parts: https://microsoft-search.github.io/pnp-modern-search/

[#3]

@Martin Coupal 

Thanks for responding. The site is done in classic UI and the results are in the class list. So a class list could be Maths with all the student names in one column and their test results in the subsequent columns. Each student could have many results depending on how many tests they took.

[#4]

@James Burch 

So the same options would be valid. For option #2 using search, you would need to use the classic search and refiners web part

However, this requires some knowledge using the search engine and also display templates for the search results. 

https://support.microsoft.com/en-us/office/configure-a-content-search-web-part-in-sharepoint-0dc16de...

https://docs.microsoft.com/en-us/sharepoint/use-result-types-and-display-templates

[#5]

@Martin Coupal 

Thanks Martin, I'll investigate the search functions and see if can offer what I require.

[#6]

@James Burch There could be an easier solution using a commercial web part like this : https://www.infowisesolutions.com/product/SmartRollupPro

https://www.infowisesolutions.com/blog/comments.aspx?id=243

I've been working with these web parts some time ago and they work pretty well. Don't know if you can buy only this web part or need to get the bundle.