# Using a Virtual Entity to store historical log (sample) Hi, my friends, Today, I will talk about the **Table from external data** *( Virtual Entity).* How to create and configure the virtual table as my sample to store the history of the business process stage. Firstly, let's quickly discuss [**Virtual Entity**](https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-virtual-tables-using-connectors?tabs=sql). In short, it can be understood as a 'virtual data table' created within Dataverse with the capability to seamlessly integrate with external data systems. What's even more surprising is that Virtual Entities possess almost all the functionalities of a Standard Entity, except for Rollup, Auditing, and Workflow capabilities. As of now, Virtual Entity allows us to connect to two main sources: SQL DB and Sharepoint (**Sharepoint list**)**.** Now, let's proceed with trying to create a Virtual Entity on Dataverse. And I will create the Virtual Entity from an external source - **SharePoint.** ## 1. Create Connection Firstly, I need to set up a connection to link to an external data source beforehand. I recommend doing this, although, during the Virtual Entity creation process, everyone can add the connection directly. I will create a new SharePoint connection.

Next, select the **Source** you want to connect to as SharePoint >> select the option '**Connect directly (cloud-service)**' - I choose this option as my SharePoint Site is created in the Cloud. \ Then click **Create** >> enter the user/pass information to establish the SharePoint Site connection.

If successfully created, I'll have a connection like the one shown below.

## 2. Create Connection Reference After establishing the connection, the next step is to create a Connection Reference. *Initially, the connection was successfully created, meaning it has stored the authentication details. Creating a Reference Connection for various environments that point to the same connection eliminates the request for additional authentication.*

However, the primary purpose of using Connection Reference instead of direct Connection is to deploy solutions across different environments without encountering authentication errors. *When deploying solutions across different environments, if the solution contains components utilizing connections such as Flows, Canvas Apps, etc., ensuring authentication for the connection is a prerequisite for preventing errors in these components.* \ *If errors occur, the resolution involves entering each environment and re-authenticating each connection. This process is time-consuming and can sometimes be wrong."* To create a **Reference Connection**, I go back into the Solution:

Then, fill in the necessary information for the Connection Reference and click **Create**.

{% hint style="info" %} *It's advisable to name the Connection Reference the same on each environment. Because, when deploying solutions across different environments, the system relies on the 'Name' field of this Connection Reference for mapping purposes.* {% endhint %} The Connection Reference had been created:

## 3. Sample & Create Virtual Entity First, I prepare a SharePoint List table as shown below:

**The challenge** here is that:\ My clients, using D365 CE and the Power Platform, are facing constraints with Dataverse capacity. They wish to track the history of Lead stage transitions without burdening Dataverse's capacity with a vast number of these records. Due to the substantial quantity of Lead records, tracking records become extensive. As a solution, my team and I proposed using **Virtual Entities** to connect to an Azure SQL DB, storing these tracking records there instead of within Dataverse. This approach effectively reduces the load on capacity. Additionally, leveraging Virtual Entities proves to be swift and convenient, as it's an out-of-the-box solution without requiring excessive coding. To demonstrate this, I utilized a **SharePoint List (as a substitute for SQL DB)** to create columns for recording Lead stage transition history (as shown in the image above). This SharePoint List is connected to a Virtual Entity in Dataverse, named: "**Lead Tracking table".** {% hint style="info" %} To create Lead tracking records, I utilize a **Flow** applied to the **Lead entity's Business Process Flow (BPF)** and **triggered** by the **'Added or Modified'** event, **filtered** by the '**Active Stage'.** However, the **data** will be **generated** on a **SharePoint List.** {% endhint %}

Flow: Creating Item on SharePoint List - Lead tracking

**Let's go back to creating a Virtual Entity.** \ I navigate to **Solution >** Then**, click New > Table > Table from external data.**

Then, select the required integration **Connection**, and I choose SharePoint. Everyone should use **Connection Reference** for this (as image below).

Configure Conection & Reference Connection

When choosing the SharePoint connection, the system will prompt me to select the ***SharePoint Site*** or enter the ***URL of the SharePoint Site***.

Under the '**Data**' section, I select the ***SharePoint List*** that I want to create as a Virtual Entity on Dataverse

Next is the **column mapping for the Virtual Entity**. Initially, the system will automatically map and suggest the Virtual Entity name as well as the column names on Dataverse."

*Many might notice more columns than those captured in the SharePoint List I've shown, as these additional fields are OOB (out-of-the-box) and hidden.* {% hint style="info" %} It would be great if everyone could name columns on the external source using a **clear convention**. This way, the system will suggest table and column names on Dataverse that are also clear. {% endhint %} Finally, click on **Next** > Proceed through the ***Review and Finish*** steps, then click on the **Finish** button to complete the process.

At this point, we wait for the system to create the Virtual Entity. Once the creation is finished, we'll have a 'beautiful' table like this.

## 4. Customize Virtual Entity Once we have the Virtual Entity, the next step is to drag the fields in Forms, Views, and potentially create new Fields on the Virtual Entity to map with columns from the External Source, in this case, the SharePoint List {% hint style="info" %} Here's a tip: to **create columns** on the Virtual Entity, everyone needs to switch to the **Classic interface**. You won't find it on the new UI at make.powerapps.com. :D {% endhint %}

Tips: Create new field on Virtual Entity

I use this hidden functionality to create the Lookup field in Dataverse. Please try it! :tada: ## 5. Checking Once the Virtual Entity is created and the Flow trigger on the BPF Lead Process record is set up, now let's try changing the stage and see the results. **Lead -** I have a Lead record that has gone through 2 stages: Lead > Deal

**SharePoint List -** now tracks 2 records when the Lead transitions stages. \ \&#xNAN;*Note:* I adjusted the display of columns on the SharePoint List to make the data presentation more visually appealing, as shown below.

**Virtual entity** - Lead Tracking

Thank you and hopping help.\ \&#xNAN;**\[NTD]yns.asia**\ ...[invite me a cup.](https://ko-fi.com/ntdyns/?ref=qr\&v=2) :coffee: Thank you. :heart: