General Template Fields Tables

Template Field catalog

When a Global or Subledger Template is saved, STEPforward stores the list of fields used by the Template in the system catalog table named SF_SYSMasterTemplateFields of the following structure:

Column Generalized data type Use
TemplateName VARCHAR(8) Name of the base Template
CloneName VARCHAR(8) Name of the Template clone
Type CHAR(1) Template type (Base or Clone)
IdNumber SMALLINT Id number of the field
UserName VARCHAR(30) User name of the field
Action TINYINT Run-time action of the field
AuditControl LOGICAL Flag to control use of the field at TX data entry time
SubledgerNumber LOGICAL Flag to track fields of Subledger Number type
SubDataType TINYINT Type of data accepted by Subledger Number field
LinkNo SMALLINT Number of the link for linked fields
Location VARCHAR(8) Name of the source Template for link
Mandatory LOGICAL Flag to track mandatory data input in the field
DataPrecision TINYINT Number of decimal places for numeric field
ValueList SMALLINT Id number of the Value List attached to the field
SF_TimeStamp SMALLINT Version of the row (incremented with each update)

Notes:

The table contains one row for each Field/Template pair used by STEPforward. If the Template has clones, each clone provides its own set of rows, listing all fields used in it.

Type is a one-character code that marks the Template as being of the base (B) or clone (C) type.

IdNumber is the id number of the field that is unique for each Template Domain. That is, the same id number can be used for the Transaction field and the Global or Subledger field; however, within each template category the id numbers are unique.

UserName is the displayed name of the field (in contrast to the system name that is used to form the name of the table column). When a field with the given id number is used in a number of different Templates or their clones, all basic characteristics of the field are preserved (such as the field system name and data type), but the user name of the field can vary from one Template to another.

Action is a numeric code that determines the run-time behavior of the field in the given Template/clone. The following codes are used by STEPforward for the field actions:

Code Action
0 Empty the field on new record entry and accept the I-beam when it tabs into the field
1 Preserve old contents when primed up for the new record entry and accept I-beam when it tabs into the field
2 Preserve old contents when primed up for the new record entry and move I-beam to the next connected field when it tabs into the field
3 Skip to the next connected field when the I-beam tabs into the field
4 Lock the field (make it non-editable)
254 Show a display-only field as a button
255 Show a display-only field as a non-editable text field

LinkNo is a non-zero number assigned to all linked fields in the Template/clone that belong to the same train (i.e. originate from the same source Template). Fields that are not linked have LinkNo set to 0.

Location is the name of the Source Template for the linked fields. Fields that are not linked have Location set to an empty string.

Mandatory column determines whether the field can be empty for the record to be saved.

DataPrecision carries the number of decimal places displayed by the fields of FLOAT or MONEY data type. Note that the internal representation of data in the database table is not dependent on this parameter; rather, the human-readable form of the field's contents is formatted with the prescribed number of decimal places when the field is set to a specific value. The DataPrecision value is ignored for the fields of other data types.

