June | 2023 | D365 Demystified

Create SQL Connection Reference

First, let’s create an SQL Connection reference in Power Platform –

Go to Connections, create a new Connection.

Now, you can select the authentication type as SQL Server Authentication. You can choose based on how your setup is, but if you want to quickly test this feature, you can follow this process.

Then, you need to fill in the Server Name and Database Name.
You can get the Server Name from here in the Azure Portal. You’ll find a Server name in the SQL Server details.

And Database Name under the Server itself as

And fill out the information as below.

Further, if you scroll, you’ll need Server Admin name which will be on the Server itself.

And fill the same in the fields below in the connection dialog box. Finally, click Create.

Connection is now created.

Create Table

Here’s how you can create a virtual Table in Power Apps Maker (https://make.powerapps.com/) –

Once you are in Power Apps Maker, go to Tables and you can drop down from Create Table menu.

You’ll see option to Create by connecting to external source.

Now, you have 2 options at this point of writing this blog. One is SQL and other is SharePoint. We’ll select SQL for this example. Both also use the connection you are logged in.
You can select the connection we created in the steps above.

In the next step, you’ll see the Tables from SQL. In this example, our table is student.

When you click Next, you’ll see the columns from SQL

Once everything looks good, you can then click Next. Finally, you’ll see he summary of the Dataverse virtual table to be created.

And finally, the Table will read the data from SQL and display here. The data will not be synced back to SQL.

In case you also want to know how to convert a SharePoint list to Dataverse table, you can refer this post – Create Dataverse Virtual Table from SharePoint List | Power Platform

Hope this helps!

Thank you!

Now, you can create a Dataverse virtual table by referencing a SharePoint List. This is an easy way to bring in schema from your SharePoint List in use and make it into a Dataverse Table.

Good news is that this also syncs back to the SharePoint List! Let’s see how.

SharePoint List

Let’s say you have a SharePoint List which you want to convert into a Dataverse Table in your Power Platform –

Let’s do this by creating using a Virtual Table which now connects to SharePoint and SQL. Please note that at the time of writing this post, connecting to external data is still in Preview.

Create Table

Here’s how you can create a virtual Table in Power Apps Maker (https://make.powerapps.com) –

Once you are in Power Apps Maker, go to Tables and you can drop down from Create Table menu.
You’ll see and option to Create by connecting to external source.
Now, you have 2 options at this point of writing this blog. One is SQL and other is SharePoint. We’ll select SharePoint for this example. Both also use the connection you are logged in.
Once you select SharePoint, you’ll either be asked to select one of the recently used Sites or if you know the URL of the Site in which your list resides, you can choose the same.
Refer below that I’m choosing up to the name of the Sub-site in question.
So, you can either select a recently used Site or just paste the URL of the Site which has your List in it.

OR
Once you are in the desired Site, you’ll see the List under that Site.
Once I select the List, I’ll be asked if I need to change any schema name from the ones identified by the wizard.
Or if everything looks OK, you can simply click on Next. You’ll be given a summary of what Table will be created from your SharePoint list.
It takes a few moments to create this Virtual Table for you with the data from the SharePoint List.
Once ready, it’ll appear as below with the data and you can start adding your data as well.
Also, the data you add from the Virtual Table is also sent back to the SharePoint List.
This entity / table will be listed as any other Table in the Power Apps Maker with the Virtual type.