As part of the 2023 Wave 1 Release Plan, Microsoft simplified the process of connecting to external data and creating virtual tables from within the Maker portal. This process makes creating these connections very easy, and results can be seen in a manner of minutes. Configuration is now at the fingertips of citizen developers as well.
This new functionality is currently to allow makers to connect to existing data source such as SQL or SharePoint. Additional data sources should be come available at a later stage, but for the time being only connection to SQL Server and Microsoft SharePoint are available from the Maker Portal.
In this post I go over the process of creating this connection to an external SQL Server database. In my previous post from January 10th, I went over this process using a SharePoint table. To start creating a new connection, navigate to your solution in Maker Portal, and click on the new button to add a new table. You will see that there are two available options: Table and Table from external data. We will need to select the Table from external data option to start the New table from external data wizard. The image below shows the selection.
The first thing that we need to do is select the Connection. As previously mentioned, in this Preview feature only the SharePoint and SQL Server connections are available. Once you know which connection you want to use, click on the + Add Connection link to connect to the external source. You will be able to connect using your AD credentials or using a Gateway. For this particular case using the SQL Server Connection, we connected using the Gateway. Once you have established the connection, you will see the credentials showing up in the Select a connection section which a green arrow next to it if the connection was successful. The credentials will show the name of the database and the name of the SQL Server.
After we click Next in the Connection section, we will see in the Data section a list of all the database tables that are available in this SQL database that we can use for the creation of the Virtual Tables. In the image below we have multiple tables. This selected table will be used to create the virtual table in Dataverse and make the connection between the SQL database table and the Dataverse table. Select the table (in this case the Sales Order Header) and click next to configure the table.
We should now see details about the table that we are going to be connecting to. We see the Display Name, Plural Name, Schema Name and Primary field the same as we would when we create a regular table. You will notice that the Primary key is greyed out, and that the schema name for that is externalprimarykey. All the columns from the SQL database table will be displayed in the table with the columns under the Configuration section.
Clicking on the Next button will navigate to the final screen called Review and finish. This will show you the details of the External source and the Dataverse table so that you can make any changes if needed before the creation of the Virtual table. By clicking on the Finish button, the process will start of the creation of the virtual table.
If the process takes too long, you might see an error message. This is a timeout error when the process takes more than 2 minutes. You can likely ignore the error and close the dialog window. Wait a few minutes to make sure the process is complete. Refreshing the solution should now show you the table that you created in Dataverse. The screenshot below shows the error message.
To test this functionality, you can go ahead and create a record in SQL Server. You should be able to see that the record is visible in Dataverse as well. The two images below show you the same record that is created in SQL Server and in Dataverse.
A previous blog post on doing this with a SharePoint virtual table is available at the link below:
https://www.ariclevin.com/powerapps/post/virtual-table-maker-portal-sharepoint/