Power Apps - Connection to SQL Query and Limit Access by User

[JDog]

Hi Folks, new to the forum and exploring whether Power Apps is a good fit for us. Our data is on Azure SQL Server. We run a service business and have multiple tables in the database. The "Jobs" table has the data that I would like to send via Power Apps to field techs to complete. "Jobs" is relational to "Job Sites", "Customers", "Suppliers" (tech or subcontractor), etc. Therefore, "Jobs" has FK links to PK fields in these tables and won't display "Customer Name", etc, just the "Customer ID", etc. 

So, my 2 x questions are:

1. Can I link a Power App to an SQL query or how is it best done? The Jobs table alone won't be sufficient.

2. Can I restrict records based on the "Supplier" (or contractor) ID so they can't see other tech's allocated jobs?

Just exploring whether the above are indeed possible before we take this further. Appreciate any help from more knowledgeable developers.

Cheers

[Pstork1]

In Power Apps you normally connect directly to a Table or View in SQL using a connection account. You can simulate SQL queries using functions like Filter(), Sort(), and Search().  But you can't actually run a traditional SQL query or a Stored Procedure.  Also, The connection is done doing a connection account so there is no way to apply specific user permissions to the connection dynamically.

You can run traditional SQL queries and Stored Procedures using Power Automate flows.  But the connection is still done using a connection account so the application needs to apply and maintain the permissions.

[JDog · Accepted solution]

Hi Pstork1, Thanks for the reply. Looks like it might have to be an ASP.NET page instead. Appreciate the help.