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)