Forum Discussion
Sharepoint Lists many-to-many relation
Viko2020 First,we should understand that SharePoint isn't a replacement for a real database system. It doesn't handle relationships as well as say SQL. In that regard, you can't do a many-to-many relationship in the way you are trying to do without some type of custom work that would keep your data in sync between the lists.
Second, in regards to a database design for a many-to-many relationship... the standard construct would be to have a third table, called and intersection table. This table is where you could store the relationships for the Product and the Product Groups. In this, you could have a list of Products, and a List of Product groups. Your intersection table would have a look up to both of those lists.
- Viko2020May 08, 2020Copper Contributor
Thank you Beau Cameron for your note. Finally I was able to achieve partial success with Power automate flow.
1) Products table with Groups field as a multiple lines of text
2) ProductGroups table with Products lookup field, multiple values allowed
3) Flow triggered with each ProductGroup change which will:
- Clear Groups field for all Product records
- For each record in ProductGroup and for each record in its Products lookup field find matching record in Prouducts table and update its Groups text field (concatenate with previous value) with link to particular ProductGroup. This is just an example of updated field value:
concat(item()?['Groups'], '<a href=', items('Apply_to_each')?['{Link}'], '>',items('Apply_to_each')?['Title'],'</a></br>')
Result looks like this:
The only disadvantage of my original intention is that Groups field inside Products table is a http link without option to link Product with ProductGroup. Table (okay, better say list) linking must be always made from ProductGroups table!
- MichaelAntonFSep 29, 2021Copper ContributorI've been searching for this really long. And the answers are all unsatisfying. As an Airtable user, I will probably always be disappointed with Lists, but this feature should really be a no brainer.
- PaulStrinatiJan 05, 2023Copper ContributorHi Viko2020, would you be willing to share the PowerAutomate steps to achieve this? I have a very similar requirement.
Thanks in advance!