none
Consolidating detail data from various SharePoint subsites into one list RRS feed

  • Question

  • Hi,

    we have a SharePoint List, which acts as an overview for approx. 1000 projects.
    Each project has it´s own SharePoint site. On each project sub-site there are several lists with e.g.

    • financial details
    • time details
    • administration details
    • etc.

    Now I want to establish a query, which

    • steps through the overview list,
    • reads the given URL to the SharePoint subsite of each project,
    • open e.g. the financial details list in this project´s subsite
    • reads the desired columns
    • and repeats the previous steps for each project
      until all financial details of each project are collected

    Below I wanted to provide snapshots, but as a new user I was not allowed to post pictures.

    As I have no idea how long it will take to approve my account and the fact that I need a solution rapidly, I have created some old-fashioned Text-graphics, hoping that helps to illustrate the scenario.


    This is the SharePoint site structure
                                               Overview site
                                                        |
                                      Project         |
                                      Overview____|
                                      List             |
        ________________________________|________________________________

        |                                              |                                               |
    Subsite Project1               Subsite  Project2                        Subsite Project3
        |                                              |                                               |
        |_Financials                               |_Financials                                |_Financials
        |                                              |                                               |
        |_...                                         |_...                                           |_...

    This is an exceprt of the ProjectOverview List

    Project Name | URL

    Project1        | http://sharePointSubsite/Project1/Financials

    Project2        | http://sharePointSubsite/Project2/Financials

    Project3        | http://sharePointSubsite/Project3/Financials

    The URL-column holds the links to the project subsites

    These are excerpts of the Financial Lists in each projects subsite.


    Subsite Project1:

    Budget Planned | Actual Budget | Cost Planned | Actual Cost

    10000               |      8000        |    7000          |  3000


    Subsite Project2:

    Budget Planned | Actual Budget | Cost Planned | Actual Cost

    20000               |      18000        |    15000          | 10000

    Subsite Project3:

    Budget Planned | Actual Budget | Cost Planned | Actual Cost

    30000               |      38000        |    29000          | 32000


    The resulting list should then look like

    Project            |Budget Planned | Actual Budget | Cost Planned | Actual Cost

    Project 1         | 10000              |      8000        |    7000          |  3000

    Project 2         | 20000              |      18000      |    15000         | 10000

    Project 3         | 30000              |      38000      |    29000         | 32000


    Lately I found some very interesting Youtube tutorials by "The Power User"

    Although Miguel Escobar provides really excellent tutorials, I was not able to figure out, how to implement the above described scenario.

    As the solution to this would be a real life saver for me, any feedback is highly appreciated.

    Thanks in advance for your contributions and looking forward for your feedback.

    Best regards

    Frank Radewagen

    • Edited by Frankreich Monday, June 4, 2018 6:21 PM
    Monday, June 4, 2018 6:19 PM

Answers

All replies