Vtiger Picklist 创建实例

Vtiger Picklist 创建实例 (OSSEZ.COM) CHS 技术参考, 2012-12-07 作者: YUCHENG HU OSSEZ LLC (USA) OSSEZ (中国) 信息技术有限公司 技术支持: http://www.ossez.com http://wiki.ossez.com 相关工作: 技术文档格式化版本 版本历史: 版本 日期 作者 描述 1.1 2012-12-07 YUCHENG HU 创建新版本 OSSEZ.COM-v1.0-技术 模板 个人简介word模板免费下载关于员工迟到处罚通告模板康奈尔office模板下载康奈尔 笔记本 模板 下载软件方案模板免费下载 简易版.ott 2012-12-07 版权所有 © OSSEZ LLC 2006 - 2012 1 / 5 目 录 1 创建实例....................................................................................................................................... 3 OSSEZ.COM-v1.0-技术模板简易版.ott 2012-12-07 版权所有 © OSSEZ LLC 2006 - 2012 2 / 5 1 创建实例 Please note that my instructions are based on MySQL, other databases will require their own table creation commands. You should have some basic level of database knowledge before proceeding. In this example I am converting the Inventory | Vendors | Category field. 1) The first step is to find the field you want to convert. The table vtiger_field contains all fields in the system. I ran a SQL query to list all fields in the vtiger_vendor table. SELECT * FROM `vtiger_field` WHERE `tablename` = "vtiger_vendor" It was very clear that category was the field I was looking for. Please note that it is the fieldname field that is you need. 2) Define the field as pick list. Field uitype defines the field type. In this case it is currently set to 1 which means text. Edit the vtiger_field record and change the type to 15 which means picklist. 3) Determine the unique picklist number for this new picklist. Table vtiger_picklist_seq contains the last know picklist number. Edit the one and only record in this table and increment the number by one and save the record. If the record is currently 30 then change it to 31. 4) Create a unique picklist entry. Table vtiger_picklist holds the relationship between the unique number and the field name. You will need to add a new record. picklistid = the number from the previous step (our example is 31) name = the name from step 1 (our example is category). 5) Create the tables needed to hold the picklist values. This step is a little technical. You will need to know how to create tables in your database system. OSSEZ.COM-v1.0-技术模板简易版.ott 2012-12-07 版权所有 © OSSEZ LLC 2006 - 2012 3 / 5 You will need two new tables, one for the picklist values and one to store the last record number. They are in the format of: vtiger_FIELD Where FIELD = picklist name from step 1 (our example would be vtiger_category). vtiger_FIELD_seq Where FIELD = picklist name from step 1 (our example would be vtiger_category_seq). The following MySQL code will create the picklist table for the category field. Note that every place you see the word category you would need to change it to your field name. CREATE TABLE `vtiger_category` ( `categoryid` int(19) NOT NULL auto_increment, `category` varchar(200) collate latin1_german2_ci NOT NULL, `presence` int(1) NOT NULL default '1', `picklist_valueid` int(19) NOT NULL default '0', PRIMARY KEY (`categoryid`), UNIQUE KEY `category_category_idx` (`category`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=18 ; The following MySQL code will create the picklist last record table. Note that every place you see the word category you would need to change it to your field name. CREATE TABLE `vtiger_category_seq` ( `id` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci; 6) Create a picklist valueid. Each picklist value has a unique ID. The last known value is stored in vtiger_picklistvalues_seq. To add a new one, increment the number by one and save the record. If the current record is currently 300 then change it to 301. 7) Add the first picklist value. Each picklist must have at least one item. Create a record in your new table (our example is vtiger_category) with your first picklist value. categoryid = 1 (the first picklist value) category = "picklist value" OSSEZ.COM-v1.0-技术模板简易版.ott 2012-12-07 版权所有 © OSSEZ LLC 2006 - 2012 4 / 5 presence = 1 (1=editable, 0=non-editable) picklist_valueid = (Value from the step 5, in our example it is 301) 8) Update the sequence table. Each picklist table has a corresponding table to hold the last known picklist entry. In our example it is vtiger_category_seq. Create a new record and set the value to 1. This is done because we only have one entry in the table for vtiger_category. 9) Get a list of roles for your organization. The final step is to define which roles can access this picklist entry. If you don't do the last few steps then you can not edit your picklist via the picklist editor. Table vtiger_role lists all the roles in your organization. Make a note of each roleid. In most standard installations they will be H1, H2, H3, H4, H5. 10) Make the picklist accessible for each role. Table vtiger_role2picklist contains a record for each picklist id and each role allowed to use it. Add a new record for each role you have in your organization. In our example there are 5 roles. In our example a record would look like: roleid = "H1" (the first roleid) picklistvalueid = valueid (in our example it is 301 from step 6) picklistid = listid (in our example it is 31 from step 3) sortid = 1 (it is the first picklist item) So the 5 records will be: H1, 301, 31, 1 H2, 301, 31, 1 H3, 301, 31, 1 H4, 301, 31, 1 H5, 301, 31, 1 Finally, login and verify the changes. Go to Settings | Picklist Editor and your new field should show up. You can now use the normal editing process to setup your new picklist field. luxuryoils Posts: 13 Joined: Sun Nov 09, 2008 3:14 am OSSEZ.COM-v1.0-技术模板简易版.ott 2012-12-07 版权所有 © OSSEZ LLC 2006 - 2012 5 / 5 1 创建实例