User-Defined Fields

Overview 

• User-defined fields allow organisations to 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

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

• Once a user-defined field is added, a tab named User Fields is created, and the user-defined fields are listed there.

Navigation:  Main Menu > Setup > User-defined Fields

Buttons Explained

• New: Creates a new 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: Allows a stored procedure to be created to autofill the user field.

Filters Explained

• A dropdown list of the available modules to select one (to the left of New (button)). 

Fields Explained

• 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 typed in.

• Editor: A dropdown list of options that specifies how users will complete the field.

• Layout: Specifies where the user-defined field is placed. The options are:

  • Below: The field is displayed below the caption.

  • Column: The field starts a new column.

  • Right: The field is displayed to the right of the caption.

• Caption: Specifies where the caption is displayed. The options are: 

  • Hide

  • Left

  • Right

  • Top

• Width: Specify the fields width. 

• Decimal: Specify the number of decimal places if the input requires a number.

• Order: A field to type a number to change the order in which the fields are displayed.

• Menu (...):  A context menu that changes depending on the field clicked. Typical options are:

  • Insert

  • Edit Lookup List

  • Edit SQL

  • Move Up

  • Move Down

  • Delete

Editor Types

• 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). Users must select an option from the lookup list.

• ComboBox (Editable): Requires a lookup list (right click the entry and select Edit Lookup List). Users can select an entry from the lookup list or type 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 is displayed in bold as a group header.

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

• 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: Allows users to type a memo.

• Number: Allows users to 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 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 such as 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