Manage Many-To-Many relation between Tasks & Projects SharePoint lists; by storing the ProjectsIDs inside the Tasks field OR by creating a seperate list

(0) Share

Report

Posted on by johnjohn123

3,514

I have 2 SharePoint online lists:-

Task
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 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 second list a valid approach to follow? and if it is so, then why i did not find any online documentation which uses this approach ?

Thanks in advance for any help

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@johnjohn123

Use a third list. This is standard for classical database design. It is called a junction table and the principles are well established. The shortcuts will cause problems.
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp But the confusion comes as SharePoint allow us to store multiple values inside a single field, such as using choice,lookup, people/group which allow multiple values/selections.. so seems we should stay away of these multi-value fields types and create a separate list represent the M-to-M relation??.. but did you ever find any link/documentation which mention this approach? i simply did not find any documentation describing/suggesting this approach to overcome the delegation warning we get when we try to filter choice,lookup,people/group fields which allow multiple selections.. did you get my point, why i asked this question?

Thanks
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@johnjohn123

SharePoint existed for years before PowerApps and many of those properties were created to make it easier to deal with lists within SharePoint as it is not a relational database. PowerApps was layered over SharePoint and the same functions can be re-created within PowerApps following established database principles. The native back end database for PowerApps is Dataverse (formerly CDS), and the relationships are part of it. To use SharePoint, I suggest reading @WarrenBelz 's blog Practical PowerApps https://www.practicalpowerapps.com/ and my blog post here. https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-PowerApps/ba-p/848842 .

As my dad told me, just because you CAN do something doesn't mean you SHOULD do it.
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp Ok thanks for the reply.. now to keep the question precised , so you are saying that creating a M-To-M sharepoint list is the way to go, rather than using Multi-Values fields?
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@johnjohn123

Yes