Unanswered

Using Foriegn keys in SQL Server Vs using Lookups in SharePoint

(0) Share

Report

Posted on by johnjohn123

3,514

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

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@johnjohn123

One of the advantages to using SQL is that you can build views in SQL based on a query and use the view as a datasource in PowerApps.
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@johnjohn123

One of the advantages to using SQL is that you can build views in SQL based on a query and use the view as a datasource in PowerApps.
rbrooks66 143 on at

Like
a
(0)

Report
Copy link

Link copied!

In addition to using views, you can also use stored procedures (https://www.mssqltips.com/sqlservertip/6857/call-sql-server-stored-procedure-from-power-apps/) that allow you to insert data into the table. Using SQL also allows you to use drop downs assigned to different reference tables. The one downside, is using SQL is considered a premium service and they charge more for it. For one app, it's a nominal fee (something like $5 per user per month) but if you plan on developing multiple power apps using SQL, the fee structure doesn't seem to have been thought out because it was something like $2400 per year for 1 portal which only allows you 2 applications. If you are planning on doing something like that, I would suggest you arrange a meeting with a Microsoft license person to discuss what you want to do (long term) and make sure the costs are known up front.
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp thanks for the reply.. so if i build a view inside SQL >> and then inside Power Apps >> i define the view as the datasource for a gallery >> then what will happen when the user access the gallery? will all items inside the view get fetched at once? or the gallery will show 100 items >> and as the user scroll the power apps will show extra 100 items and so on? similar to how gallery work when defining SharePoint as the data source?..

Second question, is using Foreign keys inside SQL tables work well inside Power Apps? or it is better to store the Parent key as number field? similar to how we build the relation inside SharePoint (by storing the parent ID inside a number field) instead of using lookups SharePoint columns? as Power Apps does not like the SharePoint lookup columns..

Can you please advice on my above 2 points?

Thanks
rbrooks66 143 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry, I didn't use a gallery. I connected the drop down directly to the view in SQL.