From Meta

This function creates Excel tabulated data structure using metadata captured in a TableWiz schema.

“From Meta” means “From Metadata” or the saved metadata schema file.

Key Steps

To capture a schema from an existing datagrid, follow the steps below.

Step #

Step Detail

1

Click on the “From Meta” button in the TableWiz Menu bar.

2

A parameters form is activated (See Figure x)

3

Select a schema file to use as the source of metadata for the new datagrid. Click on the load” button to open a File Open dialog box. Click on the “B” button to toggle between single schema mode and batch mode. In Batch Mode all the datagrid parameters are sourced from the schema files.

4

Fill in the parameters and/or accept the defaults generated by TableWiz based on the selected schema. Refer to the Parameter Description Table below.

5

When ready to create the datagrid(s) from the loaded schema, click on the “Go” Button

6

The function will complete.

7

If you want to retry the function, go to step 3

8

Else, click in the X button to close the parameter form

Table 5: From Meta Key Steps

Key Concepts

There are two important concepts to understand in order to use the “From Meta” function productively:

  1. Single vs Batch Mode
  2. Templates

These concepts are explained in the sections below.

Single vs Batch Mode

In “Single Mode” Tablewiz creates one new output datagrid at a time, based on your schema selection and the other parameters you set in the form.

In “Batch Mode” there are two differences:

  1. You select multiple schemas as input, and
  2. Tablewiz uses the details in the schemas to mostly override the settings in the parameters form.

Templates

There are two use cases for Template. the original one I wanted and a new one implemented by Andrey

Use Case 1: Template for Single “From Meta” function

This is when the user wants to create an application-specific result workbook, e.g. the Project Workbook with a one-off creation of a set of datagrids in a workbook according to the template

  • the user can select a worksheet / workbook combination to be used as the template for new datagrids created from schema. this can be in either single create or “batch” mode.
  • These parameters do not persist between executions of the “From Meta” function

If the template mode is activated, the parameters will set a template workbook/worksheet to use as the template source.

This is how Templates work in a one-off “From Meta” function:

the Target Address parameters are used in template mode. Note the template worksheet name always overrides the Target Worksheet settings

There are two pathways, depending on whether “Target Workbook” is set to “New” or  an existing workbook.

Path 1: Target Workbook is NEW

  1. Create a copy of the template workbook in full (all worksheets)
  2. for each schema selected as input to the “From Meta” Function, repeat the following steps
  3. make a copy of the worksheet selected in “Template Worksheet”
  4. Rename the new worksheet as per the view attribute “TargetWorksheetName” – if TargetWorksheetName is not active, create a new worksheet name as in “<template worksheet name>  & “(1) ” – like Excel default worksheet name on create new or copy
  5. Create the target datagrid according to the selected view and the schema attributes

Path 2: Target Workbook is selected

  1. do not create a new workbook, use the target workbook
  2. steps are performed as per step 2 above.
Use Case 2: Template for all “From Meta” functions

A user can select a workbook/worksheet to be used as the default for all future “From Schema” execution

This is useful when the user wants a common look and feel to all worksheets created by “From Schema” – e.g. a consultancy or service provider wants branding or fonts etc to be consistent at all times.

Note: If Use Case 2 is set when use case 1 is executed, the workbook/worksheet selected for the “one off” overrides the global default.

Refer to the section on “Settings” for more information.

Parameters

The following dialog / form appears, containing the parameters required to create a new datagrid from metadata schema file or files.

Figure 13: From Meta Parameters Form

The fields in the Parameters Form are described in more detail in the table below:

Section

Property Name

Description

Data Type

Input Parameters

 

These parameters define the schema that will be used to create the table or range output

 
 

Batch Mode (“B”)

Click on this button to toggle the Batch Mode on or off. In Batch mode, multiple schemas are selected, and their contents used to determine the target locations.

 
 

LOAD Schema

click on this button to load the source schema

 
 

Source schema name

Shows the name of the schema that was loaded from the selected schema file.

 
 

Select a View

Schemas can create multiple views, either captured automatically by TableWiz or edited manually.  Refer to the section below on editing schemas.

 

Template Settings

Use a template workbook

You can select a worksheet in the target workbook or another workbook completely to be the template for new worksheets created in the “From Meta” function.

The default for this parameter is FALSE (unchecked).

If you check this parameter, additional parameter fields are enabled

