All the Canvas apps i built use SharePoint as the data-source. and through my experience i learn not to use SharePoint Lookup fields to build a relation between SharePoint lists, instead to store the relationID inside a number field.
Now i want to build a new Power Apps project, still i did not decide on the data-source. i am afraid of using SharePoint, since the project we are going to build depend on building "complex" relations between different entities as follow:-
1) Project >> each Project will have zero-To-many TaskList >> each TaskList will have zero-To-many Task >> each Task will have zero-To-Many Audit-trail >> each Task will have zero-To-many AssignedUsers >> each Task will have a Status (open, active,completed, closed)
2) We need to build some galleries >> for example which should list all the Audit-Trails under a Project, with the ability to filter the Audit-Trail using a drop-down that list all the Projects.
3) We need to build a gallery which shows Tasks, and to be able to filter the Tasks based on the AssignedUser and/or based on the Project. for example >> show me all the Tasks assigned to UserA and is under Project100
4) Show some Task statistics under each project, for example when the user click on a Project item>> the user will be redirected to another screen, which should show some statistics as follow; show the number of Tasks that are under the current project per status, for example:-
10 open tasks ------ 5 active tasks --- and so on..
Now based on my experience building points 2, 3 & 4 will not work in reality if we use SharePoint as the datasource>> as all the formulas will raise delegation warnings.. so i am not sure if using SQL server will be a more suitable approach?
Second question, i worked with building SQL server tables for large projects , but mainly for building ASP.Net web application, not for power apps... so if we decide to choose SQL Server as the data-source for the power app >> then is it fine to build the relation between the SQL tables using foreign keys? or similar to SharePoint Lookups fields we should avoid using SQL Foreign keys and also store the relationID inside SQL number fields?
Thanks
