Hi, thanks for watching this video. You can try this example with our database. The connection information for that is in the description of the video. A common question we have received is why is this feature so important. Previously, you would have to load the table into Dataverse on a periodic basis and the data would be stale. You would also have two copies of your data, one of which is stale. Thanks for watching! - Brian
Excellent walkthrough. Just a quick question before I setup the environment. Are you aware if Powerautomate or plugin can trigger events on these virtually connected tables?
Right now, full support of this feature is with SQL Server, Excel and SharePoint. Others are coming. You can u se Virtual Tables for Custom Connectors likely though.
Hello. I always watch videos well and click like to your UA-cam in Korea. I have a question. How do I insert an image into the PowerApp dataverse? I've searched and watched a lot of UA-cam videos, but I'm struggling because I haven't solved them yet. Please answer
Is the image file going to be a link or a binary file that you upload? You can either use a File Data type to upload the binary. There's also a PCF control you can get from pcf.gallery to show a preview of the image after upload too. Hope that helps! -Brian
Hi Brian - Great video, thank you for sharing. Your instructions work great. Do you have any additional instruction on how to pass virtual tables through solutions into new environments? I tried package up and import from one environment and got the tables in there no problem, but the tables have no data in them and there was an error about a missing connection reference. Any thoughts on what I need to do next?
Hi Brian, thank you for your vieos's, they are a grate help. I'm an "old" CRM4 ->Dynamics2016 administrator and have built complex systems in Dyn2016. Now I'n in my new job and I have to build a system that has aprox 20 tables (in Azure SQL) and a custom web service (also in Azure) to face and collect data from our relatively few customers. I tend to favor the model driven apps, hence the question can the Virtual Connector be used in this case?
For sure.They've simplified the installation of these connectors now too. So you'll just point the connector to your Azure SQL DB or on-prem to do that. For your Web services, you'll need a more traditional virtual connector to OData for those I believe.
Thanks for a great video. What are the licenses implications? Say, for example, that you use SharePoint as underlying data source and create a virtual table and app. Would this scenario be deemed as Premium --> Licences? or would it be treated as Standard hence no need for premium licenses? Thnaks
Correct, if your app uses Dataverse, even through a virtual table, you would need a license. That said, if you're using a canvas app, why not go directly to SharePoint and bypass that.
One "make or break" functionality for me would be the possibility to do advanced find to search and filter i.e. contacts based on external data. Can you use advanced find and query the external tables that are brought in?
Thank you for this walkthrough. Is the virtual connector physically bringing in the data into the Dataverse entity? I think I heard "foreign key" at the end of the video regarding that. If not, then are the data coming over the virtual connector still subject to the size limit of a Dataverse entity (4GB if not mistaken)?
Hi Hristo, no in this case it's not bringing any data over. More linking to the table and storing the primary key from the source system in the child table.
1. Does this works for ON-premises connections for SQL or others? 2. The actual use case is to relate/link off-dataverse tables with datverse tables. Am I getting it correct?
Hi Vishvesh, 1. Yes it works for on-prem datasources. It's just passing the queries through to the underlying connector. 2. One of the largest use cases is to relate to reference data like transactional data without having to duplicate the tables. So for me, linking to a system that's your system of record for customers, students, patients is key. Then Dataverse extends those tables with children tables to those reference tables. - Brian
Hi John. I was surprised by that as well but yes, I didn't have to set permissions. I did have to set the connection permissions though at make.powerapps.com. - Brian
Hi , I have successfully created custom entity via virtual connector . How I can use that custom entity in either in data flow or plugin on events (update, delete or add) . I did try to use simple way as we use normal dataverse table but its not doing anything not even creating error . Can you please suggest me something ? Many Thanks
A few questions, and sorry if I am totally not getting it, but 1) how is this different/better than just using the straight sql server connector? 2) where I think this would come in handy is for power apps portal. Will it work for that? For instance could I use azure API mgt to connect to an external API, the create a custom connector for that API in API mgt, then use this virtual connector functionality to bring that into data verse and expose it from a CRUD perspective within data verse to my portal app (connecting to external APIs from within PowerApps portal is something I have been struggling with). Or am I getting this totally wrong?
Great questions @David. 1. If you're buildinig a dataverse solution, you can't join into a SQL Server table without replicating all the data. For example, you might want to create a Power App Portal and reference some of your existing tables as a lookup source. That can't be done without doublilng your tables. I see this scenario mostly with schools, hospitals and manufactoring sectors where we're having to replicate all the tables in dataverse. 2. Yes, it will work for a PA Portal, which really opens things up. Thanks for the question - Brian
@@PragmaticWorks so this more for portals and data verse solutions than it is for standalone canvas PowerApps, correct? Since canvas apps can directly connect to SQL Tables and read, write, edit. If I am missing something I apologize. Really appreciate all the vids you post on these seldom covered topics btw.
@@Troy_Lambert Yes, a canvas app can have a connection of its own into a SQL data source. This is about having that external data source available as an "entity" in dataverse -- that means you can create lookups on your "real" dataverse tables to records in the remote source.
Thank you for this video. I only stuck having a issue, when you select the 'Results' button at 15:25 I'm not getting returned the tables but a error "An error has occurred.", Is this a know issue or just me overlooking something? Kind Regards, Jaco
How do I connect data verse to on premise sql server? Is there a public ip for the data verse that I need to white list on my on premise network? In essence how do I create that connect from Dataverse which is in the cloud to an on premise network?
Even when I installed Virtual connector , I can see it in my power apps admin centre dynamics 365 but I am unable to see this in my solutions.. what could be the problem ?
Hey, I kept getting errors, but I had several places that looked different from what you showed. For one, after I created the connection, there was no option to "share" it -- the ... menu just has "Switch Account", "Delete", and "Details". Second, when I created the Virtual Connector, I didn't get any of the Tenant Id, Client secret, etc. I had "Name", a lookup called "Connection Reference" (which was the same as in your demo), and a text field called "Dataset Value" that gives no hint as to what it is expecting.
hi, have you ever tried to connect to oracle Fusion, SaaS application, its seem much harder to connect powerapps to SaaS application or specifically oracle
I haven't tried Oracle Fusion but check out any limitations in the docs. It should work with nearly any connector that you can get to work in Power Apps/Power Automate. So if you are able to build basic apps against your Fusion system, you should be golden.
Have you seen any sql data types that have not been supported? Additionally, have you seen any instances where a sql column is not pulled over in the virtual table?
Hi Barrie, I just added a view in my database and I could not see it so it must not have that functionality yet. This is still in preview and there's a lot more to come.
@@PragmaticWorks I just wanted to add that I do agree that this is quite the game changer. Also, for the question of using views, the limitation is likely because primary keys are required for the metadata in the entity. I think a suitable workaround would be to create a table that mirrored a desired view, create the dataverse entity, then replace the table with a view of the same name. Then, "instead of" triggers could be used for updates if desired. Many thanks again for presenting this video and I hope many others see it and find it usefull.
Hi, thanks for watching this video. You can try this example with our database. The connection information for that is in the description of the video.
A common question we have received is why is this feature so important. Previously, you would have to load the table into Dataverse on a periodic basis and the data would be stale. You would also have two copies of your data, one of which is stale. Thanks for watching! - Brian
This is huge! Was just wiring up a connection between power automate, dataverse and SSMS
Excellent walkthrough. Just a quick question before I setup the environment. Are you aware if Powerautomate or plugin can trigger events on these virtually connected tables?
Not at this time I'm afraid.
Hi Brian, thank you for the video. Is it working for Custom Connector? 😅
Right now, full support of this feature is with SQL Server, Excel and SharePoint. Others are coming. You can u se Virtual Tables for Custom Connectors likely though.
Hi Brian, thank you for this video, brilliant as always.
Glad you enjoyed!
Amazing video, so clear and well presented. As always Brian thank you !
Glad you liked it!
hi brian, You blow my mind again. is it possible to limit the connector to read only mode ?
You can do dataverse security roles on top of the table but that said, this will honor any security for any account it's logging in as.
it's awsome Briant thank you ! i have a question. how can i add a column that contain only "Yes" or "No" as an input to my SQL ?
Are you speaking of a bit column or one that's a varchar column that literally says Yes and No? Either one can be done.
@@PragmaticWorks i need to write "actif, inactive"
Hello. I always watch videos well and click like to your UA-cam in Korea.
I have a question. How do I insert an image into the PowerApp dataverse? I've searched and watched a lot of UA-cam videos, but I'm struggling because I haven't solved them yet. Please answer
Is the image file going to be a link or a binary file that you upload? You can either use a File Data type to upload the binary. There's also a PCF control you can get from pcf.gallery to show a preview of the image after upload too. Hope that helps! -Brian
Hi Brian - Great video, thank you for sharing. Your instructions work great. Do you have any additional instruction on how to pass virtual tables through solutions into new environments? I tried package up and import from one environment and got the tables in there no problem, but the tables have no data in them and there was an error about a missing connection reference. Any thoughts on what I need to do next?
I'm insanely excited about this!
Glad to hear it!
Hi Brian, thank you for your vieos's, they are a grate help. I'm an "old" CRM4 ->Dynamics2016 administrator and have built complex systems in Dyn2016. Now I'n in my new job and I have to build a system that has aprox 20 tables (in Azure SQL) and a custom web service (also in Azure) to face and collect data from our relatively few customers. I tend to favor the model driven apps, hence the question can the Virtual Connector be used in this case?
For sure.They've simplified the installation of these connectors now too. So you'll just point the connector to your Azure SQL DB or on-prem to do that. For your Web services, you'll need a more traditional virtual connector to OData for those I believe.
Thanks for a great video. What are the licenses implications? Say, for example, that you use SharePoint as underlying data source and create a virtual table and app. Would this scenario be deemed as Premium --> Licences? or would it be treated as Standard hence no need for premium licenses? Thnaks
Correct, if your app uses Dataverse, even through a virtual table, you would need a license. That said, if you're using a canvas app, why not go directly to SharePoint and bypass that.
This is incredible!!!! Thank you for another great tutorial!
Glad you liked it!
One "make or break" functionality for me would be the possibility to do advanced find to search and filter i.e. contacts based on external data. Can you use advanced find and query the external tables that are brought in?
Thank you for this walkthrough. Is the virtual connector physically bringing in the data into the Dataverse entity? I think I heard "foreign key" at the end of the video regarding that. If not, then are the data coming over the virtual connector still subject to the size limit of a Dataverse entity (4GB if not mistaken)?
Hi Hristo, no in this case it's not bringing any data over. More linking to the table and storing the primary key from the source system in the child table.
@@PragmaticWorks Thanks, that makes it clear!
1. Does this works for ON-premises connections for SQL or others?
2. The actual use case is to relate/link off-dataverse tables with datverse tables. Am I getting it correct?
Hi Vishvesh, 1. Yes it works for on-prem datasources. It's just passing the queries through to the underlying connector. 2. One of the largest use cases is to relate to reference data like transactional data without having to duplicate the tables. So for me, linking to a system that's your system of record for customers, students, patients is key. Then Dataverse extends those tables with children tables to those reference tables. - Brian
Awesome! Thanks a lot!
Glad it helped!
is it possible now to use the choice fields in the virtual entity? How would I go about setting this up
I'm afraid not at this point. It can be a lookup column though.
I didn't see you add the App Permissions in Azure. You created the secret but the App Registration won't work without setting the Permissions...?
Hi John. I was surprised by that as well but yes, I didn't have to set permissions. I did have to set the connection permissions though at make.powerapps.com. - Brian
Hi Brian, does this method consume storage space in Dataverse?
No storage required (other than for the metadata). No data actually lives in DV. :) - Brian
Hi , I have successfully created custom entity via virtual connector . How I can use that custom entity in either in data flow or plugin on events (update, delete or add) . I did try to use simple way as we use normal dataverse table but its not doing anything not even creating error . Can you please suggest me something ? Many Thanks
A few questions, and sorry if I am totally not getting it, but 1) how is this different/better than just using the straight sql server connector? 2) where I think this would come in handy is for power apps portal. Will it work for that? For instance could I use azure API mgt to connect to an external API, the create a custom connector for that API in API mgt, then use this virtual connector functionality to bring that into data verse and expose it from a CRUD perspective within data verse to my portal app (connecting to external APIs from within PowerApps portal is something I have been struggling with). Or am I getting this totally wrong?
Great questions @David. 1. If you're buildinig a dataverse solution, you can't join into a SQL Server table without replicating all the data. For example, you might want to create a Power App Portal and reference some of your existing tables as a lookup source. That can't be done without doublilng your tables. I see this scenario mostly with schools, hospitals and manufactoring sectors where we're having to replicate all the tables in dataverse. 2. Yes, it will work for a PA Portal, which really opens things up. Thanks for the question - Brian
@@PragmaticWorks so this more for portals and data verse solutions than it is for standalone canvas PowerApps, correct? Since canvas apps can directly connect to SQL Tables and read, write, edit.
If I am missing something I apologize. Really appreciate all the vids you post on these seldom covered topics btw.
@@Troy_Lambert Yes, a canvas app can have a connection of its own into a SQL data source. This is about having that external data source available as an "entity" in dataverse -- that means you can create lookups on your "real" dataverse tables to records in the remote source.
Thank you for this video. I only stuck having a issue, when you select the 'Results' button at 15:25 I'm not getting returned the tables but a error "An error has occurred.", Is this a know issue or just me overlooking something?
Kind Regards,
Jaco
What's your data source in your case?
@@PragmaticWorks It is a SQL database on Azure. I have also tried maken a new database, but it resulted in the same error.
@@PragmaticWorks I've found the solution. It had to do with the client secret which was removed by our IT department...
How do I connect data verse to on premise sql server? Is there a public ip for the data verse that I need to white list on my on premise network?
In essence how do I create that connect from Dataverse which is in the cloud to an on premise network?
Hi Nasar, You'll just need to use the On Prem Data Gateway (same service that Power BI uses) to connect to an on-prem data source.
@@PragmaticWorks Thanks
Do the users of Power Apps based on virtual connectors still need account access to the SQL server or just to the dataverse?
Does this work with Oracle? I see the documentation only mentions SQL Server, SharePoint and Excel...
Not yet but it's likely on the roadmap.
is there a record limit?
Even when I installed Virtual connector , I can see it in my power apps admin centre dynamics 365 but I am unable to see this in my solutions.. what could be the problem ?
Hey, I kept getting errors, but I had several places that looked different from what you showed. For one, after I created the connection, there was no option to "share" it -- the ... menu just has "Switch Account", "Delete", and "Details". Second, when I created the Virtual Connector, I didn't get any of the Tenant Id, Client secret, etc. I had "Name", a lookup called "Connection Reference" (which was the same as in your demo), and a text field called "Dataset Value" that gives no hint as to what it is expecting.
After looking at this more closely, I was trying to use this for a dataverse connection, and I see it's only for SQL, SPO, and Excel. :Sadface:
hi, have you ever tried to connect to oracle Fusion, SaaS application, its seem much harder to connect powerapps to SaaS application or specifically oracle
I haven't tried Oracle Fusion but check out any limitations in the docs. It should work with nearly any connector that you can get to work in Power Apps/Power Automate. So if you are able to build basic apps against your Fusion system, you should be golden.
@@PragmaticWorks i hope so ill try this connector and see thank you
Does anyone know when this virtual connector provider will be generally available?
Yes, its been tweaked quite a bit and is easier. When you create a table in dataverse now, you now will select New --> Table --> External Table.
Wonderful. Thank you.
This is a game changer
Glad you enjoyed it!
Have you seen any sql data types that have not been supported?
Additionally, have you seen any instances where a sql column is not pulled over in the virtual table?
This only works if your SQL SERVER Database has GUID or INT types as primary key in the tables (Configured in the table designer).
I am getting job status 'Waiting for event' what is that's mean ?
Will these work offline?
They will not because there's no data being stored in dataverse.
Do you know if this includes views?
Hi Barrie, I just added a view in my database and I could not see it so it must not have that functionality yet. This is still in preview and there's a lot more to come.
@@PragmaticWorks thanks for that. Followup question:. How are changes to the table, such as new columns, handled?
@@bnemetchek As of today (still preview status), you have recreate the dataverse entity by going to the advanced view to create the entity. - Brian
@@PragmaticWorks I just wanted to add that I do agree that this is quite the game changer. Also, for the question of using views, the limitation is likely because primary keys are required for the metadata in the entity. I think a suitable workaround would be to create a table that mirrored a desired view, create the dataverse entity, then replace the table with a view of the same name. Then, "instead of" triggers could be used for updates if desired. Many thanks again for presenting this video and I hope many others see it and find it usefull.
I tried this out. Turns out views also show up and work.
Give us a hint - what specific new feature are you talking about? Don't waste viewers time - most are looking for help on specific topics
Hi Gregg, the feature we're talking about is in the thumbnail and in the description of the video.