Forum Discussion
Linking 3 lists to show one column's value, possible?
We have 3 lists:
- Teams - columns: Title.
- Services - columns: Title, Team (lookup, one value).
- Customers - columns: Customer name, Services (lookup, multiple values).
The relationships are:
- One Team can provide one or more Services.
- One Service can be provided by only one Team.
- One Service can be provided to one or more Customers.
- One Customer can receive one or more Services.
so between Services and Customers, there is a many to many relationship.
We would like to use the current user's Department name, which is the same as the Teams Title name, to show the list of customers, that this user's Team is providing Services for.
We tried to use the web parts and connections between those, but as the relationship between Customers and Services is many to many, that was not possible. Not sure if linked data sources is the way to go.
Can anyone point us in a direction, that may help us realizing the above?
Will appreciate any advice, thanks in advance
John
This is the solution we came up with:
1. We identified the scope within which the current page is.
2. For that scope we retrieve the necessary data using javascript and store them in localstorage for later usage with an expiration time of 10 hours.
3. Each page that an end-user access then presents the data needed by taking it from the localstorage.
5 Replies
Thanks to Pete W., Dean G. and Scott M. for your input.
We will for now go with a manual filtering of the customers, as the services provided by the each team is small and known for now.
Will look into the Flow Common Data Model as that looks useful and nearly out of the box :)
Will update this thread, should we find a solution that fits our needs.
Kind regards, JohnThis is the solution we came up with:
1. We identified the scope within which the current page is.
2. For that scope we retrieve the necessary data using javascript and store them in localstorage for later usage with an expiration time of 10 hours.
3. Each page that an end-user access then presents the data needed by taking it from the localstorage.
You could always use PowerQuery in Excel to do the relationship, save the Excel file then embed that document within the site with Slicers. Alternatively, you could do the same in PowerBi and Expose the report through the embed code.
On approach would be to use an Access Web App.
Other approaches will require some code,
MarcDAnderson has some examples on his blog that could be helpful
Another approach would be to use the new Flow Common Data model, see https://flow.microsoft.com/en-us/blog/flow-and-common-data-model/
Hi John,
We have a framework which can handle this scenario. Happy to arrange a chat to determine its suitability.
Regards
Pete
