Using virtual tables

Virtual tables combine data from more than one table or filter data that resides in a single table. Because it can only be used in read-only situations, a virtual table is ideal when creating a report or a scrolling window.

At least one and at most sixteen member tables can be contained within a virtual table. When a virtual table contains only one member table, column filtering, or excluding data from the member table’s record, occurs. If more than one standard table belongs to a virtual table, the virtual table combines data from member tables to create a record from multiple standard tables.

Scripting

Using virtual tables has several scripting advantages. Most importantly, the use of virtual tables reduces the code needed to access data stored in multiple tables. As an example, the following sanScript script links two tables together without using a virtual table:

set SHOE_ID of table SHOES to ShoeID;
get table SHOES;
if err() = OKAY then
	set SHOE_ID of table MANUFACTURER to ShoeID of table SHOES;
	set MAN_ID of table MANUFACTURER to manufacturerID of table SHOES;
	get table MANUFACTURER;
end if;

As data is needed from more tables, the script grows. By contrast, retrieving the data using virtual tables decreases the code to two lines:

set MAN_ID of table MANUFACTURERS_OF_SHOES to manufacturerID;
get table MANUFACTURERS_OF_SHOES;

This simplification of script allows for less code maintenance and quicker retrieval of data, improving the performance of an application.

When scripting for virtual tables, the commands set, get, fill, and clear are supported.

You can only read from the virtual table. If you try to use a statement like “change” that writes to a table, you’ll get a compile error.

Scrolling Windows

Because virtual tables are read-only, a scrolling window that accesses a virtual table must be browse-only. An open table error occurs at runtime if the scrolling window WindowType property is set to Editable or AddsAllowed.

Reports

Reports frequently need to combine data from multiple tables. While table relationships can be used for this purpose, virtual tables provide not only a performance optimization but also a simpler method to combine data.

Virtual tables do have limits. If you need to combine more than sixteen tables for a report, it is possible to create table relationships between a standard table and a virtual table with no noticeable decrease in performance; however, a virtual table cannot be a member table of another virtual table.

If the primary table does not exist, an open table error will occur when the virtual table is accessed.

Documentation Feedback