Thank you Piero for the video! Do you have any video in which you have created canvas which will elaborate the master detail relationship using sql server tables and one master table have 4 details tables. How to do that and also if the detail tables can be updated as well.
your course is very good my friend. actually one of the best. but it has been nice if you create those boxes and all the commands step by steps, so it makes learning easier.
Hi Benji, thanks for your comment. In my channel I have created a series of videos that explain how to create a power app from scratch, very similar to what you see here. Take a look and let me know what you think, this is the playlist for that course: th-cam.com/video/jMtZidgYApM/w-d-xo.html
Excellent video, Piero! Thanks the clear steps and explanations. Will the filters and functionality on the Home screen work the same way using a gallery grid instead of a data table?
Hi, yes. You can of course use the same filters in a gallery grid. Watch my Power Apps from 0 to hero videos where I show how to use filters with galleries.
Hi, sorry for the late answer. unfortunately I have tested that scenario. But I think you shouldn't have any problem, there are no restrictions on the documentation.
Nice tutorial. I have just a question. Using SQL View as Data Source, How to update a specific table only? I am new to Power Apps. I have an empty vertical gallery with sql view as data source. Let say I have a view name SampleView created at SQL. This SampleView has table1 and table2 joined.If I use Sampleview as data source for empty vertical gallery. How can I update record on table2 only? Let's say I put Text Input named TextInput1 in the gallery. Its value came from table2. If I modified the value of the first row of TextInput1, how can I update it back on table2?
Hi A G, unfortunately not, however: Here is a full course that I did using SharePoint as the datasource and most of the scenarios apply. th-cam.com/video/jMtZidgYApM/w-d-xo.html
From where we are getting the values which are getting automatically populated in the combo boxes. We are fetching the values from SQL server only? If so, then what is the process?
Hi, yes the values from the combo boxes comes form SQL. You can connect to the table or even create a view and connect to the view as well, as data sources.
By the way there is a full course on my Channel on how to build a power app from scratch, in video #8 I explain how to populate the combo boxes with data from a SharePoint list, the procedure is very similar with SQL: th-cam.com/play/PL8XyLAbV0oQvKOTThg1DHsWFm27cb4F-Z.html&si=_o00PTRMBr4VEgTL
Great video!! I am trying to create a view in SQL server but it keeps saying it is returning no results but when i execute the query on the server I can see the results. Is there something I am missing? If i connect my gallery to another view it works but not my new one. I am using an on prem gateway. any help greatly appreciated.
Hi Rohan, if you can connect other SQL views to your gallery, but not the new one, then the problem is not the connection or the gateway, the problem needs has to be related with the new view you created, perhaps it is related with the data type of some of the columns of your view that are not compatible with power apps and that you need to reformat before displaying the records in the gallery. Check if there is any error message on the gallery when you import the view. there must be something highlighted in red in the Datasource property, or in the same gallery control. Sometimes is just that you need to reformat some of the columns to a Text format or something like that.
Hi Nicholas, currently there is no native way to use SQL statements if your are using the SQL Connector, you can only use the existing functions, you can create SQL views (using select statements) and import those SQL views as tables in Power Apps. However, an alternative would be to use Custom connectors. A custom connector is a wrapper that allows you to connect to any datasource through rest web services. So, let's say you already have your SQL database, and various store procedures and functions that uses SQL statements (insert, delete, update, select, etc.), you can create web services that uses those store procedures, and configure custom connectors so your power apps can consume those webservices.
Hi, this is a Canvas App, and I use the SQL connector. Model driven Apps only support Dataverse. The same with Portals. The benefit of canvas app is the flexibility to use any type of datasource. In this video I'm using a Table control to show the list of records from the SQL Database. I'm not using Forms or Galleries, however if you would like to see how to build a fully functional canvas app from scratch using galleries and forms, check this: th-cam.com/video/jMtZidgYApM/w-d-xo.html
Hi akokoh, for this example, yes. I have a SQL Server Database on my PC and I use the Datagateway to connect it to Power Apps. Instead of your PC it could be any server on your network.
When am patch is not submitting the data to SQL table. It is throwing error and error is "Network error when using Patch function . The requested operation is invalid'. Does we need any permission to patch the data into SQL table
Hi, is your SQL Database On-premise? or in the cloud? if it is On-premise you need to configure your Datagateway (You can follow the instructions here: th-cam.com/video/X6wlrjZDq1U/w-d-xo.html). Network error sounds to me more like you power apps cannot connect to your SQL Database for some reason, make sure your datagateway is working fine. Regarding permissions, you don't need any specific permission, you only need read/write access to your database.
Hi Varun, for SQL server with large number of records, always use delegable functions only: docs.microsoft.com/en-us/connectors/sql/#power-apps-delegable-functions-and-operations-for-sql-server On the other hand, leverage the best practices on SQL like indexing, creating views, etc. for better performance. Also think about using SQL Azure instead of SQL Server is possible. sometimes datagateways can affect your performance: docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance
Hi Piero, Thanks for your quick response. But, with delegable functions it's taking too much time to load and also not correct data. It's not possible to write view's because data having multiple branching folders. SQL server is mandatory. Please reply to this if you get any idea in future also. Thanks
@@varunkumar-ue1vc This is a good article you can follow with recommendations to solve issues with SQL Server. Take a look at the "issue types per data source" section. powerapps.microsoft.com/es-es/blog/considerations-for-optimized-performance-in-power-apps/
Also follow the recommendations here: docs.microsoft.com/en-us/powerapps/maker/canvas-apps/common-performance-issue-resolutions#considerations-specific-to-sql-server-on-premises
This video was really insightful. Thank you Piero
Thank you Piero for the video! Do you have any video in which you have created canvas which will elaborate the master detail relationship using sql server tables and one master table have 4 details tables. How to do that and also if the detail tables can be updated as well.
your course is very good my friend. actually one of the best. but it has been nice if you create those boxes and all the commands step by steps, so it makes learning easier.
Hi Benji, thanks for your comment. In my channel I have created a series of videos that explain how to create a power app from scratch, very similar to what you see here.
Take a look and let me know what you think, this is the playlist for that course: th-cam.com/video/jMtZidgYApM/w-d-xo.html
Excellent video, Piero! Thanks the clear steps and explanations. Will the filters and functionality on the Home screen work the same way using a gallery grid instead of a data table?
Hi, yes. You can of course use the same filters in a gallery grid. Watch my Power Apps from 0 to hero videos where I show how to use filters with galleries.
@Piero Marchena How to handle bulk Data Upload in SQL Server From PowerApps.
Hey Piero. Have you came across a scenario where you didnt have access to all the columns of a table.Is it possible to still perform filter funciton?
Hi, sorry for the late answer. unfortunately I have tested that scenario. But I think you shouldn't have any problem, there are no restrictions on the documentation.
Excelent.
Nice tutorial. I have just a question. Using SQL View as Data Source, How to update a specific table only?
I am new to Power Apps. I have an empty vertical gallery with sql view as data source. Let say I have a view name SampleView created at SQL. This SampleView has table1 and table2 joined.If I use Sampleview as data source for empty vertical gallery. How can I update record on table2 only?
Let's say I put Text Input named TextInput1 in the gallery. Its value came from table2. If I modified the value of the first row of TextInput1, how can I update it back on table2?
Hi
Thanks for video.
Apart from this PowerApps/SQL video, have you done any more, specifically for PowerApps Canvas apps and SQL?
Hi A G, unfortunately not, however: Here is a full course that I did using SharePoint as the datasource and most of the scenarios apply.
th-cam.com/video/jMtZidgYApM/w-d-xo.html
Hi Piero, Could you share the SQL scripts for the tables/views etc. used for this application so that I could try building the same app at my side?
Here you are: github.com/pieromarchena/PowerAppsAndSQL/blob/main/MWDemoDB1.sql.txt
From where we are getting the values which are getting automatically populated in the combo boxes. We are fetching the values from SQL server only? If so, then what is the process?
Hi, yes the values from the combo boxes comes form SQL. You can connect to the table or even create a view and connect to the view as well, as data sources.
By the way there is a full course on my Channel on how to build a power app from scratch, in video #8 I explain how to populate the combo boxes with data from a SharePoint list, the procedure is very similar with SQL: th-cam.com/play/PL8XyLAbV0oQvKOTThg1DHsWFm27cb4F-Z.html&si=_o00PTRMBr4VEgTL
Great video!! I am trying to create a view in SQL server but it keeps saying it is returning no results but when i execute the query on the server I can see the results. Is there something I am missing? If i connect my gallery to another view it works but not my new one. I am using an on prem gateway. any help greatly appreciated.
Hi Rohan, if you can connect other SQL views to your gallery, but not the new one, then the problem is not the connection or the gateway, the problem needs has to be related with the new view you created, perhaps it is related with the data type of some of the columns of your view that are not compatible with power apps and that you need to reformat before displaying the records in the gallery. Check if there is any error message on the gallery when you import the view. there must be something highlighted in red in the Datasource property, or in the same gallery control. Sometimes is just that you need to reformat some of the columns to a Text format or something like that.
Amazing video. Is there a way to write SQL statements when connecting to the database? E.g. Select * from colum 1, 2 etc. Like you can do in Power BI
Hi Nicholas, currently there is no native way to use SQL statements if your are using the SQL Connector, you can only use the existing functions, you can create SQL views (using select statements) and import those SQL views as tables in Power Apps. However, an alternative would be to use Custom connectors.
A custom connector is a wrapper that allows you to connect to any datasource through rest web services.
So, let's say you already have your SQL database, and various store procedures and functions that uses SQL statements (insert, delete, update, select, etc.), you can create web services that uses those store procedures, and configure custom connectors so your power apps can consume those webservices.
This application is of which type ..e.g- canvas/model driven/portal and above screen contains form or gallery...plz reply
Hi, this is a Canvas App, and I use the SQL connector.
Model driven Apps only support Dataverse. The same with Portals.
The benefit of canvas app is the flexibility to use any type of datasource.
In this video I'm using a Table control to show the list of records from the SQL Database. I'm not using Forms or Galleries, however if you would like to see how to build a fully functional canvas app from scratch using galleries and forms, check this: th-cam.com/video/jMtZidgYApM/w-d-xo.html
Pls sir is the SQL serve table on desktop computer then Link to power app?
Hi akokoh, for this example, yes. I have a SQL Server Database on my PC and I use the Datagateway to connect it to Power Apps.
Instead of your PC it could be any server on your network.
When am patch is not submitting the data to SQL table. It is throwing error and error is "Network error when using Patch function . The requested operation is invalid'. Does we need any permission to patch the data into SQL table
Hi, is your SQL Database On-premise? or in the cloud? if it is On-premise you need to configure your Datagateway (You can follow the instructions here: th-cam.com/video/X6wlrjZDq1U/w-d-xo.html). Network error sounds to me more like you power apps cannot connect to your SQL Database for some reason, make sure your datagateway is working fine.
Regarding permissions, you don't need any specific permission, you only need read/write access to your database.
@@PieroMarchenaThank you so much for your response
Sir , Is there any way you can tell me how to create the boxes and create the filter step by step i will be thankful.
Sure, follow this video: th-cam.com/video/qxUm9PlasXs/w-d-xo.html
Hi Piero,
How can we deal with lakh records from SQL server to powerapps?
Hi Varun, for SQL server with large number of records, always use delegable functions only: docs.microsoft.com/en-us/connectors/sql/#power-apps-delegable-functions-and-operations-for-sql-server
On the other hand, leverage the best practices on SQL like indexing, creating views, etc. for better performance.
Also think about using SQL Azure instead of SQL Server is possible. sometimes datagateways can affect your performance: docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance
Hi Piero,
Thanks for your quick response. But, with delegable functions it's taking too much time to load and also not correct data.
It's not possible to write view's because data having multiple branching folders.
SQL server is mandatory.
Please reply to this if you get any idea in future also.
Thanks
@@varunkumar-ue1vc This is a good article you can follow with recommendations to solve issues with SQL Server. Take a look at the "issue types per data source" section.
powerapps.microsoft.com/es-es/blog/considerations-for-optimized-performance-in-power-apps/
Also follow the recommendations here: docs.microsoft.com/en-us/powerapps/maker/canvas-apps/common-performance-issue-resolutions#considerations-specific-to-sql-server-on-premises