This is huge! Was just wiring up a connection between power automate, dataverse and SSMS

This is incredible!!!! Thank you for another great tutorial!

@PragmaticWorks

2 жыл бұрын

Glad you liked it!

Hi Brian, thank you for this video, brilliant as always.

@PragmaticWorks

2 жыл бұрын

Glad you enjoyed!

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

Amazing video, so clear and well presented. As always Brian thank you !

@PragmaticWorks

Жыл бұрын

Glad you liked it!

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!

@PragmaticWorks

2 жыл бұрын

Glad to hear it!

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?

@PragmaticWorks

2 жыл бұрын

Not at this time I'm afraid.

Awesome! Thanks a lot!

@PragmaticWorks

2 жыл бұрын

Glad it helped!

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)?

@PragmaticWorks

2 жыл бұрын

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.

@cchristoff

2 жыл бұрын

@@PragmaticWorks Thanks, that makes it clear!

hi brian, You blow my mind again. is it possible to limit the connector to read only mode ?

@PragmaticWorks

Жыл бұрын

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.

Do the users of Power Apps based on virtual connectors still need account access to the SQL server or just to the dataverse?

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

@PragmaticWorks

2 жыл бұрын

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.

Hi Brian, thank you for the video. Is it working for Custom Connector? 😅

@PragmaticWorks

2 жыл бұрын

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.

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?

@PragmaticWorks

2 жыл бұрын

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 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?

@PragmaticWorks

2 жыл бұрын

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.

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 ?

@PragmaticWorks

2 жыл бұрын

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.

@ZenMel0dies

2 жыл бұрын

@@PragmaticWorks i need to write "actif, inactive"

Wonderful. Thank you.

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 ?

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

is it possible now to use the choice fields in the virtual entity? How would I go about setting this up

@PragmaticWorks

2 жыл бұрын

I'm afraid not at this point. It can be a lookup column though.

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.

@andyjabez9780

2 жыл бұрын

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:

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?

@PragmaticWorks

2 жыл бұрын

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

@cachememory2236

2 жыл бұрын

@@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.

@andyjabez9780

2 жыл бұрын

@@cachememory2236 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

@PragmaticWorks

2 жыл бұрын

What's your data source in your case?

@jacoderooij5308

2 жыл бұрын

@@PragmaticWorks It is a SQL database on Azure. I have also tried maken a new database, but it resulted in the same error.

@jacoderooij5308

2 жыл бұрын

@@PragmaticWorks I've found the solution. It had to do with the client secret which was removed by our IT department...

Hello. I always watch videos well and click like to your KZread in Korea. I have a question. How do I insert an image into the PowerApp dataverse? I've searched and watched a lot of KZread videos, but I'm struggling because I haven't solved them yet. Please answer

@PragmaticWorks

Жыл бұрын

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, does this method consume storage space in Dataverse?

@PragmaticWorks

2 жыл бұрын

No storage required (other than for the metadata). No data actually lives in DV. :) - Brian

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...?

@PragmaticWorks

2 жыл бұрын

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, have you ever tried to connect to oracle Fusion, SaaS application, its seem much harder to connect powerapps to SaaS application or specifically oracle

@PragmaticWorks

2 жыл бұрын

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.

@marounsader318

2 жыл бұрын

@@PragmaticWorks i hope so ill try this connector and see thank you

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?

@PragmaticWorks

2 жыл бұрын

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.

@nasarazam

2 жыл бұрын

@@PragmaticWorks Thanks

I am getting job status 'Waiting for event' what is that's mean ?

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?

Does this work with Oracle? I see the documentation only mentions SQL Server, SharePoint and Excel...

@PragmaticWorks

11 ай бұрын

Not yet but it's likely on the roadmap.

This is a game changer

@PragmaticWorks

2 жыл бұрын

Glad you enjoyed it!

Does anyone know when this virtual connector provider will be generally available?

@PragmaticWorks

Жыл бұрын

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.

This only works if your SQL SERVER Database has GUID or INT types as primary key in the tables (Configured in the table designer).

Do you know if this includes views?

@PragmaticWorks

2 жыл бұрын

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.

@bnemetchek

2 жыл бұрын

@@PragmaticWorks thanks for that. Followup question:. How are changes to the table, such as new columns, handled?

@PragmaticWorks

2 жыл бұрын

@@bnemetchek As of today (still preview status), you have recreate the dataverse entity by going to the advanced view to create the entity. - Brian

@bnemetchek

2 жыл бұрын

@@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.

@bnemetchek

2 жыл бұрын

I tried this out. Turns out views also show up and work.

Will these work offline?

@PragmaticWorks

Жыл бұрын

They will not because there's no data being stored in dataverse.

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

@PragmaticWorks

2 жыл бұрын

Hi Gregg, the feature we're talking about is in the thumbnail and in the description of the video.