User-Defined Fields
Overview
- User-defined fields let organisations capture more information if the standard fields are insufficient. User-defined fields can be added to the following modules from the user-defined fields interface.
- Customers
- Customer Contacts
- Suppliers
- Supplier Contacts
- Staff
- My Organisation
- Several read-only user-defined field types are provided 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.
- Once a user-defined field is added, a tab named User Fields is created, and the user-defined fields are listed there.
Settings
| Setting | Description | Default |
|---|---|---|
| General > Folder - Documents - e.g. C:\Temp\Docs |
| Blank |
Buttons Explained
| Button | Description |
|---|---|
| New | Creates a field. |
| Delete | Deletes the selected field. |
| Save | Saves any changes. |
| Preview | Opens the fields on a blank page for viewing. |
| Menu > Create Selectable Stored Procedure | Lets you create a stored procedure to autofill the user field. |
Filters Explained
| Filter | Description |
|---|---|
| Module | A dropdown list of the available modules to select one (to the left of the New button). |
Fields Explained
Info
Fields marked in blue (✎) can be edited.
| Field | Description |
|---|---|
| ID | The system-generated number for the entry. |
| Name | The system name for the user field. |
| Caption | The caption for the field which can be entered. |
| Editor | A dropdown list of options that specifies how users complete the field. |
| Layout | Specifies where the user-defined field is placed. The options are:
|
| Caption | Specifies where the caption appears. The options are:
|
| Width | Specify the fields width. |
| Decimal | Specify the number of decimal places if the input requires a number. |
| Order | Enter a number to change the order in which the fields appear. |
| Menu (...) | A context menu that changes depending on the field clicked. Typical options are:
|
Editor Types
| Field | Description |
|---|---|
| Checkbox | Creates a checkbox. Returns 1=Checked; 0=Unchecked |
| ComboBox (Fixed list) | Requires a lookup list (right-click the entry and select Edit Lookup List). You must select an option from the lookup list. |
| ComboBox (Editable) | Requires a lookup list (right-click the entry and select Edit Lookup List). You can select an entry from the lookup list or enter the text. |
| Customer | Creates a lookup list of customers. |
| Date | Creates a calendar to select a date. |
| Date (Read-only) | Displays a date that cannot be edited. Click Menu (button) > Create Selectable Stored Procedure to determine the date. |
| Filename | Option to select a file. The file is copied to a designated folder, and the name of the file is stored in the field. |
| Group Header | The caption appears in bold as a group header. |
| Image |
|
| Item | Provides a dropdown list of items for selection. |
| Lookup (SQL) | Uses SQL to lookup ID-Value pairs. See the notes below for more information. |
| Memo | Type a memo. |
| Number | Enter a number. |
| Number (Read-only) | Displays a number that cannot be edited. Click Menu (button) > Create Selectable Stored Procedure to determine the number. |
| Supplier | Displays a lookup list of suppliers. |
| Text | Displays a field for text input. |
| Text (Read-only) | Displays text that cannot be edited. Click Menu (button) > Create Selectable Stored Procedure to determine the text. |
| Time | Provides a time chooser to select a time. |
| Timestamp | Provides both a calendar and time chooser. |
| Caption | Displays the caption. |
| Lookup (SQL) | Completes the field by running an SQL query. |
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 must return an ID (Integer) field and a DESCRIPTION (Text) field.
The Lookup (SQL) field type can contain parameters in the SQL that are populated from same-name user-fields linked to the same record. For example, if the parameter name is :CUSTOMER, it is 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 such as DATE and TIMESTAMP.
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