Checkbox

 

Template Workbook

If you want to use a template worksheet for the “From Meta” function, select an open workbook in this field.

If the Template Workbook is not open, please go to the Excel “File Open” function and open the Template Workbook – then click on the “Refresh” button to the right. to refresh the workbooks and worksheet lists.

 
 

Template Worksheet

Select the worksheet in the Template Workbook that the “From Meta” function will use as the starting point for any new worksheet created in the “From Meta” function.

 

Result Settings

 

These parameters control the layout of the new table/range and select the target Excel container in which the new table/range is created

 

Result Settings – Result Content

 

These parameters control the layout of the new table/range

 
 

Table/Range Selector

Select whether the target will be created as a range or as an Excel Table.

 
 

Add Border around range

with Excel Tables, formatting is created by Excel, but with Range target Tablewiz can create a simple border around the range.  If format metadata was captured and stored in the schema, the schema metadata will override this setting

Checkbox

 

# Rows

Defines the number of table rows to create. If table size metadata was captured and stored in the schema, the schema metadata will override this setting

Integer

 

Add dummy data

Leave unticked if the result should just be the empty table structure.  Check this if you want dummy data to be created in the result table or range

Checkbox

Result Settings – Target Location

 

These parameters select the target Excel container in which the new table/range is created

 
 

Target Workbook

if you want to create a new Workbook, select “New” in the drop-down selector.  Otherwise select an open workbook in which you want to create this new table.

 
 

Target Worksheet

if you want to create a new Workbook, select “New” in the drop-down selector (default).  Otherwise select one of the existing worksheets in the selected workbook in which you want to create this new table.

 
 

Target Address

the source address is captured by TableWiz when the schema is created.  This setting will be used to populate these settings in the parameter form, but you can modify this by editing either the target address or the row/column numbers.  it’s entirely your preference which you edit as changes in one are reflected in the other.

 

Table 6: From Meta Parameters Description Table

If the schema includes any User Views

Figure 14: View Selector Drop-down control – From Meta Parameters Form

Figure 15: Target Workbook Drop-down control and Refresh button – From Meta Parameters Form

Figure 16: Target Workbook Drop-down control and Refresh button – From Meta Parameters Form

Figure 17: Target Worksheet name field with Sheet Name length error

Figure 18: Target Worksheet name field after clicking “Truncate” – From Meta Parameters Form

Figure 19: Target Worksheet name field after manual edit – From Meta Parameters Form

Results

Single Schema Mode

The result of the “From Meta” is a new or modified datagrid in the target location.

Batch Mode

When in “batch” mode, multiple new datagrids are created, using attributes contained in the schema file, including:

  1. Target worksheet
  2. Whether a table or range is created
  3. Target table name if the source was a table.
  4. Top/left Row/Column address

How TableWiz uses Templates

A user can select any worksheet in any workbook as the source of the template for creating datagrid from stored metadata.

The target of a templated “From Meta” operation is always a new workbook, which is a copy of the selected template workbook in its entirety, so the “target location” controls are hidden when the checkbox “Use Template Workbook” is checked.

The refresh button has the same function as the Refresh button in the “target location” controls: it refreshes the list of open workbooks that can be selected as the template workbook. When a workbook is selected, the list of worksheets in that workbook is populated into the target worksheet.

When TableWiz in creates the result datagrid(s), it uses the template worksheet and makes a copy of it (this then becomes the target worksheet). TableWiz creates the datagrid in the new target worksheet.

In “Batch” mode, this process is repeated for all the selected schemas. For the 2nd schema, TableWiz makes a second copy of the template worksheet (thee next target worksheet) and creates the datagrid; for the 3rd schema it makes a 3rd copy of the template worksheet (the 3rd target worksheet) and creates another datagrid from the 3rd schema, and so on for all schemas.

This is exactly like the non-templated “Batch mode”, except we are supplying a source worksheet (the template) for the “create table” function, instead of just creating a new worksheet (or creating the table on an existing worksheet). This is the third case, where the worksheet already exists as a template, but you make a new copy of it before creating the table.

In template mode, the schema values are used for target worksheet name, target address etc exactly as they are in the current batch mode.

Settings

At this time, the only setting is the default schema storage folder.

Set schema default folder

Once set the From Table will automatically save the captured schemas in this folder. From Meta function will open the file selector in this folder by default.

Figure 20: Set schema default folder