A virtual table combines records from different tables. It consists of a main table and one or several joined tables. A joined table has a common field with a main table. If you change data in the main table or joined tables, these changes are also reflected in the virtual table. You cannot import data to a virtual table – it just takes data from previously created tables.
Let’s suppose that we want to combine data from two tables that were previously imported to Worksheet Systems: NorthwindOrders and NorthwindCustomers, to see records of customers that are related to those orders. To do it, follow these steps:
- In the panel on the right, click New Virtual Table. This will display the virtual table settings window, which you can open later after you save your table.
- Fill in the following data:
- Virtual Table Name: Enter a short but descriptive name of the table. The best option would be to mention the names of tables whose records you are going to combine. For example, CustomersWithOrders.
- Main Table: Select the main table from which you are going to take the records. In our case, it is NorthwindOrders.
- Joined Tables: Click the selection button to open a new window where you will define relationships between fields in the virtual table. You should select a reference field from the main table, a joined table, and a corresponding field from the joined table. In our case, it is CustomerID, NorthwindCUstomers, and again CustomerID, as these are the same fields in both tables. In other words, the CustomerID column in the NorthwindOrders table refers to the CustomerID column in the NorthwindCustomers table, so the relationship between the two tables above is the CustomerID column. To avoid field name duplication, you can select a prefix that the system will put before the field names in the virtual table. When ready, click Save to return to the settings window.
- Next, you should decide which fields you need to display in your virtual table, and which you do not need. To do it, select the unneeded fields in the pane on the right and click the button to move them to the pane on the left. Fields in the pane on the left will not appear in your virtual table. For our example, let’s leave the OrderID and CustomerID fields for the main table and CustomerName, CompanyName, and Country for the joined table.
- Finally, you can apply filters. For example, we want to display the customers only from the United Kingdom. To implement this, click the selection button to open the filter definition window. Specify the field name, operation, and value. In our case it is Country, Equal, and UK. When ready, click Set Filter.
As a result, the settings window will look like this:
- When ready, click Save.
As a result, a link to your table will appear under Data Tables in the panel on the left. Click this link to view and modify your table.
If you change something either in the NorthwindOrders table or in the NorthwindCustomers table, then the resulting virtual table, CustomersWithOrders, will show the same changes. You can also edit the main table fields directly in the virtual table.
To change the name, add more joined tables, or remove fields, click the settings icon in the upper-right corner. This will open the same settings window.