[Vtigercrm-developers] UITYPE & TYPEOFDATA map for vtiger 5.x
David V.
davidv.net at gmail.com
Sun Jun 27 11:43:58 PDT 2010
Thank you.
Nice piece of "reverse engineering" ! :-)
David V.
On Sun, Jun 27, 2010 at 2:14 PM, Holbok István <holbok at gmail.com> wrote:
> 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_typesalso.
>
> Best regards:
>
> István Holbok
> Phone: +3670-342-0900
> e-mail: 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
>
>
>
> _______________________________________________
> Join vtiger community at http://forums.vtiger.com
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20100627/29ee4c23/attachment-0002.html
More information about the vtigercrm-developers
mailing list