Skip to content

User Defined Fields

 

All the User-defined field values are stored in one table in QuickEasy, namely FIELDVALUES. Each record in this table is linked to a Module (eg. Customers, Suppliers, etc.), a record in that Module as well as to the User-defined field that defines it.

The link to the Module is created using the MODULEID field. Following is a list of the Module keys used in QuickEasy:

  • 0:Customers
  • 1:Customer Contacts
  • 2:Suppliers
  • 3:Supplier Contacts
  • 4:Staff

The link to the primary key of a Record in the Module is created using the RECORDID field.

The link to the primary key of the user-defined field that defines it's properties is created using the FIELDSPECID field.

The value of the user-defined field is stored in either FIELDVALUE, FIELDVALUE_DATE or FIELDVALUE_BLOB depending on the type of field.

Example - User-defined field in Customers

Start with a basic Customer select statement:

select 
C.CUSID, 
C.COMPANY
from CUSMAIN C
order by C.COMPANY

Now add an embedded select statement to extract the value of the user-defined field:

select 
C.CUSID, 
C.COMPANY,
(select F.FIELDVALUE
from FIELDVALUES F
where F.MODULEID = 0
and F.RECORDID = C.CUSID
and F.FIELDSPECID = 1) as BEE_Rating
from CUSMAIN C
order by C.COMPANY

Notes:

where F.MODULEID = 0 (0 = Fixed value for Customer Module)

and F.RECORDID = C.CUSID (Will link the user-field to a record in the Customer Module)

and F.FIELDSPECID = 1 (This value identifies the User-defined field and can be viewed in the ID field of the User-defined Fields grid - Edit>Settings & Defaults>User-defined Fields)