In today’s world, data is important for businesses. It helps them make decisions and come up with new ideas. But, as we have more and more different types of data, it becomes hard to manage all of it. This is where Microsoft Power Pages and its feature of virtual tables can help.
Virtual tables in Power Pages are a great tool for managing data. They let businesses connect to data from different places, like SQL databases and SharePoint lists, and use it as if stored locally. This makes Power Pages even more useful because it can work with different data sources.
In this article, we will talk about virtual tables in Power Pages. We will explain what they are, why they are important, and how they work. We will also show you how to create and use virtual tables in Power Pages. This will help you make the most of your data.
So, let’s get started!
Table of Contents:
Virtual Table in Microsoft Power Pages
A virtual table in Power Pages is a special type of table that doesn’t have a physical representation in the Microsoft Dataverse but rather represents a query or view of the external data source. They enable the integration of external data sources by seamlessly representing data as a table without data replication.
Importance of Virtual Tables
Here are some key points highlighting the importance of virtual tables in Power Pages:
Data Integration: Virtual tables allow Power Pages to treat external data as local data, enabling seamless integration of external data sources.
Dynamic Data Representation: Virtual tables represent a query or view of the external data source, which means the data is always up-to-date.
CRUD Operations: Virtual tables allow full create, read, update, and delete (CRUD) operations on the external data as native Dataverse tables.
Flexibility: Virtual tables provide the flexibility to connect to various data sources such as SQL databases, Excel spreadsheets, SharePoint lists, and more.
Efficiency: Virtual tables enable data integration from external data sources by representing that data as tables in Microsoft Dataverse without data replication.
Extensibility: Virtual tables significantly extend the capabilities of Power Pages, enabling it to leverage a wide range of data sources.
Physical Table vs Virtual table
Understanding the difference between physical and virtual tables is crucial in Power Pages. Both serve different purposes and have unique characteristics.
Here’s a comparison to help clarify these differences:
Factor | Physical Table | Virtual Table |
Definition | A physical table is a database object that stores data in rows and columns. | A virtual table in Power Pages is a special type of table that doesn’t have a physical representation in the Microsoft Dataverse but rather represents a query or view of the external data source. |
Data Storage | Physical tables store the actual data. | Virtual tables do not store data themselves. They represent data from one or more physical tables. |
Data Source | Data is stored directly within the table. | Data is pulled from external sources such as SQL databases, Excel spreadsheets, SharePoint lists, and more. |
Data Changes | Changes to the data are made directly in the table. | Changes to the data in the underlying table are reflected in the view. |
Use Case | Used for storing and managing data within the database. | Used for integrating external data sources by seamlessly representing data as a table without data replication. |
Steps to Create a Virtual Table in Power Pages with External Data
STEP 1: Go to Microsoft Power Pages.
STEP 2: Next to Data, click on "+ Table". Select "+ New table from external data".
STEP 3: If available, select an existing connection. Otherwise, click "+ Add connection" to add a SharePoint or SQL Server connection.
Click Next.
STEP 4: Enter the required connection details. For example, if you are connecting to a SharePoint site, enter
SharePoint URL
Select a list from available options
Click Next
STEP 5: In the Data section, choose a list from your SharePoint site.
Click Next to proceed.
STEP 6: In the Configuration section, update the details of the table column before it is created in Dataverse. You can set the
Display Name
Plural Name
Schema name
Primary field
Primary key
Click Next.
STEP 7: Finally, review all the details you have entered. If everything looks correct, click "Finish" to create your virtual table.
After you click Finish, you will see the data in your new virtual table. You can view this data in different Views and Forms within Power Pages.
Table Data
In Power Pages, table data refers to the structured information stored in rows and columns. Each row represents a record, and each column represents a field of that record.
For example, in a “Customers” table, each row could represent a different customer, and columns could include fields like “Customer ID”, “Name”, “Email”, etc.
Views
A view is a virtual table based on the result-set of an SQL statement. In Power Pages, views are used to present the data in a more organized and meaningful way to the end users. They can be used to hide the complexity of data, restrict access to data, or simply to present a different perspective of data.
For example, you might have a view that only shows customers from a certain region, or a view that calculates the total sales for each product.
Forms
Forms in Power Pages are used to create, read, update, and delete (CRUD) data in a table. They provide a user-friendly interface for interacting with table data. A form contains fields that correspond to the columns in a table.
Users can fill out these fields to add a new record to the table, or they can use the form to edit or delete existing records. For example, you might have a “New Customer” form that allows users to enter information about a new customer, which is then added to the “Customers” table.
Edit Virtual Table in Power Apps
There are several scenarios where you might need to edit a virtual table in Power Pages:
Data Source Changes: If the structure or schema of your external data source changes, you may need to edit the virtual table to reflect these changes. For example, if a new column is added to your SQL Server table or SharePoint list, you would need to add this column to your virtual table as well.
Business Requirements Change: If your business requirements change, you may need to edit the virtual table to meet these new requirements. For example, you might need to display additional data fields, or you might need to change the way data is formatted or calculated.
Performance Optimization: If you’re experiencing performance issues, you might need to edit the virtual table to optimize data retrieval. For example, you might need to filter or index the data in a certain way to improve performance.
Security and Access Control: If you need to change who can access the data in the virtual table, you might need to edit its settings. For example, you might need to restrict access to certain data fields based on user roles or permissions.
Data Validation: If you need to enforce data integrity or business rules, you might need to edit the virtual table. For example, you could add validation rules or triggers to ensure the data in the virtual table meets certain criteria.
Click "Edit table properties" to edit the virtual table in Microsoft Power pages:
Real-World Use Case
Below are the real-world use cases of virtual tables in Microsoft Power Pages:
Healthcare industry: For instance, a customer needed to provide “Good Faith Estimates” in Healthcare, but their EMR system did not have this functionality. By creating a virtual table in Power Pages, they extend the tables in their separate system that provides critical information.
Business-to-business (B2B) portals: Companies can build B2B portals using Power Pages that serve their customers and vendors, and external users can have anonymous and authenticated access to data stored in Business Central online.
Conclusion
Virtual tables in Power Pages have revolutionized the way we handle and integrate data from various sources. They bridge the gap between local and external data, providing a seamless and efficient way to manage diverse data sets. Whether you’re dealing with SQL databases or SharePoint lists, virtual tables make it possible to treat external data as if it were local, extending the capabilities of Power Pages.
The ability to create, read, update, and delete data in virtual tables as if they were native Dataverse tables is a game-changer. It opens up a world of possibilities for creating dynamic, data-driven web pages.