Many to Many relation between 2 sharePoint lists; Create it as Multi-Value field OR create 3rd list

Valued Contributor

I have 2 SharePoint online lists:-

1) Task
2) Project

where a task can be associated with zero,one or multiple projects.

Now I have 2 approaches to store this Many To Many relation between the Task and the Project list inside SharePoint:-

1) To store the ProjectIDs inside a SharePoint lookup field OR inside a single line of text and separate the ids by ";"..

2) Second approach; to build a third SharePoint list which store the TaskID and the ProjectID something as follow:-

ID | ProjectID | Task ID

1 | 100 | 1

2 | 200 | 1

3 | 100 | 2

4 | 300 | 3


Now in SharePoint it allows us to store such an info inside a field, without having to create a second list, but the issue inside Power Apps (as we are going to build the CRUD operation for both lists inside Power Apps) is with the delegation errors we will get if we try to get Tasks under a Project, either if we try to filter the lookup field, or do a search on a single line of text field which contain the ProjectIds separated by ";".. so is creating a third list a valid approach to follow? and if it is so, then why i did not find any online documentation which uses this approach ?

0 Replies