Using Custom Fields

TIMS3 is installed with 10 predefined custom fields. For easy startup using custom fields all you need do is turn on the use of these fields using the 'Used' checkbox in the figure below. More fields than 10 are also possible but, it requires you to have Database Administrator privilege and sufficient knowledge about how to manipulate your backend RDBMS engine, see 'Unlimited Custom Fields' below. Data for this form is stored in the TIMS3 database table named tblUserFlds.

Field Descriptions

The custom field description indicates the name that will be displayed for the custom field on the form in Project Navigator and Data Manager. The default field description and predefined field names indicate the type of data that can be stored in each field.

Format String

This is the Visual Basic field format property to describe how the data is formatted for display. Note these are display properties only and do not affect the actual input value. Some possible values include:

• <Null>
  

• Yes/No
  

• Medium Date
  

• Short Date
  

• Long Date
  

• Long Time
  

• ##,##0.00

For a full range of options see the Visual Basic Format() function and some sample VB code below:

MyStr = Format(Time, "Long Time")
MyStr = Format(Date, "Long Date")
MyStr = Format(MyTime, "h:m:s") ' Returns "17:4:23".
MyStr = Format(MyTime, "hh:mm:ss AMPM") ' Returns "05:04:23 PM".
MyStr = Format(MyDate, "dddd, mmm d yyyy") ' Returns "Wednesday, Jan 27 1993".
' If format is not supplied, a string is returned.
MyStr = Format(23) ' Returns "23".
' User-defined formats.
MyStr = Format(5459.4, "##,##0.00") ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00") ' Returns "334.90".
MyStr = Format(5, "0.00%") ' Returns "500.00%".
MyStr = Format("HELLO", "<") ' Returns "hello".
MyStr = Format("This is it", ">") ' Returns "THIS IS IT".

Field Control Types

Custom Field data can be displayed using a variety of different control types.

• TEXTBOX - this is the default and most simple control type it displays the field's data.
  

• SELFLOOKUP - Custom field data is displayed in a drop-down list, showing every unique previous entry in that field.  This type of control is ideally suited for a volume of text data that you want to enter consitently, but will grow.
  

• CHECK - Stores 'Yes/No' type data.  The stored value can be displayed as 'Yes/No', 'True/False', or 'On/Off' by setting the field's Format.  The default Format is 'Yes/No'
  

• COMBO -  Field data is displayed in a drop-down pick list using the information specified in the field's Validation Code.
  

• IDCOMBO -  As with the COMBO control type, Field data is displayed in a drop-down pick list using the information specified in the field's Validation Code, however with IDCOMBO, the actual stored value is a number, but the displayed value is a text value.  Use the control type to store the 'Foreign Key' from an separate table.

Field Validation Codes

A Validation code is written using a rudimentary language that tells TIMS3 how a custom field should be processed.   Ways to process a custom field may include:

• Ensuring field data is stored in Upper Case.
  

• Disallowing certain characters.
  

• Specifying members of a picklist (for 'COMBO' and 'IDCOMBO' control types.) either by naming them explicitly or providing an SQL SELECT statement that returns the desired values.

The codes are written in the form: "@|key₁([value₁]) @|key₂([value₂]) @|key₃([value₃]) etc&ldots;" Keywords are marked with the pair of characters "@|" and are followed immediately by the keyword, which is in turn followed immediately by the value parameter, which is embedded between "([" and "])". The order of the keywords is not important, but note that they are processed in the order in which they appear. This means that if an earlier keyword modifies data (such as "UCASE"), later keywords will process the newly modified data - not the original data.

Available keywords and allowable values are as follows: (Keywords are not case sensitive.)

• "UCASE" - values:"y" and "n" (or "Y" and "N").

  • Examples:
    
  • "@|Ucase([y])" converts data to Uppercase.
    
  • "@|Ucase([n)" converts data to Lowercase.
• "NCHAR" - values:Any character. Disallows any of the characters that appear in the value string. Characters in the value string are case sensitive.
  • Examples:
    
  • "@| NChar ([1234567890])" disallows numerals.
    
  • "@| NChar ([]])" disallows the "]" character.
