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
SF_TemplateName VARCHAR(8) Name of the base Template
SF_CloneName VARCHAR(8) Name of the Template clone
SF_Type CHAR(1) Template type (Base or Clone)
SF_IdNumber SMALLINT Id number of the field
SF_UserName VARCHAR(30) User name of the field
SF_Action TINYINT Run-time action of the field
SF_AuditControl LOGICAL Flag to control use of the field at TX data entry time
SF_SubledgerNumber LOGICAL Flag to track fields of Subledger Number type
SF_SubDataType TINYINT Type of data accepted by Subledger Number field
SF_LinkNo SMALLINT Number of the link for linked fields
SF_Location VARCHAR(8) Name of the source Template for link
SF_IsDetail LOGICAL Flag to track the origin of the field
SF_Mandatory LOGICAL Flag to track mandatory data input in the field
SF_DataPrecision TINYINT Number of decimal places for numeric field
SF_ValueList SMALLINT Id number of the Value List attached to the field
SF_ContextHelp BLOB RTF-based text of the context-sensitive help for 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.

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

SF_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.

SF_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.

SF_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

SF_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 SF_LinkNo set to 0.

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

SF_IsDetail is a flag that determines whether or not the field is used in Detail table.

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

SF_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 SF_DataPrecision value is ignored for the fields of other data types.

SF_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 SF_ValueList is set to -1.

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

SF_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.

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

SF_SubDataType is used only with the fields that are marked as SF_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:

SF_TemplateName SF_CloneName SF_Type SF_IdNumber SF_UserName          SF_SubledgerNumber SF_LinkNo SF_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
SF_SystemName VARCHAR(30) System name of the field
SF_UserName VARCHAR(30) User name of the field
SF_DataType TINYINT Type of data the field stores
SF_Width SMALLINT Size of the field (max number of characters)
SF_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.

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

SF_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

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

SF_IdNumber is a unique id number assigned to the field.

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

SF_SystemName        SF_UserName          SF_DataType SF_Width SF_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 (SF_SystemName)