[Vtigercrm-developers] UITYPE & TYPEOFDATA map for vtiger 5.x

Holbok István holbok at gmail.com
Sun Jun 27 11:14:01 PDT 2010

Dear All,

I was a little bit confused with the issue of UITYPE and TYPEOFDATA, so 
I did a small map how UITYPE, TYPEOFDATA and SQL data type relate to 
each other.

This map in the form of HTML table is here.
The sign '???' means - I do not understand why that typeofdata was used.

This table uses the data from http://wiki.vtiger.com/index.php/Ui_types 

Best regards:

István Holbok
Phone: +3670-342-0900
e-mail: holbok at gmail.com <mailto:holbok at gmail.com>

A solution for the Gulf Coast cleanup: HARDOIL
Watch the video: http://environmental-us.com/hardoil/en/video.html

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20100627/6a541b6d/attachment-0002.html 

More information about the vtigercrm-developers mailing list