In addition to supporting an unparalleled range of physical field types, Arbutus also supports virtual columns. Virtual columns allow the result of any calculation or calculations to be presented as a column in a table. Virtual columns are implemented by Arbutus purely as definitions, stored in the table’s metadata. Virtual columns operate automatically and dynamically, with no procedural code required. There is no physical data actually created, and no pre-processing requirements or delays. Once defined, virtual columns are automatically exposed through the table definition and can be referenced just like any physical column.
As a simple example, if you had an inventory table that contained physical columns specifying the quantity on hand (QTY) and the unit price (PRICE), then you can define a simple virtual column VALUE with the definition QTY*PRICE. As soon as the column is defined you can refer to VALUE as if it were a physical column in the table, as Arbutus does not differentiate between physical and virtual columns. While the above example is very simple, virtual columns can contain the value of any expression, regardless of complexity.
In addition, virtual columns can be multi-valued. For this type of virtual column you may specify any number of tests which will be sequentially evaluated until one is true or the list is exhausted. Each test is paired with a separate value expression. The value of the virtual column is determined by which test (if any) succeeds, on a row-by-row basis, providing comprehensive testing capabilities. Virtual columns may refer to any value or column, including other virtual or related columns. This results in the ability to establish even complex data relationships through virtual columns. Any calculation or relationship inherent in your data can be modeled as a virtual column, allowing Arbutus to not only access the data itself, but also the meaning implicit in that data.
Virtual columns are useful for a variety of applications. To give a few examples, they can:
- Encapsulate business logic calculations
- Expand coded values, such as department codes, into relevant names
- Provide semantically relevant names for various business logic conditions
- Act as automatic triggers to implement specific business logic
- Identify error conditions in the table