Table Column
New data storage tables are defined from the NEW button on the My Data area of the home or Nodal Display section of the Metamation screen. Once the basic properties of the table have been defined, you can then define the columns (fields or content) which can be stored in the table.
As with any other form of structured database (Access, Oracle, SQL Server etc), columns are used to define the information that can be stored. Within Metamation, the types of information required has already been defined, you simply need to utilise the fields and provide the missing information such as labels, lengths, formats and validation.
The column definition screen is divided into two sections. On the left, existing columns within the defined table are listed with basic information. Selecting any existing column with the mouse pointer displays the columns properties on the right. New columns can be added by using the New button at the base of the screen.
On the right of the existing column list, the column properties panel shows the properties of new or existing columns of information. When adding or amending columns within the table, the columns are defined through the following prompts:
Column Settings
These prompts are used to define the column, how the data is stored, and how it is labelled on screen:
Column Name: The name is used to describe the use of the column. This is a short description.
Data Store: The data store indicates the real storage field where the information will be held in the table. Many storage fields of different data types are available.
Entry Label: The entry label is the prompt which appears on entry and summary screen displays. This will be set from the column name, but can be amended as you see fit.
Data Source: This entry is only available where the table properties have been defined to pull data from an external data source
Meta Data Flow
This series of prompts are used to define Metric controls, used to move data around (such as pull or push information to other tables/levels), graph data and set useable fields:
Meta Name
The name is used to describe the metric which will be stored. This is a short description and must be unique within the Metamation database (you will be warned if your selected title has already been used).
Meta Description: A short textual description of the defined metric. This is a numerical entry and is used to group different metrics in the same table together for the purposes of graphing.
Meta Group: Placing different metrics in the same numbered group will make the items appear on the same graph. Putting them in separate groups will result in separate graphs.
Meta Spread: This is used to indicate if the metric should spread up, down, no spread or both up and down. Spreading the data is the flow. As an example, budgets normally flow down the defined tree, invoice amounts flow up (so that at the top level, you get the grand total of all invoices generated).
Up Link: This is the defined Meta area where up flowing information will be sent. The drop down list of up links shows other defined Meta fields. Example of upflow may be invoice line items flowing UP to a INVTOT (Invoice total) in a separate invoice header table.
Up ID: Where the data flow is upwards, this is the ID column (in the defined table) to use in the upward table (e.g. invoice number)
Down Link: This is the defined Meta area where down flowing information will be sent. The drop down list of up links shows other defined Meta fields. Example of down flows may be budget figures flowing down to a budget record in a separate table.
Down ID: Where the data flow is downwards, this is the ID column (in the defined table) to use in the downward table (e.g. department number)
Show type: For the defined metric, this defines the type of graph to be displayed. The available graphs can be selected from the drop down list. This will effect all other metrics in the same group in the defined table.
Validation Settings: These prompts are used to define the format, length, and validation of the defined column: This is the maximum length of the entry you will allow. This will default depending on the storage area defined above, but you can change this to be a smaller number if you require. If you need to hold more information, change the storage location to be a larger field.
Max Length:
Where left blank (--Select--) the data will remain as entered. In the drop down list, common date and time formats are listed. However, the other option allows you to specify formats for numerics, leading characters, padding etc.
Format: Where entries must be one of a range of set values, the values can be entered here, separated by a semi-colon (;). Ie entry of "PLC;Ltd;Co" will limit the entry to one of these three options via a drop down list.
Entry List: Where entries must exist in another table, this allows the table to be specified of available tables in the drop down list.
Valid Table: Where entries must exist in another table, this entry allows the column which holds the ID to be stored (normally the ID column) to be selected.
Valid Column: Once the Valid Column has been selected, the set display button will display the available fields in the selected validation table.
Set Display: Tick those columns that should be displayed in the drop down selection list (as few as possible to avoid display truncation)
Mandatory:If during row insertion, this is a mandatory field, this should be ticked. Leaving this unticked makes entry of the defined column optional.
Validation Settings:
The 2nd set of validation settings are used to define field calculations, and how the defined field is used for filtering and summary display as follows:
Calculation:
As with excel, one column can be calculated from the contents of another. This allows say, the VAT amount to be calculated from a column holding the net amount. The calculation allows the calculation to be directly entered. Options besides the calculation allow the entered calculation to be validated ( ) which will check for errors, or for the calculation to be built up through field and calculation selection ( ).
Show Summary:
Where this tick-box is marked, the defined column will be displayed in the data summary display. Where left unticked, it will only be displayed in the form or sheet entry.
Once all prompts in the column definition have been entered, press the add/update button to save the changes to the column or add it to the defined column list.
At the base of the screen, the other action buttons available are:
New - Adds a new blank column to the table (enter the definition into the column properties panel)
Display Layout - Is used to define the layout of the form based screen for this table
Save - Saves your changes to the defined column.
Calculation Definition
New data storage tables are defined from the NEW button on the My Data area of the home or Nodal Display section of the Metamation screen. Once the basic properties of the table have been defined, you can then define the columns (fields or content) which can be stored in the table.
As with excel, one column can be calculated from the contents of another. This allows say, the VAT amount to be calculated from a column holding the net amount. The calculation allows the calculation to be directly entered. Within the properties panel of the column, options besides the calculation allow the entered calculation to be validated ( ) which will check for errors, or for the calculation to be built up through field and calculation selection ( ).
Within the prompt for the calculation, calculations may be generally entered as text as per Excel style notation. The fields are named as per the internal field names, so for instance, if you have defined a field for VAT NET AMOUNT stored within internal field VALUE1, you should refer to the field as VALUE1.
As an example, to add to values together, the formula would be "VALUE1 + VALUE2", where VALUE1 and VALUE2 are the field names.
The common operators which are available for use in the manual calculation entry are as follows:
/ - Divide the first number by the 2nd
+ - Add the two numbers together
- Subtract the 2nd number from the 1st number
* - Multiply the two numbers together
& - Join the two numbers together (i.e, "12 & 34" makes it "1234", not 46)
() - Brackets - used to control flow of logical calculation
Column Calculation Wizard
New data storage tables are defined from the NEW button on the My Data area of the home or Nodal Display section of the Metamation screen. Once the basic properties of the table have been defined, you can then define the columns (fields or content) which can be stored in the table.
As with excel, one column can be calculated from the contents of another. This allows say, the VAT amount to be calculated from a column holding the net amount. The calculation allows the calculation to be directly entered. Within the properties panel of the column, options besides the calculation allow the entered calculation to be validated ( ) which will check for errors, or for the calculation to be built up through field and calculation selection ( ).
Within the calculation wizard is displayed, a pop-up window will be presented on screen as follows:
The calculation wizard allows fields to be selected from the scrolling right hand list, and operations from the scrolling left hand list. As entries are selected by clicking on the field or operation name, so the calculation is built up in the calculation window.
As operators are selected (such as the IF condition), the number of parameters will automatically be shown. Reposition the cursor in the correct location for the condition, positive and negative results as require.
Adding fields or operators will automatically be inserted in the current cursor location. If required, text can be added, calculations be amended, and sections copied and pasted within the calculation using the keyboard and mouse within the calculation window as it is build up.
Calculations are generally entered as text as per Excel style notation. The fields are named as per the internal field names, so for instance, if you have defined a field for VAT NET AMOUNT stored within internal field VALUE1, you should refer to the field as VALUE1.
As an example, to add to values together, the formula would be "VALUE1 + VALUE2", where VALUE1 and VALUE2 are the field names.
The common operators which are available for use in the manual calculation entry are as follows:
/ - Divide the first number by the 2nd
+ - Add the two numbers together
- Subtract the 2nd number from the 1st number
* - Multiply the two numbers together
& - Join the two numbers together (i.e, "12 & 34" makes it "1234", not
46)
() - Brackets - used to control flow of logical calculation
The other entries which can be manually entered into the calculation are as follows:
Expression Actual action Validation
>, <, =, <=,
>=, =>, =<
Conditions, can be used in the 1st
parameter of an IF clause
Must have a 1st and
2nd value – of any type
IF() An IF condition, must have 3 parameters (as per excel IF format) of condition, result true, result false
1 st parameter must beacondition(see<,>,=,<=,=>
etc), or a condition test of AND, OR, ISBLANK, ISNUMBER, NOT – see below)
“text” Any text inside double quotes is valid, and acts as a text value
numeric Any numeric entry up to the next separator is valid, and is treated as a literal value (i.e., 9, “101.7” or “3.145927”)
Any brackets or commas inside quote marks are part of text
ABS() ABS function – as per excel Only 1 parameter, must be a numeric
AND() Logical join of 2 or more
(separated by commas) conditions
– can only be used in IF()
condition
OR() Logical OR join of 2 or more conditions – can only be used in IF condition
NOW Returns the current system time in the format HH:MM:SS
TODAY Returns the current system date in the format DD/MM/YY
DATETIME Returns the current system date and time
DATEDIFF() Returns the time or date difference. Three parameters
(separated by commas) are the 1st
date, 2nd date and a difference marker of “D” for days, “M” for
2 or more parameters, each parameter must be a condition, see IF()
1st parameter
2 or more parameters, each parameter must be a condition, see IF()
1st parameter
months, “H” for hours, “M” for minutes or “S” for seconds
DAY() Returns the date (1-31) within a specified date element
MONTH() Returns the month (1-12) within a specified date element
YEAR() Returns the year (yyyy) within a specified date element
INT() Returns the integer element of a numeric parameter
HOUR() Returns the hour (0-23) within a specified time element
MINUTE() Returns the minute (0-59) within a specified time element
SECONDS() Returns the seconds (0-59) within a specified time element
ISBLANK() Returns a condition of whether the column described is blank or not – has to be used in an IF() clause
NOT() Swaps the true and false around in an IF clause
TRIM() Trims leading and trailing spaces of a defined element
ISNUMBER() Returns a condition of whether the column described contains a numeric or not – has to be used in an IF() clause
ISTEXT() Returns a condition of whether the column described contains text or not (i.e., is not numeric) – has to be used in an IF() clause
MID() Extracts part of a text string. Three parameters (separated by commas) are element, start position and number of characters
LEN() Returns the length of an element 1 parameter only, must be numeric. Once the calculations have been built up, the Parse option will validate the calculation for obvious errors, and the Save will save the caluation back into the original properties screen.
Prompt Formats
New data storage tables are defined from the NEW button on the My Data area of the home or Nodal Display section of the Metamation screen. Once the basic properties of the table have been defined, you can then define the columns (fields or content) which can be stored in the table.
Against the defined columns, the Format column of the properties window dictates the format for entry and re-display of the columns. Where left blank (--Select--) the data will remain as entered. In the drop down list, common date and time formats are listed. However, the other allows you to specify formats for numerics, leading characters, padding etc.
Common characters with special meanings are as follows:
A = Mandatory position for an alphabetic character
N = Mandatory position for a numeric character
0 = Mandatory pad for a numeric number
# = Optional position for a numeric number
-, +, _ etc = padding characters
"xx" = pad characters in the text
Available Storage fields
New data storage tables are defined from the NEW button on the My Data area of the home or Nodal Display section of the Metamation screen. Once the basic properties of the table have been defined, you can then define the columns (fields or content) which can be stored in the table.
When defining data, your created field will be stored in one of the pre- defined available fields within the Metamation table structure. Each pre-defined field can have only once user per defined table. When defining the fields (columns), in the column properties panel, the data store indicates the real storage field where the information will be held in the table. Many storage fields of different data types are available.
Every type of field has a number of occurrences. So for instance, the field to hold fax numbers (FAX) has two occurrences, so available storage fields are FAX1 and FAX2.
| 
