Vtiger Custom Fields UITYPE (SQL Data/Column) list

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
Email 13 E~O varchar(50) No
Email 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
email / vtiger_contactdetails email Email 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 Email 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

One Comment on “Vtiger Custom Fields UITYPE (SQL Data/Column) list”

  1. Very nice post. I just stumbled upon your blog and wished to
    say that I have truly enjoyed browsing your
    blog posts. In any case I’ll be subscribing to your feed and I am hoping you write again soon!

Leave a Reply

Your email address will not be published. Required fields are marked *