A schema is a generic term for the syntax and structure used to capture metadata. In TableWiz, metadata is captured in a datagrid description schema (“schema”) that includes information about the columns and about the overall datagrid itself. The TableWiz schema is stored in a text file in XML format, and so can be viewed or even edited to change the information content.
TableWiz schemas can be created in multiple ways:
- TableWiz can examine a datagrid in an open Excel workbook and extract the metadata directly.
- Create and edit a TableWiz Schema DocTable in an Excel worksheet and define the metadata values. TableWiz can read the DocTable and create the XML schema file
- Create and edit a TableWiz schema in an XML Editor
By far the main use case for TableWiz is #1 above, just point TableWiz at an existing table and click “Go”.
In TableWiz the datagrid metadata is subdivided into “views”. A View is a defined variation of the columns in the datagrid. There are two types of views:
The “DefaultView” is the full description of all the physical attributes of the datagrid being described. The “DefaultView” includes all the necessary information to fully create a high-fidelity copy of the original datagrid. It is called the “DefaultView” because TableWiz uses the “DefaultView” attributes as input to its various functions, unless there is other information provided elsewhere, i.e. the User Views.
Please refer to the figure at the end of this section to see how all these components relate to each other.
A User View is a named view that defines a subset of columns to be used for a particular application. The only mandatory attribute of a column in a User View is the column name, which must match a column name defined in the DefaultView.
Columns are created by TableWiz in the order in which they are defined in the view. Thus the DefaultView contains columns in the exact order in which they were parsed, starting from left-most to the last column on the right.
UserViews can therefore change the order of columns created in a target datagrid by re-ordering the column names in the view. Only the columns in the UserViews will be created in any target datagrid. Any column name not included in the User View will not be created.
UserView column definitions can also override most of the attributes that are defined in the DefaultView. For example, as well as a different order, a UserView may have a different Title or even a different formula. Columns may be defined as visible or hidden, and if hidden the column will be created but not be visible in the target table. Such hidden columns can be unhidden using the standard Excel functions.
The version 1 schema includes a special UserView called the “FieldsView”, which lists all the columns in the DefaultView. The FieldsView is automatically created during schema generation by TableWiz and should not be modified.
The original idea was to fully partition user interaction and underlying metadata, so the users only ever deal with Views. This is a legacy artifact from the original creation of the schema format and is deprecated for future versions.
In the User Interface, or any of the documentation, “FieldsView” refers to the full physical table.
Please refer to the figure below to see how all these components relate to each other.
TableWiz can then use the schema in a number of ways:
- To create a new instance of the datagrid structure (headings, formulas etc)
- Add the datagrid columns to an existing table (future release)
- Create a documentation sheet that lays out the metadata details
- Validate an existing datagrid against a schema (future release)
- Compare two schemas and identify differences (future release)
When creating a new instance of a datagrid, the target datagrid can be either a table or a range, within certain limits. For example although a schema captured from a table can be created as a range, formulas in the existing schema may not work correctly in a named range.
If you use application workbooks with numerous worksheets, TableWiz has a “batch” mode that allows the user to select multiple schemas and create new datagrids in worksheets in one action. If the application workbook has a common theme or worksheet structure, you can select a template worksheet when creating a batch of datagrids, and TableWiz will use that worksheet as the starting point for each new datagrid.
Helper functions include:
- Creating a Table of Contents for all datagrids in the workbook.
- Setting a common folder for loading and saving datagrid schemas
There are multiple methods for changing schemas:
- Edit the schemas in a text editor. The schemas are XML text files and can be directly edited. We’ve been using Liquid Studio Community Edition and Visual Studio Community, but any text editor will do, even notepad. The benefit of using an XML editor is syntax highlighting and code folding.
- Create a “DocTable” (DT) of the schema and then edit the values in the DocTable. The DocTable doesn’t support all attributes of the schema but for some users will be easier to use than an XML editor
- Modify a datagrid and save the updated structure as a new schema