Virtual Tables

I've been fascinated by Bruce Robertson's Virtual List technique, but became frustrated by the difficulty of formatting the Virtual List, and wished for a way of formatting the results in the virtual list as though they were fields.

I realised that the idea of using a global list of values could be replaced by a global list of data dictionaries.

A data dictionary is a concatenated list of name|value pairs which can store as many namevalue pairs as you need, so you can put as many data items as you want in the dictionary. In effect, you can store one 'record' per line in the list of dictionary entries. So my virtual list of Dictionaries (with 3 entries in it and in a simplified view) looks like this:

name=joe smith | dob=20/4/1946 | children=3
name=matilda menace | dob=19/5/1988 | children=1
name=jamie cullen | dob=11/5/75 | children=2

I use two of Six Fried Rice's custom functions to manage the data dictionaries.  PassParameter() is used to build the data dictionary, dictGet() is used to retrieve the value associated with a name in a dictionary

The virtual table has a similar constuction to a virtual list. Each record in the Virtual table has a unique record number starting a 1and containing as many records as you want. It also contains the usual unstored calculation field to extract the relevant list entry from the global list - in my list this is now named Dictionary,.  However, in my virtual table, I add an unstored calculation field for each namevalue pair in the data dictionary which extracts the relevant data from the Dictionary field. For example

virtualTable:: name = dictGet(Dictionary;"name")

This now gives me a 'real' data field to play with. All I need to do is to define a virtual table field for each field in the data dictionary to get a 'real' field in the table I can manage in the usual way,

I've included a simple demo to illustrate the technique, which takes real data in one table and presents it as virtual data in a second table.  This is a trivial example which makes it straightforward to build the virtual table, but the virtual table can be built with a looping script technique to add a data dictionary for any set of data you want. I've not yet run into a local storage issue, but the technique allows handling of several virtual tables simutaneously without any noticable performance issues

In addition, you can set up several sets of fields relating to entirely independant data dictionaries and present them on appropriate layouts,  Any fields which don't have a valid name in the current data dictionary remain null. Of course, since you now have an unstored field in your virtual table, you can construct relationships with other tables to include additional information on the layouts.

The data dictionary can be built in whatever way you wish from one or more tables,  It exists only locally on the users machine so there are no multi-user issues unless you need to store information from the virtual table into a real one.

The one thing I haven't managed to do yet is to present a virtual table which automatically has just the number of records to correspond with the number of data dictionaries contained in the global variable. Perhaps someone can suggest a way of doing this

You can find the two custom functions at http://sixfriedrice....ripts-advanced/

Hope this is useful to others

Brian Rich
Helpful Systems
Warwickshire
UK

Demo file below (sorry if UK date formats cause grief)