[Vtigercrm-developers] [vtigercrm-developers] UITYPE & TYPEOFDATA map for vtiger 5.x
Nicolas Larcipretti
niclarcipretti at gmail.com
Wed Apr 25 08:06:31 PDT 2012
Well, in fact those parameters work in a very weird way (IMHO) cause when
you create a new field using the max lenght, for instance, vtiger uses this
parameter to create the database field, but, instead of checking if the
user typed more then X chars, it simply "chops" the string and insert it
anyway (please guys, correct me if this happens because some deep shit I've
made to the system). So, if this behavior is ok to you, go ahead and be
happy :) cause for me it just wasn't.
I've tried to bring the validation issue up before to the community, but
they simply ignored me...I don't think they are worried with someone trying
to mess with their databases...pitty
Cheers
Nicolas
2012/4/25 raymondub <eltroll at gmail.com>
>
> Hi,
>
> thanks for your answer. No for me you are not paranoid at all ! I prefer to
> be protected too.. Vtiger a completely new application for the users, they
> are not used to such good one and so many functionnalities. They are using
> a
> lot of excel files and we had to implement macros to check what they were
> doind.. hell on earth ! I m really happy that i found vtiger !
> Regarding your solution, i have already made javascript validation but the
> problem is that i didn t know at all where i had to put them in vtiger. I
> was trying to find where was the validation of the email and to implement a
> custom new typeofdata but i believe that your solution will be easier :-)
> It ll be very great if you was able to send me an example :-)
> But i ve a question.. there a size for the field (ex table
> vtiger_contactdetails: firstname =>varchar(40) ) and a maximum length (ex:
> table vtiger_field`: tablename ='vtiger_contactdetails' =>firstname
> maximumlength = 100). Are you using that for your validation ?
>
> thx in advance,
>
> Patrick
>
>
> Nicolas Larcipretti wrote:
> >
> > Hi,
> >
> > In my case, I needed to add javascript and php validation (call me
> > paranoid, but when you throw a system like vtiger to the wolves, you
> > better
> > be prepared for everything lol), so my solution to that was to add jquery
> > and a custom js file to the vtiger's edit pages renderer files (that
> > solved
> > client side data validation) and I created an event to every module I
> > needed to make server validation before saving (that solved the server
> > side
> > data validation). If you're not familiar with any of those techniques, I
> > might be able to help you.
> >
> > Cheers
> >
> > Nicolas
> >
> > 2012/4/25 raymondub <eltroll at gmail.com>
> >
> >>
> >> Hi
> >>
> >> do you know how i could to implement custom validation for the fields in
> >> vtiger.
> >>
> >> For example: The length of a field must be max 30 characters
> >> I understand that for that i ll have to work with the
> >> vtiger_field.typeofdata..
> >> Cause i m sure that it s possible to make, it s already implemented with
> >> the
> >> email in contacts. If there s no @ that the email is not valid. I was
> not
> >> able to find the place of that check.
> >>
> >> thanks in advance,
> >>
> >> Pat
> >>
> >>
> >> Holbok István 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_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
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > _______________________________________________
> >> > Join vtiger community at http://forums.vtiger.com
> >> >
> >>
> >> --
> >> View this message in context:
> >>
> http://old.nabble.com/UITYPE---TYPEOFDATA-map-for-vtiger-5.x-tp29007047p33739514.html
> >> Sent from the vtigercrm-developers mailing list archive at Nabble.com.
> >>
> >>
> >> _______________________________________________
> >> http://www.vtiger.com/
> >
> > _______________________________________________
> > http://www.vtiger.com/
> >
>
> --
> View this message in context:
> http://old.nabble.com/UITYPE---TYPEOFDATA-map-for-vtiger-5.x-tp29007047p33746757.html
> Sent from the vtigercrm-developers mailing list archive at Nabble.com.
>
>
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20120425/cb92634c/attachment-0001.html
More information about the vtigercrm-developers
mailing list