Sharepoint Lists many-to-many relation

%3CLINGO-SUB%20id%3D%22lingo-sub-1362265%22%20slang%3D%22en-US%22%3ESharepoint%20Lists%20many-to-many%20relation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1362265%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20It%20sounds%20to%20me%20like%20a%20very%20common%20and%20simple%20scenario%2C%20but%20I%20wasn't%20able%20to%20find%20any%20solution.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20this%20scenario%3A%3C%2FP%3E%3CP%3ETwo%20tables%20(Sharepoint%20lists)%3A%20Products%20and%20ProductGroups.%3C%2FP%3E%3CP%3EOne%20Product%20can%20be%20in%20multiple%20ProductGroups%20and%20one%20ProductGroup%20can%20contain%20multiple%20Products.%3C%2FP%3E%3CP%3EI%20need%20a%20list%20of%20Products%20and%20see%20to%20what%20ProductGroup%20it%20belongs%20-%20and%20vice%20versa%3A%20list%20of%20ProductGroups%20and%20see%20what%20Products%20it%20contains.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%20a%20lookup%20field%20and%20allow%20multiple%20entry%2C%20it%20works%20as%20expected%20in%20one%20way%20only%20-%20I%20see%20all%20ProductGroups%20my%20Product%20belongs%20to%20(Image%201)%2C%20but%20when%20I%20go%20to%20ProductGroup%20list%2C%20there%20is%20no%20reference%20to%20my%20Product%20list%20(Image%202).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EImage1%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Viko2020_1-1588742698374.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F189397iB3972293D2B23AC3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Viko2020_1-1588742698374.png%22%20alt%3D%22Viko2020_1-1588742698374.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EImage2%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Viko2020_2-1588742784838.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F189398i506F87B79312ACA7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Viko2020_2-1588742784838.png%22%20alt%3D%22Viko2020_2-1588742784838.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20idea%20how%20to%20link%20those%20tables%3F%20It%20would%20be%20very%20helpful.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1363461%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20Lists%20many-to-many%20relation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F657258%22%20target%3D%22_blank%22%3E%40Viko2020%3C%2FA%3E%26nbsp%3BFirst%2Cwe%20should%20understand%20that%20SharePoint%20isn't%20a%20replacement%20for%20a%20real%20database%20system.%20It%20doesn't%20handle%20relationships%20as%20well%20as%20say%20SQL.%20In%20that%20regard%2C%20you%20can't%20do%20a%20many-to-many%20relationship%20in%20the%20way%20you%20are%20trying%20to%20do%20without%20some%20type%20of%20custom%20work%20that%20would%20keep%20your%20data%20in%20sync%20between%20the%20lists.%3CBR%20%2F%3E%3CBR%20%2F%3ESecond%2C%20in%20regards%20to%20a%20database%20design%20for%20a%20many-to-many%20relationship...%20the%20standard%20construct%20would%20be%20to%20have%20a%20third%20table%2C%20called%20and%20intersection%20table.%20This%20table%20is%20where%20you%20could%20store%20the%20relationships%20for%20the%20Product%20and%20the%20Product%20Groups.%26nbsp%3B%20In%20this%2C%20you%20could%20have%20a%20list%20of%20Products%2C%20and%20a%26nbsp%3B%20List%20of%20Product%20groups.%20Your%20intersection%20table%20would%20have%20a%20look%20up%20to%20both%20of%20those%20lists.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372311%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20Lists%20many-to-many%20relation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372311%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F39836%22%20target%3D%22_blank%22%3E%40Beau%20Cameron%3C%2FA%3E%26nbsp%3Bfor%20your%20note.%20Finally%20I%20was%20able%20to%20achieve%20partial%20success%20with%20Power%20automate%20flow.%3C%2FP%3E%3CP%3E1)%20Products%20table%20with%20Groups%20field%20as%20a%20multiple%20lines%20of%20text%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20ProductGroups%20table%20with%20Products%20lookup%20field%2C%20multiple%20values%20allowed%3C%2FP%3E%3CP%3E3)%20Flow%20triggered%20with%20each%20ProductGroup%20change%20which%20will%3A%3C%2FP%3E%3COL%3E%3CLI%3EClear%20Groups%20field%20for%20all%20Product%20records%3C%2FLI%3E%3CLI%3EFor%20each%20record%20in%20ProductGroup%20and%20for%20each%20record%20in%20its%20Products%20lookup%20field%20find%20matching%20record%20in%20Prouducts%20table%20and%20update%20its%20Groups%20text%20field%20(concatenate%20with%20previous%20value)%20with%20link%20to%20particular%20ProductGroup.%20This%20is%20just%20an%20example%20of%20updated%20field%20value%3A%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Econcat(item()%3F%5B'Groups'%5D%2C%20'%3CA%20href%3D%22%2C%20items(%22%20apply_to_each%3D%22%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E'%2Citems('Apply_to_each')%3F%5B'Title'%5D%2C'%3C%2FA%3E')%E2%80%8B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EResult%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Viko2020_0-1588917082865.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F189985i5B16865553937A3C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Viko2020_0-1588917082865.png%22%20alt%3D%22Viko2020_0-1588917082865.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Viko2020_1-1588899256450.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F189955i66DA5F7A3B27BE9A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Viko2020_1-1588899256450.png%22%20alt%3D%22Viko2020_1-1588899256450.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20only%20disadvantage%20of%20my%20original%20intention%20is%20that%20Groups%20field%20inside%20Products%20table%20is%20a%20http%20link%20without%20option%20to%20link%20Product%20with%20ProductGroup.%20Table%20(okay%2C%20better%20say%20list)%20linking%20must%20be%20always%20made%20from%20ProductGroups%20table!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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:

Viko2020_1-1588742698374.png

 

Image2:

Viko2020_2-1588742784838.png

 

Do you have any idea how to link those tables? It would be very helpful. 

 

2 Replies
Highlighted

@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.

Highlighted

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:

  1. Clear Groups field for all Product records
  2. 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:

Viko2020_0-1588917082865.png

 

Viko2020_1-1588899256450.png

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!