PowerApps vs SQL Server

[Anonymous]

Hi there!

I would like help from the community. 

So, I'm making an App, the data source is SQL Server. It's connected with an On-Prem gateway and from that pov, everything is OK.

I have issues with data sources. I have multiple screens and multiple forms and since I have a relational database, in one screen/form in App I have fields from various tables in the database.

For you who have worked on this kind of solution for PowerApp, is a combination of a few fields from different SQL tables available for PowerApps in a single form?

It's quite hard to find answer for this so I assume that it's possible since no one is talking about it 🙂

[BCLS776]

A form is linked to a single datasource in terms of adding/updating records. However, you can do other things that interact with other tables/datasources at the same time:

• You could put a Patch() statement in the OnSuccess or OnFailure property of the form to make changes to other datasources
• You can use LookUp() and Filter() against other datasources to bring in records to populate fields in the current form

Is there a specific thing you're trying to do?

Bryan

[Anonymous]

Yes, here is an example.

I have 4 SQL tables that contain 4 different fields.

e.g. Table

Contract - field Contract_number

Customer - field Customer_name

Payment - field Value

Country - field Country_name

For many reasons, we don't have all those fields in the same tables so we split them in presented tables.

Now, form in Power Apps have;

• Contract_number
• Customer_name
• Value
• Country_name

and question is, can I somehow connect the Power app field for the exact SQL table field?

For now, It's not working.

[BCLS776]

If you need to work with those four separate tables as though they are one within your app, you can use Collect() and/or ClearCollect() to bring them together into an internal collection. Note, any changes you make to the internal collection are not automatically reflected back in the parent tables -- you will need to use a Patch() statement to update those separately.

This internal collection could be based upon Filter()s of the parent tables to keep data sizes and app performance in check.

Is that what you are after?

Bryan

[Pstork1]

Assuming the different tables you are working with are related you can create a SQL View and use the View just like you would use a table in Power Apps.  That way you can have multiple tables represented in a single form.

[Anonymous]

Will try the Patch() function for each field.

[Anonymous]

I'm using SQL View's as data source for some another form, e.g. for Registers that are specified by multi table fields. 

Are you suggesting me that I should use SQL Views for Insert/Update on Power App forms?

[Pstork1]

If the SQL View is created to allow Insert/Update, and there are some restrictions in SQL on what will and won't work, then yes I would use Views to be able to update multiple related tables at a time.

[Anonymous · Accepted solution]

So, this is what I've done.

Since Power Apps and SQL are quite limited and actually they are limit each other parallelly, I had to improvise.

I stayed with SQL Views as primary data source for registers. Within SQL Views, I was not able to create Insert/Update/Delete since SQL does not support multiple table editable Views. For that, I've just used more screens and each screen was connected to his dedicated SQL table. In that case, ordinary Submit clause in PowerApps is doing job for Insert/Update/Delete. Also, I was trying to create procedures on SQL level that will be triggered by PowerApps OnClick function, that will trigger PowerAutomate process, but usually, I've got stucked with PowerAutomate issue, when sometimes process didn't work as it should. Also Power Apps don't support data source which contains Triggers (one Table in SQL and trigger on it) and if you ask me, that is nonsense and something that MS should work on. 

Also, to follow up on the story about the screens, PowerApps don't support few different data sources on same screen (in same form) so if you have data from 3 different SQL tables and you are trying to get them in one form for Insert or Update, that won't work.

[Pstork1]

Forms by their nature are bound to a specific data source.  So they can't hold controls bound to multiple data sources at a time.