• "INSET" - values: Series of strings with "|" as delimiter. Verifies that the data is a member of the set of strings listed in the value parameter. INSET verification is not case sensitive.  Ideally, this Keyword should be paired with a "COMBO" control type.
  • Examples:
    
  • "@|InSet([Monday|Tuesday|Wednesday])" ensures data is one of "Monday", "Tuesday" or "Wednesday".

The validation code "@|UCase([y]) @|NChar([/"\])" converts the data to uppercase and disallows "/", """, and "\".

• "SQL" and "SQLOPEN" - Specifies an SQL SELECT query string to provide data for a lookup table. "SQL" or "SQLOPEN" type validation codes must be paired with either "COMBO" or "IDCOMBO" control types.
  

• Use "SQL" enforce a Limit to List style of picklist. In the mode is set, the user must either choose an available value from the results of the SQL query or nothing (NULL).

  • This type of Validation code is ideally suited to be paired with an "IDCOMBO" control type where the stored value is a record ID not the actual data to which it refers.
    
  • Example:.
    
  • "@|SQL([SELECT ID, Desc from MyView;])", paired with an "IDCOMBO" control type displays the field "Desc" in a descriptive picklist, and stores the field "ID" in the Custom Field., where "MyView" is a table or view in the TIMS3 Database.

  • Use "SQLOPEN" to give the user a picklist without forcing the entered valued to be limited to the provided list.

    • This type of Validation code is designed to be paired with a "COMBO" control type.
      
    • Example:
      
    • "@|SQLOPEN([SELECT Desc from MyView;])" Gives the user the values in the "Desc" field of "MyView", but doesn't force the user to choose only from the list.

About "SQL" and "SQLOPEN" Validation Codes

• When "@|SQL" is paired with an "IDCOMBO" the SQL statement must return two columns:  (1st) A numeric ID  Field, which must be of type Long Integer or the equivalent.  This field contains the information to be actually stored in the custom field; and (2nd) A text description containing the text to be displayed.
  

• When "@|SQL" or "@|SQLOPEN" is paired with an "COMBO" the SQL statement must return one column:  a text description field containing the text to be displayed.
  

• Any table or view mentioned in "SQL" or "SQLOPEN" validation code MUST be present in the TIMS3 database with read permission for all TIMS3 users.  This is really only significant for separate lookup tables that would not normally appear in TIMS3.  For TIMS3 SQL Server, a simple solution would be to create a view in the TIMS3 Database that points to data from your required table.
  

• SQL statements will be processed as pass-through queries, so the SQL syntax must be valid for the type of server that you are using, be it "MS Access", "SQL Server", or "Oracle".

Examples:

        @|SQLOPEN([SELECT Distinct Executive from tblCompanies])   with ControlType=COMBO
        @|BOOL([y])    with ControlType=CHECK

Unlimited Custom Fields

TIMS3 is capable of handling an unlimited number of custom fields, however, configuring unlimited custom fields is a task that requires Database Administrator privileges and knowledge on how to edit and manipulate database tables. If you are not sure how to manipulate database tables do not attempt to 'try something' on a live database, doing so could mean loss of data (perhaps even your life if you delete the wrong data).

In order to add additional custom fields the DBA must perform at least two operations on the TIMS3 database:

1. First the appropriate database table needs to be modified to include the additional field name desired. The appropriate table depends on which document group the data will be associated with. Document groups include GRAPHIC, WORDPRO or SPREADSHEET. The tables associated with these groups are noted below:

   Document Group	Table Name
   GRAPHIC	tblGraphicDocs
   WORDPRO	tblWordProDocs
   SPREADSHEET	tblSpreadSheetsDocs

2. After adding the field to the appropriate table, a record needs to be added to the TIMS3 database table named tblUserFlds. This data record (samples shown below) should define the TIMS3 table the field was added to in TableName, the field name in UserFldName, the displayed field decription in Descr, Used should be set to 1, TabOrder should be set to its appearance sequence number, ValidationCode, FormatString, ControlType should be filled as desribed above. Note the InputMask is not presently used and should be ignored/filled with <Null> data.