May 05 2020 10:39 PM
Hello, It sounds to me like a very common and simple scenario, but I wasn't able to find any solution.
Have this scenario:
Two tables (Sharepoint lists): Products and ProductGroups.
One Product can be in multiple ProductGroups and one ProductGroup can contain multiple Products.
I need a list of Products and see to what ProductGroup it belongs - and vice versa: list of ProductGroups and see what Products it contains.
When I use a lookup field and allow multiple entry, it works as expected in one way only - I see all ProductGroups my Product belongs to (Image 1), but when I go to ProductGroup list, there is no reference to my Product list (Image 2).
Image1:
Image2:
Do you have any idea how to link those tables? It would be very helpful.
May 06 2020 06:05 AM
@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.
May 07 2020 06:01 PM - edited May 07 2020 10:53 PM
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:
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!
Sep 29 2021 12:50 AM
Jan 05 2023 04:14 AM