[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
also.
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
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
-------------- 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