UI TYPE (SQL Data/Column) list for vTigerCRM Custom Fields
GENERATED VTIGER CRM UITYPE, TYPE of DATA and SQL Data/Column Type on Custom Field Settings
| Field type on custom field settings |
Length | Decimal places |
(Generated) uitype |
(Generated) typeofdata |
(Generated) SQL Column Type |
Mandatory |
| Text | 100 | — | 1 | V~O~LE~100 | varchar(100) | No |
| Text | 100 | — | 1 | V~M~LE~100 | varchar(100) | YES |
| Text | 25 | — | 1 | V~O~LE~25 | varchar(25) | No |
| Text | 25 | — | 1 | V~M~LE~25 | varchar(25) | YES |
| Number (float) |
10 | 0 | 7 | NN~O~10,0 | decimal(11,0) | No |
| Number (float) |
10 | 0 | 7 | NN~M~10,0 | decimal(11,0) | YES |
| Number (float) |
7 | 3 | 7 | NN~O~7,3 | decimal(11,3) | No |
| Number (float) |
7 | 3 | 7 | NN~M~7,3 | decimal(11,3) | YES |
| Percent % |
— | — | 9 | N~O~2~2 | decimal(5,2) | No |
| Percent % |
— | — | 9 | N~M~2~2 | decimal(5,2) | YES |
| Currency | 10 | 2 | 71 | N~O~10,2 | decimal(13,2) | No |
| Currency | 10 | 0 | 71 | N~M~10,0 | decimal(11,0) | YES |
| Date | — | — | 5 | D~O | date | No |
| Date | — | — | 5 | D~M | date | YES |
| — | — | 13 | E~O | varchar(50) | No | |
| — | — | 13 | E~M | varchar(50) | YES | |
| Phone | — | — | 11 | V~O | varchar(30) | No |
| Phone | — | — | 11 | V~M | varchar(30) | YES |
| Pick List (Value 1, Value 2, etc.) |
— | — | 15 | V~O | varchar(255) | No |
| Pick List |
— | — | 15 | V~M | varchar(255) | YES |
| URL | — | — | 17 | V~O | varchar(255) | No |
| URL | — | — | 17 | V~M | varchar(255) | YES |
| Checkbox | — | — | 56 | C~O | varchar(3) | No |
| Checkbox | — | — | 56 | C~M | varchar(3) | YES |
| Text Area |
— | — | 21 | V~O | text | No |
| Text Area |
— | — | 21 | V~M | text | YES |
| Multi Select Combo Box |
— | — | 33 | V~O | text | No |
| Multi Select Combo Box |
— | — | 33 | V~M | text | YES |
| SkyPe ID |
— | — | 85 | V~O | varchar(255) | No |
| SkyPe ID |
— | — | 85 | V~M | varchar(255) | YES |
| EXISTING VTIGER (5.x) CRM UITYPE, TYPE of DATA and SQL Data/Column Type on the SQL tables: vtiger_field & module tables, e.g. vtiger_leaddetails & others |
||||||
| Example Column-name / table |
Fieldname | Fieldlabel | UITYPE | TYPEOFDATA | SQL column type |
Mandatory |
| Text box |
||||||
| tickersymbol / vtiger_account |
tickersymbol | Ticker Symbol |
1 | V~O | varchar(30) | No |
| noofemployees / vtiger_leaddetails |
noofemployees | No Of Employees |
1 | I~O (??? text) |
int(50) | No |
| qtyinstock / vtiger_products |
qtyinstock | Qty In Stock |
1 | NN~O (??? text) |
decimal(25,3) | No |
| qty_per_unit / vtiger_service |
qty_per_unit | No of Units |
1 | N~O (??? text) |
decimal(11,2) | No |
| subject / vtiger_servicecontracts |
subject | Subject | 1 | V~M | varchar(100) | YES |
| Text box, mandatory entry |
||||||
| time_start / vtiger_activity |
time_start | Time Start |
2 | T~M | varchar(50) | YES |
| time_end / vtiger_activity |
time_end | End Time |
2 | T~O | varchar(50) | No |
| accountname / vtiger_account |
accountname | Account Name |
2 | V~M | varchar(100) | YES |
| activitytype / vtiger_activity |
activitytype | Activtiy Type |
2 | V~O | varchar(200) | No |
| Text box with Inheritance |
||||||
| accesskey / vtiger_users |
accesskey | Webservice Access Key |
3 | V~O | varchar(36) | No |
| Text box with Inheritance, mandatory entry |
||||||
| account_no / vtiger_account |
account_no | Account No |
4 | V~O | varchar(100) | No |
| Date | ||||||
| enddate / vtiger_projecttask |
enddate | End Date |
5 | D~0~OTH~GE~startdate~Start Date [0 Null is a possible typo] |
date | No |
| dateinservice / vtiger_assets |
dateinservice | Date in Service |
5 | D~M~OTH~GE~dateinservice~Date in Service |
date | YES |
| datesold / vtiger_assets |
datesold | Date Sold |
5 | D~M~OTH~GE~datesold~Date Sold |
date | YES |
| sales_start_date / vtiger_service |
sales_start_date | Sales Start Date |
5 | D~O | date | No |
| sales_end_date / vtiger_service |
sales_end_date | Sales End Date |
5 | D~O~OTH~GE~sales_start_date~Sales Start Date |
date | No |
| expiry_date / vtiger_service |
expiry_date | Support Expiry Date |
5 | D~O~OTH~GE~start_date~Start Date |
date | No |
| support_end_date / vtiger_customerdetails |
support_end_date | Support End Date |
5 | D~O~OTH~GE~support_start_date~Support Start Date |
date | No |
| end_period / vtiger_invoice_recurring_info |
end_period | End Period |
5 | D~O~OTH~G~start_period~Start Period |
date | No |
| Date, default to currenttime |
||||||
| date_start / vtiger_activity |
date_start | Start Date & Time |
6 | DT~M~time_start | date | YES |
| date_start / vtiger_activity |
date_start | Date & Time Sent |
6 | DT~M~time_start~Time Start |
date | YES |
| Number box |
||||||
| employees / vtiger_account |
employees | Employees | 7 | I~O | int(10) | No |
| total_units / vtiger_servicecontracts |
total_units | Total Units |
7 | V~O | decimal(5,2) | No |
| json encoded string |
||||||
| to_email / vtiger_emaildetails |
saved_toid | To | 8 | V~M | text | YES |
| cc_email / vtiger_emaildetails | ccemail | Cc | 8 | V~O | text | No |
| Percent | ||||||
| probability / vtiger_potential |
probability | Probability | 9 | N~O | decimal(7,3) | No |
| progress / vtiger_servicecontracts |
progress | Progress | 9 | N~O~2~2 | decimal(5,2) | No |
| general relate field, for relating entities |
||||||
| sc_related_to / vtiger_servicecontracts |
sc_related_to | Related to |
10 | V~O | int(11) | No |
| product / vtiger_assets |
product | Product Name |
10 | V~M | int(19) | YES |
| Phone | ||||||
| phone / vtiger_account |
phone | Phone | 11 | V~O | varchar(30) | No |
| OrgUnit pickbox / Organization multiselect pickbox |
||||||
| from_email / vtiger_emaildetails |
from_email | From | 12 | V~M | varchar(50) | YES |
| email / vtiger_contactdetails |
13 | E~O | varchar(100) | No | ||
| Picklist | ||||||
| activitytype / vtiger_activity |
activitytype | Activity Type |
15 | V~M | varchar(200) | YES |
| industry / vtiger_account |
industry | industry | 15 | V~O | varchar(200) | No |
| Picklist, mandatory entry ??? |
||||||
| recurringtype / vtiger_activity |
recurringtype | Recurrence | 16 | O~O | varchar(200) | No |
| duration_minutes / vtiger_activity | duration_minutes | Duration Minutes |
16 | T~O | varchar(200) | No |
| reminder_interval / vtiger_users |
reminder_interval | Reminder Interval |
16 | V~O | varchar(100) | No |
| URL | ||||||
| website / vtiger_account |
website | Website | 17 | V~O | varchar(100) | No |
| Textarea with colspan=2 |
||||||
| commentcontent / vtiger_modcomments |
commentcontent | Comment | 19 | V~M | text | YES |
| description / vtiger_crmentity |
description | Description | 19 | V~O | text | No |
| Textarea with colspan=2, mandatory entry |
||||||
| question / vtiger_faq |
question | Question | 20 | V~M | text | YES |
| Textarea | ||||||
| message / vtiger_smsnotifier |
message | message | 21 | V~M | text | YES |
| bill_street / vtiger_accountbillads |
bill_street | Billing Address |
21 | V~O | varchar(250) | No |
| Textarea, mandatory entry |
||||||
| title / vtiger_troubletickets |
ticket_title | Title | 22 | V~M | varchar(255) | YES |
| Date | ||||||
| closingdate / vtiger_potential |
closingdate | Expected Close Date |
23 | D~M | date | YES |
| due_date / vtiger_activity |
due_date | Due Date |
23 | D~M~OTH~GE~date_start~Start Date & Time |
date | YES |
| due_date / vtiger_servicecontracts |
due_date | Due Date |
23 | D~O | date | No |
| targetenddate / vtiger_project |
targetenddate | Target End Date |
23 | D~0~OTH~GE~startdate~Start Date [0 Null is a possible typo] |
date | No |
| Textarea, mandatory entry |
||||||
| bill_street / vtiger_quotesbillads |
bill_street | Billing Address |
24 | V~M | varchar(250) | YES |
| 25, 26, 27,28 ??? missing entries at http://wiki.vtiger.com/index.php/Ui_types |
||||||
| access_count / vtiger_email_track |
access_count | Access Count |
25 | V~O | int(11) | No |
| folderid / vtiger_notes |
folderid | Folder Name |
26 | V~O | int(19) | No |
| filelocationtype / vtiger_notes | filelocationtype | Download Type |
27 | V~O | varchar(5) | No |
| filename / vtiger_notes | filename | File Name |
28 | V~O | varchar(200) | No |
| Time left |
||||||
| reminder_time / vtiger_activity_reminder |
reminder_time | Send Reminder |
30 | I~O | int(11) | No |
| No sample for UITYPE 31-50 in the SQL table: vtiger_field |
||||||
| Popup select box for account and contact addresses |
||||||
| accountid / vtiger_contactdetails |
accountid | Account Name |
51 | I~O | int(19) | No |
| Picklist for username entries |
||||||
| handler / vtiger_service |
assigned_user_id | Owner | 52 | I~O | int(11) | No |
| smcreatorid / vtiger_crmentity |
creator | Creator | 52 | V~O | int(19) | No |
| User picklist |
||||||
| smownerid / vtiger_crmentity |
assigned_user_id | Assigned To |
53 | V~M | int(19) | YES |
| Salutation type picklist |
||||||
| salutation / vtiger_leaddetails |
salutationtype | Salutation | 55 | V~O | varchar(200) | No |
| firstname / vtiger_leaddetails | firstname | First Name |
55 | V~O | varchar(40) | No |
| Salutation (for last name) |
||||||
| lastname / vtiger_leaddetails | lastname | Last Name |
255 | V~M | varchar(80) | YES |
| Checkbox | ||||||
| donotcall / vtiger_contactdetails |
donotcall | Do Not Call |
56 | C~O | varchar(3) | No |
| discontinued / vtiger_products |
discontinued | Product Active |
56 | V~O | int(1) | No |
| Contacts popup select box |
||||||
| contactid / vtiger_salesorder |
contact_id | Contact Name |
57 | I~O | int(19) | No |
| Campaign popup select box |
||||||
| campaignid / vtiger_potential |
campaignid | Campaign Source |
58 | N~O | int(19) | No |
| Product non-editable capture, popup picklist |
||||||
| product_id / vtiger_campaign |
product_id | Product | 59 | I~O | int(19) | No |
| Attachments, file selection box |
||||||
| name / vtiger_attachments |
filename | Attachment | 61 | V~O | varchar(255) | No |
| Duration minutes picklist – different typeofdata for the tab_id: 9 and 16 ??? |
||||||
| duration_hours / vtiger_activity (16) |
duration_hours | Duration | 63 | I~M | varchar(200) | No |
| duration_hours / vtiger_activity (9) |
duration_hours | Duration | 63 | T~O | varchar(200) | No |
| Names out of entities popup picklist |
||||||
| crmid / vtiger_seactivityrel |
parent_id | Related To |
66 | I~O | int(19) | No |
| Names out of entities popup picklist |
||||||
| parent_id / vtiger_troubletickets |
parent_id | Related To |
68 | I~O | varchar(100) | No |
| Products attachments |
||||||
| imagename / vtiger_products |
vtiger_products | Product Image |
69 | V~O | text | No |
| Date (for the created and modified date & time) |
||||||
| createdtime / vtiger_crmentity |
createdtime | Created Time |
70 | T~O | datetime | No |
| createdtime / vtiger_crmentity | createdtime | Created Time |
70 | V~O (??? for tab_id: 32 – ServiceContracts) |
datetime | No |
| Currency | ||||||
| annualrevenue / vtiger_account |
annual_revenue | Annual Revenue |
71 | I~O | int(19) | No |
| unit_price / vtiger_service |
unit_price | Price | 71 | N~O | decimal(25,2) | No |
| Popup select box for Accounts, mandatory entry [Calls JS function to auto fill billing and shipping address fields. Contact pop-up limited to only contacts related to the selected Account] |
||||||
| accountid / vtiger_quotes |
account_id | Account Name |
73 | I~M | int(19) | YES |
| Vendor name |
||||||
| vendor_id / vtiger_products |
vendor_id | Vendor Name |
75 | I~O | int(11) | No |
| Potential popup picklist |
||||||
| potentialid / vtiger_quotes |
potential_id | Potential Name |
76 | I~O | int(19) | No |
| Picklist for secondary username entries |
||||||
| inventorymanager / vtiger_quotes |
assigned_user_id1 | Inventory Manager |
77 | I~O | int(19) | No |
| Quote popup picklist |
||||||
| quoteid / vtiger_salesorder |
quote_id | Quote Name |
78 | I~O | int(19) | No |
| Sales order popup picklist |
||||||
| salesorderid / vtiger_invoice |
salesorder_id | Sales Order |
80 | I~O | int(19) | No |
| Vendor name, mandatory entry |
||||||
| vendorid / vtiger_purchaseorder |
vendor_id | Vendor Name |
81 | I~M | int(19) | YES |
| Tax in Inventory |
||||||
| taxclass / vtiger_products |
taxclass | Tax Class |
83 | V~O | varchar(200) | No |
| Role name popup picklist, mandatory entry |
||||||
| roleid / vtiger_user2role |
roleid | Role | 98 | V~M | varchar(255) | YES |
| Password, mandatory entry |
||||||
| user_password / vtiger_users |
user_password | Password | 99 | P~M | varchar(30) | YES |
| User capture popup picklist |
||||||
| reports_to_id / vtiger_users |
reports_to_id | Reports To |
101 | V~O | varchar(36) | No |
| EMail, mandatory entry |
||||||
| email1 / vtiger_users |
email1 | 104 | E~M | varchar(100) | YES | |
| User image |
||||||
| imagename / vtiger_users |
imagename | User Image |
105 | V~O | varchar(250) | No |
| Text box, mandatory entry |
||||||
| user_name / vtiger_users |
user_name | User Name |
106 | V~M | varchar(255) | YES |
| Non editable picklist |
||||||
| status / vtiger_users |
status | Status | 115 | V~O | varchar(25) | No |
| Currency in user details |
||||||
| currency_id / vtiger_users | currency_id | Currency | 116 | I~O | int(19) | No |
| hour_format / vtiger_users | hour_format | Calendar Hour Format |
116 | V~O | varchar(30) | No |
| ??? – Currency in modules – missing entries at http://wiki.vtiger.com/index.php/Ui_types |
||||||
| currency_id / vtiger_pricebook |
currency_id | Currency | 117 | I~M | int(19) | YES |
| currency_id / vtiger_invoice |
currency_id | Currency | 117 | I~O | int(19) | No |
| Admin toggle, checkbox |
||||||
| is_admin / vtiger_users |
is_admin | Admin | 156 | V~O | varchar(3) | No |
| Email, Popup picklist |
||||||
| idlistsi / vtiger_emaildetails |
parent_id | Parent ID |
357 | V~O | text | No |