I think you understood the core concept correctly. Addressing your statements and questions first, before showing a more concrete example on how this would look in my case.
Yes, I am trying to create one (or possibly more) “master” table structure, or “template” as I called it, that can be replicated to form a new table, to then be used as is, or modified by adding extra columns.
The structure should be stable, once one has defined the columns that should be appearing in the newly constructed tables. But it does not need to be required. (As the templates are just copied when needed, their result is completely detached from them from this point on, no dynamic connection)
The created tables become part of the Grist document, defining (e.g., but at this point mainly) dimension tables to be used in Reference columns and as master data. (Furthermore, their content, once populated, will be mirrored to our internal RDB, but we do not need to be concerned about this here, to keep things simple).
The Owner or creator of the document is creating the tables from the template.
For Users of the document it does not matter how the tables were created, and the template may even be removed once the document structure has been finalized.
N.b.: This is all concerning mainly the development process of the Grist Document and does not really concern Viewers or Contributors of the document, once it is published. It is merely thought of as a tool to speed up the development process and foster standardized naming of columns in tables serving a similar purpose.
I will use a different example than yours, that is closer to the real application here. Several dimension tables will share common field names, their content is well defined and identical for all tables.
key | name | label | description | comment | Duplicate_in_key | Duplicate_in_name | Created_at | Created_by | Last_updated_at | Last_updated_by
Bold fields describe the data of the table, to be filled later, italic fields are calculated fields (referencing other fields in this table). The logic of the table is described as in the code block below, taken from Code View.
The Duplicate_in_key column is intended for use with rules lateron to force unique values in the ‘key’ column. (The key will be used as the business key in the RDB outside Grist, just FYI why we want unique values).
This template is intended to build audited dimension tables, where a business key (key) is specified, with a user friendly short name (name), as well as a more verbose description and possibly a comment. Additional columns may be added as desired.
The label column holds the values to be displayed in the drop down when the respective table is used in a reference column, defined to create human readable unique labels derived from the short name and, if needed as a tie breaker, also from the key.
When adding reference columns, we know we always want to select the label column, irrespective of which table we are referring to, as long as it has been created from this template.
@grist.UserTable
class Dim_table_template:
key = grist.Text()
name = grist.Text()
description = grist.Text()
comment = grist.Text()
@grist.formulaType(grist.Bool())
def Duplicate_in_key(rec, table):
return rec.key != "" and rec.key is not None and len(Dim_table_template.lookupRecords(key=rec.key)) > 1
@grist.formulaType(grist.Bool())
def Duplicate_in_name(rec, table):
return rec.name != "" and rec.name is not None and len(Dim_table_template.lookupRecords(name=rec.name)) > 1
@grist.formulaType(grist.Text())
def label(rec, table):
if rec.Duplicate_in_name:
return f'{rec.name} ({rec.key})'
else:
return rec.name
def _default_Created_at(rec, table, value, user):
return NOW()
Created_at = grist.DateTime('Atlantic/Reykjavik')
def _default_Created_by(rec, table, value, user):
return user.Name
Created_by = grist.Text()
def _default_Last_updated_at(rec, table, value, user):
return NOW()
Last_updated_at = grist.DateTime('Atlantic/Reykjavik')
def _default_Last_updated_by(rec, table, value, user):
return user.Name
Last_updated_by = grist.Text()
As it will get a bit wide to specify all columns when we create our tables, I will abbreviate the set of these set of base columns as (BCs) while spelling out any additional columns.
Let’s create a lab inventory system.
Table SDSPhrase: (Safety information):
(BCs)
Table Chemical:
(BCs) | RefList(SDSPhrase) : SDS_Facts | Number : MolWeight | Text : Appearance | Choice : Phase
Table SupplierContact:
(BCs) | Text : Phone
Table Supplier:
(BCs) | Text : Country | Text : Address | RefList(SupplierContact) : Contacts | Choice : Priority
… and so on and so on. There can be tenths of such tables in the end if one designs the hierarchical structure very detailed and granular.
By setting the template up in a smart way, one can avoid “writing” a lot of boilerplate for each of these tables. Say we want to distinguish between Jane Doe in “SuperChem GmbH” and Jane Doe in “ChemSupply Inc.”, using their email as the “key”, it will automatically be added to the “label”, in parentheses after their names. If one would like to rather have the Company Name displayed there, it is one line (or rather one word) to be changed in the formula definition of the label field in the SupplierContact table.
I can describe in more detail or give more examples, but I hope this suffices to get the idea what I am talking about, in a realistic application setting.
All the best and thanks for you time for this conversation, and I am looking forward to hear your thoughts on this.