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