User-Defined Fields

Overview 

• User-defined fields allow users to add fields if the standard fields are insufficient. User-defined fields can be added to the following modules.

  • My Company & Staff

  • Customers & Customer Contacts

  • Suppliers and Supplier Contacts

  • Classes (Used in Items)

  • Transaction Headers

  • Workflow Headers

  • Workflow Actions where Type = Input Forms

  • Tracker Modules

• User-defined fields are set up in various modules, using a similar interface and includes a Preview button to preview the layout.

• A number of read-only user-defined field types are provided which can be used to display information that is not meant to be updated by users. These fields must be updated by custom triggers or procedures in the database.

• When the mouse pointer hovers over a user-defined field, the Username and Date that the record was last updated, is displayed.

Field Types

• Caption - Static text lable

• Checkbox - Returns 1=Checked; 0=Unchecked

• ComboBox (Fixed list) - Fixed selection from lookup list

• ComboBox (Editable) - Can select from lookup list or type text

• Customer - Customer lookup (Soon to be deprecated)

• Date

• Date (Read-only) 

• Filename  - Select a file. The file is copied to a designated folder and the name of the file is stored in the field.

• Group Header - Static label used to group sections

• Image - Stores an image in the database. You can upload or paste images into the control. Multiple image formats are supported.

• Item - Item lookup (Soon to be deprecated)

• Lookup (SQL) - Uses SQL to lookup ID-Value pairs. See the Notes below for more.

• Memo

• Number

• Number (Read-only)

• Supplier - Supplier lookup (Soon to be deprecated)

• Text

• Text (Read-only)

• Time

• Timestamp

Notes

Lookup (SQL)  

This type creates a lookup ComboBox using the SQL entered in the SQL Editor. (Right-click the field and select Edit SQL) The SQL needs to return an ID (Integer) field and a DESCRIPTION (Text) field.

The Lookup (SQL) field type can contain parameters in the SQL which will be populated from same-name user-fields linked to the same record. For example,  if the parameter name is :CUSTOMER,  it will be assigned the value from the user-defined field named CUSTOMER. When a transaction is saved, the Lookup (SQL) field values are validated and cleared if they are no longer valid. 

Selectable Stored Procedure

A selectable Stored Procedure can be created using Menu > Create selectable Stored Procedure. For this reason, the Name field on User-defined fields may not use Firebird reserved names. Eg. DATE, TIMESTAMP, ETC. 

The following parameter types are included in the Selectable Procedure: 

• Group Header

• Repeat Header

• Input - Text 

• Input - Number

• Input - Select Number

• Input - Number/Text (The ID and the Text are returned in separate fields - NAME_ID / NAME_TEXT)

• Input - Memo

• Lookup

Sample SELECT SQL from Classes Stored Procedure 

select

M.MATUNITID,

M.DESCRIPTION,

U.MAKE,

U.MODEL,

U.REGNO

from MATUNIT M 

join UDF_3011_VEHICLES (M.MATUNITID) U on true