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

Question

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 ?

Answer 1

@john john

In my opinion, I suggest you use lookup column in the Task ID list.

1.Create a Project ID list.

2.Create a Task ID list, create a lookup column in the Project ID list.

3.Result.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.