Virtual Tables

[brian rich]

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)

Virtual Tables.zip

[bruceR]

Nice. Though I see that as implemented, it does not allow return delimited values in the fields. This is where some variations can be useful, such as the repeating variable method using $$array[N]; or a dictionary method that encodes returns.

[bruceR]

Something like this.

virtual tablesREV.fp7.zip

[brian rich]

Thanks Bruce.

I hadn't run into the need to encode returns into my dictionaries yet, but your modifications to the two custom functions seem to solve the problem.

Brian Rich

Helpful Systems

Warwickshire

UK

[David Jondreau]

Or you could do away with tables all together!

You'd need one table and one field to store your dictionary on file close and a repeating global to build a user interface around. Everything else could be run off of scripts.

NoSQL for FileMaker!

[beverly]

You could get into "Entity Attribute Values" (like a lot of Word Press data storage).

http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

But it's NOT SQL and very difficult to extract sometimes.

I'd prefer temporary tables and SQL calls to get them.

Beverly