Relational spreadsheets refer to the ability to link two spreadsheets. By specifying metadata in a sheet definition, a relationship can be established between two sheets, so that data from a secondary sheet can be accessed via a "_text" dimension reference to the primary sheet. In effect, this transforms a sheet into a shallow 3D system. To create such a relationship, include the name of the secondary sheet between the column headings and data, and include a map row to define the reference columns in the secondary sheet.
For example, in the following sheet, the secondary sheet 'labels' is referenced in the row below the headings, with the mappings indicated in the row below it.
| id | QVERS | QTASK | QP1A1 | QP1A2 | QP1A3 | QP1A4 |
| ##secondary | labels | |||||
| ##map | A1 | A2 | A3 | A4 | ||
| 101 | 1 | 1 | 1 | 1 | 1 | 1 |
| 102 | 1 | 2 | 3 | 4 | 5 | 3 |
| 103 | 1 | 3 | 5 | 5 | 1 | 5 |
| 104 | 1 | 4 | 4 | 1 | 3 | 5 |
| 105 | 1 | 5 | 6 | 2 | 1 | 2 |
| 106 | 1 | 6 | 2 | 3 | 4 | 8 |
| 107 | 1 | 7 | 1 | 3 | 2 | 7 |
| 108 | 1 | 8 | 5 | 7 | 5 | 4 |
The associated secondary sheet with the ID 'labels' has the following contents:
| id | A1 | A2 | A3 | A4 |
| 1 | AMD Athlon II X2 B26 3.2GHz | 512MB | 300GB | $389.99 |
| 2 | AMD Phenom II X4 B97 3.2GHz | 1GB | 750GB | $482.59 |
| 3 | Intel Core i3 i3-2120 3.3GHz | 2GB | 800GB | $625.99 |
| 4 | Intel Core i5 i5-2400 3.1GHz | 3GB | 1TB | $729.89 |
| 5 | Intel Core i7 i7-2600 3.4GHz | 4GB | 2TB | $948.95 |
| 6 | Intel Celeron G530 2.4GHz | 6GB | $1,259.59 | |
| 7 | 8GB | $1,379.49 | ||
| 8 | 10GB | $1,439.89 |
The above arrangement allows the text in the 'labels' sheet to be accessed via reference to the primary sheet:
# Returns value in row 101's QP1A2 cell [* fetch_cell( name=> 'survey_id.primary_sheet_id', row => 101, column => 'QP1A2') *] # Returns value in linked secondary sheet for the same cell: [* fetch_cell( name=> 'survey_id.primary_sheet_id', row => 101, column => 'QP1A2_text', secondary => 1) *]
Comments
0 comments
Please sign in to leave a comment.