User-defined Fields
Overview
User-defined fields allow you to add additional fields when the standard fields provided are not sufficient. 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 which includes a Preview button that allows you to preview what the layout will look like.
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 need to 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