ValueList is the id number of the Value List (as recorded in SF_SYSStandardValues table) that is attached to the field. If no Value List is attached (i.e. the field's contents is editable rather than selectable from the list), the ValueList is set to -1.

The following three columns are used only with the fields that belong to Subledger Templates:

AuditControl is a flag that determines whether or not the field will contribute its contents to the process of formatting the audit control text during the transaction data entry.

SubledgerNumber is a flag that marks the field as the one that holds the subledger number.

SubDataType is used only with the fields that are marked as SubledgerNumber types. It determines the type of characters that can be entered in the field and may have the following values:

Type Contents
1 Any alphanumeric character
2 Numeric characters (digits from 0 to 9) only

The following is a partial sample of contents of SF_SYSMasterTemplateFields table:

TemplateName CloneName Type IdNumber UserName             SubledgerNumber LinkNo Location
------------ --------- ---- -------- -------------------- --------------- ------ --------
Allocate                    205      Allocation Prefix    0               0
Allocate                    206      Allocation GL        0               0
Area                        68       Area                 0               0
Area                        69       Area Description     0               0
BankRec                     208      Payee / Description  0               0
BankRec                     209      Source Name          0               0
BankRec                     210      Issued Amount        0               0
BankRec                     211      Issue Date           0               0
BankRec                     212      Reference            0               0
BankRec                     213      Date Cleared         0               0
BankRec                     214      Days To Clear        0               0
BankRec                     217      loadBankData         0               0
CardFile                    1        Customer             1               1      Names
CardFile                    8        Product Code         0               3      PCodes
CardFile                    12       Product Description  0               3      PCodes
CardFile                    16       Name                 0               1      Names
CardFile                    60       Route                0               4      Route
CardFile                    61       Driver's Name        0               4      Route
CardFile                    68       Area                 0               2      Area
CardFile                    69       Area Description     0               2      Area

General Field catalog

Every data field used in the Global and Subledger Templates is recorded in the system catalog table named SF_SYSMasterInputFields of the following structure:

Column Generalized data type Use
SystemName VARCHAR(30) System name of the field
UserName VARCHAR(30) User name of the field
SF_DataType TINYINT Type of data the field stores
Width SMALLINT Size of the field (max number of characters)
IdNumber SMALLINT Id number of the field
SF_TimeStamp SMALLINT Version of the row (incremented with each update)

Notes:

The table contains one row for each Global and Subledger data field used by STEPforward.

SystemName is the name of the field that conforms to the syntactic requirements of the column naming convention of the RDBMS. SystemName is assigned to the column in the Template table when the Template is saved.

UserName is the name of the field that appears in the field title when the Template is displayed at the data entry time. While more than one user name may be associated with the given field (identified by its system name and id number), only the first saved occurrence of the user name is recorded in SF_SYSMasterInputFields table.

SF_DataType is the code of the data type carried by the field. The following is the list of all data types supported by STEPforward in the generalized, RDBMS-independent form:

Data type Value Use
DATE 0 Calendar date type
TEXT 1 Character string type
INTEGER 2 4-byte integer type
FLOAT 3 Floating point (8-byte double) type
MONEY 4 Money type
LOGICAL 6 Logical flag type
ACCPERIOD 7 Accounting Period type
GLPREFIX 9 GL Prefix type
GLNUMBER 10 GL Number type
TIME 11 Time type
SHORT 12 2-byte integer type
TINY 13 1-byte integer type
NOTE 18 Large text type (BLOB)
IMAGE 19 Image type (BLOB)
SLNUMBER 20 Subledger Number type

Width is the number of characters (alpha or numeric) that can be entered into the field.

IdNumber is a unique id number assigned to the field.

The following is a partial sample of contents of SF_SYSMasterInputFields table:

SystemName           UserName             SF_DataType Width IdNumber
-------------------- -------------------- ----------- ----- --------
Card_Effective_Date  Effective Date       0           20    81
Invoice_Date         Invoice Date         0           40    146
state_short          St                   1           2     13
Alt_St               Alt St               1           2     53
Product_Group_Code   PGC                  1           2     119
Package_Content      Package Content      1           255   193
Invoice_Number       Invoice Number       2           40    145
discount_rate        Discount Rate        3           20    26
Unit_Price_Modifier  Unit Price Modifier  3           40    124
Credit_Limit         Credit Limit         4           40    121
Unit_Price           Unit Price           4           40    128
Price                Price                4           40    176
Issued_Amount        Issued Amount        4           40    210
use_tax              Tax Charged?         6           20    33
ten_ninety_nine      1099 Required?       6           20    34
Quantity             Quantity             12          20    76
Units_per_Package    Units / Package      12          40    194
Qty_Garments         Qty Garments         12          40    201
Expected_Turns       Expected Turns       12          40    216
net_days             Net Days             13          20    24
discount_days        Discount Days        13          20    25
Route                Route                13          20    60
Day_of_Week          Day of Week          13          20    70
vendor_notes         Vendor Note Field    18          20    31
AR_Remarks_1         Remarks 1            18          20    65
SP_Remarks           SP Remarks           18          20    67
Other_Employee_Data  Other Employee Data  18          20    111
SF_SubledgerNumber   Type                 20          20    1

Indexing

The following index is created on SF_SYSMasterInputFields table (the exact syntax of the CREATE INDEX command varies from one RDBMS to another; the following example shows the command formatted for Sybase):

create uniqu index SF_FieldName on SF_SYSMasterInputFields (SystemName)