Populate Form Fields using Custom SQL Query

[Adhan]

Hello forum , 

I am looking into building a Power App with a form in front. some of the fields in the form are to be populated automatically from the result of a custom sql query. how can I achieve this ?  Can I trigger the query directly from the Power app form ? Do i need to use the Power Automate to bring the data from the Sql server.  We have on-prem sql and the gateway connections in place. 

thanks for your help

[timl]

Hi @Adhan 

It's not possible to call the query directly from Power Apps. You'll need to execute the query from a Power Automate flow.

If it's possible to encapsulate the query in a SQL Server view, you can connect your Power App form directly to the view.

[Adhan]

Hi timl, Thanks for your response. The second option seems possible.  we can create sql view in the db that is not a problem but the view needs to be filtered to specific value from the field in the form for example customer number field.  can we filter the result sets to the specific customer in context ? 

The first option with Power Automate, will this method increase the cost of the solution as power automate flow will incur cost every time it is run right ?

As a third option, is it possible to use virtual table in data verse to surface result from the sql view?

Apologies for multiple sub questions.  

[timl · Accepted solution]

Hi @Adhan 

In answer to the first question, yes you can filter a view. Let's say you want to display customer number 1234. To do this, you would set the Item property of your form to this:

LookUp(YourSQLView,
       CustomerNum=1234
)

For question 2, SQL Server is a premium connector and requires a premium license. If you were to purchase a Power Apps per user/per app license, this would include Power Automate flows that are called from the app, so there would be no additional cost on top of that.

For the 3rd option, yes it's possible to use a virtual table in Database. It's worth reading about some of the limitations in the documentation, such as the requirement to surface a GUID unique column.

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/get-started-ve

[Adhan]

Thanks @timl. you are super star. 

[timl]

Thanks @Adhan glad to help!