Power Platform Community Forum Thread Details

We want to start a new project for audit management,

1) the system contain these main entities and the relation between them:-

- Project

- TaskList. One Project can have many TaskList. and one TaskList can have one to zero Project

- Task. One Task can have one TaskList. and one TaskList can have many Task.

- TaskVersionHistory. One Task can have zero to many TaskVersionHistory. and one TaskVersionHistory can have one and only one Task

- TaskPersonnel. One TaskPersonnel can have one and only one Task. and One Task can have zero to many TaskPersonnel.

- TaskMilestones. similar relation to TaskPersonnel and Task.

2) The system need to be available on mobile devices and have the Offline capabilities for submitting data and reading data.

Now we have settled on using Power Apps >> but we are not sure about the data source (SharePoint or SQL Server). Now the customer have Microsoft Business Premium licenses for their users, so both approaches can be used + we have the needed skills in SharePoint + SQL Server. but we are not sure about which data source most suitable to our project.

Now from my point of view we have those pros and cons:-

SharePoint pros

- we can define custom permission for each item. for example we can define that tasks can only be accessed by the TaskPersonnel

- Power Automate fully support SharePoint, such as defining custom permissions on the items

SharePoint cons

- we it comes to build relation between entities SharePoint will fail

- using Lookup fields to define the relation will not work

- trying to do advance filtering for example get the TaskVersionHistory for a Project will be very hard (almost impossible) without getting delegation warning

SQL server pros

- we can define advance filtering inside SQL views without worrying about delegation warning

- we can define foreign keys inside SQL server to build the relation

SQL server cons

- we are going to use explicit connector, so we need to manage the user access inside the database. for example our app will be used by 200 users.

- defining custom permissions seems not straightforward as in SharePoint case.

so can anyone advice about pros/cons of using Sql Server and SharePoint in ur case?

Thanks

Categories:

Building Power Apps