vTiger Links

vtiger Open Source CRM

Developers How To’s – vtiger.com

Vtiger wiki FR – vtiger.com
French-vTiger
Vtiger CRM – Vtigerservices :: Vtiger CRM & gestion de la relation client
Vtiger Tutorial – Vtiger Emails
vtigerCRM-Spain Wiki 

vtlibrelated [vtigerCRM-Spain Wiki]

TSolucio, Consultoria Informática – Software libre
CRM – vtiger Open Source CRM – vtiger CRM Outlook Plugin
CRM – vtiger Open Source CRM – MS Excel Macro for data import by crm-now
CRM – vtiger Open Source CRM – Advanced Outlook® Plugin for 5.0.4 and 5.1
CRM – vtiger Open Source CRM – UTF-8 Converter for CRM data imports by crm-now
CRM – vtiger Open Source CRM – Google Gadget for vtiger CRM
CRM – vtiger Open Source CRM – Multiple Company Address & Terms & Conditions
CRM – vtiger Open Source CRM – Super Quick Telephone Search
CRM – vtiger Open Source CRM – Multiple From Addresses
CRM – vtiger Open Source CRM – Receipt Cards
vtiger.com: Issue Card and Receipt Card Modules: Liste de fichiers du projet
Creating new fields in a vtiger module | Enbake Consulting
vtiger520:User Manual – vtiger.com
vtiger CRM v5.x

vTiger, Taxes, VAT

If a product is taxable, the Tax checkbox must be checked in the product Pricing Information, otherwise, it will not be possible to set a tax in quotes or invoices.

There is a Tax Class field in the Product table, which does not seem to be used.

Taxe Types and Tax Rates are stored in table:

Table Field Type Null Key Default Extra
vtiger_producttaxrel productid int(11) NO MUL NULL
vtiger_producttaxrel taxid int(3) NO MUL NULL
vtiger_producttaxrel taxpercentage decimal(7,3) YES NULL

There is a line for each product for which Tax has been checked.

When one imports Products, there is apparently no way to import or set the Tax.

So I log into phpMyAdmin. I export the Products into a CSV, which gets me the productid

I create a new csv file with “productid”;”taxid”;”taxpercentage” value for each product.

productid comes from the vtiger_product table
taxid comes from the table vtiger_inventorytaxinfo
taxpercentage is the percentage: 17.000 as example.

I then load the csv file with phpMyAdmin into the table vtiger_producttaxrel.



Chinese fonts

We need to import Chinese texts into vTiger.

At present data is stored in excel files with the correct characters, but we have not found a way to export these characters to CSV files.

It appears that OpenOffice is much better than MS Excel when exporting to CSV files. OpenOffice asks for the parameters to use when creating the CSV.

You can then select the character set (encoding), the Field delimiter and the Text delimiter. So nothing to wory, just open the MS Excel file in OpenOffice Clac,

select the worksheet to export,

Click File -> Save as

Select the Text CSV format

Select Charater set UTF-8, delimiters and save.

Done

vtiger CRM 5.10 database table descriptions

To find out which are the fields that store prices, we have extracted the descriptions of all the tables found in vTiger CRM 5.10.

The description can be found in vTiger CRM 5.10 Tables descriptions

There are numerous places which store prices or money amounts. These are declared as decimal(x,y) :

Table Field Type
vtiger_inventoryproductrel listprice decimal(25,3)
vtiger_inventoryproductrel discount_amount decimal(7,3)
vtiger_invoice adjustment decimal(25,3)
vtiger_invoice subtotal decimal(25,3)
vtiger_invoice total decimal(25,3)
vtiger_invoice discount_amount decimal(25,3)
vtiger_invoice s_h_amount decimal(25,3)
vtiger_pricebookproductrel listprice decimal(25,2)
vtiger_productcurrencyrel converted_price decimal(25,2)
vtiger_productcurrencyrel actual_price decimal(25,2)
vtiger_products unit_price decimal(25,2)
vtiger_purchaseorder adjustment decimal(25,3)
vtiger_purchaseorder total decimal(25,3)
vtiger_purchaseorder subtotal decimal(25,3)
vtiger_purchaseorder discount_amount decimal(25,3)
vtiger_purchaseorder s_h_amount decimal(25,3)
vtiger_quotes subtotal decimal(25,3)
vtiger_quotes adjustment decimal(25,3)
vtiger_quotes total decimal(25,3)
vtiger_quotes discount_amount decimal(25,3)
vtiger_quotes s_h_amount decimal(25,3)
vtiger_salesorder adjustment decimal(25,3)
vtiger_salesorder total decimal(25,3)
vtiger_salesorder subtotal decimal(25,3)
vtiger_salesorder discount_amount decimal(25,3)
vtiger_salesorder s_h_amount decimal(25,3)
vtiger_service unit_price decimal(25,2)

vTiger 5.1 – Number of decimals in prices

We sell items in large quantities at very low unit price like 1,000,000 items at $0.1234, so a variation of $0.0001 is worth $100.
Out of the box, vTiger has a mix of 2 and 3 decimals to handle unit prices. Do not ask me why, but for example:

  • The product unit price has 2 decimals: decimal(25,2)
  • The price book unit price has 3 decimals: decimal(25,3)
  • Discount is decimal(7,3) which is fine for percentage, but problematic for amounts.

In order to extend the number of decimals, it is necessary to modify the type of the fields that store the price information in the database.
The example below is based on a standard installation under windows.
Open a cmd window.
Change directory to mysql.
cd C:\Program Files\vtigercrm-5.1.0\mysql\bin
In the line below, replace xxxxxx with your password to start mysql.
mysql -u root -pxxxxxx
Look at which databases are available and select the vtiger one.
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| vtigercrm510       |
+--------------------+
mysql>use vtigercrm510

List the tables available:
mysql>show tables;
The one we are interested in is the products table called vtiger_products.
We can find a short description of the vtiger data model in http://wiki.vtiger.com/index.php/Database_schema. A PDF file can be downloaded, but seems incomplete. Better information can be found here: http://forums.vtiger.com/download.php?id=1755
mysql> describe vtiger_products;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| productid        | int(11)       | NO   | PRI | NULL    |       |
| product_no       | varchar(100)  | NO   |     | NULL    |       |
| productname      | varchar(50)   | NO   |     | NULL    |       |
| productcode      | varchar(40)   | YES  |     | NULL    |       |
| productcategory  | varchar(200)  | YES  |     | NULL    |       |
| manufacturer     | varchar(200)  | YES  |     | NULL    |       |
| qty_per_unit     | decimal(11,2) | YES  |     | 0.00    |       |
| unit_price       | decimal(25,2) | YES  |     | NULL    |       |
| weight           | decimal(11,3) | YES  |     | NULL    |       |
| pack_size        | int(11)       | YES  |     | NULL    |       |
| sales_start_date | date          | YES  |     | NULL    |       |
| sales_end_date   | date          | YES  |     | NULL    |       |
| start_date       | date          | YES  |     | NULL    |       |
| expiry_date      | date          | YES  |     | NULL    |       |
| cost_factor      | int(11)       | YES  |     | NULL    |       |
| commissionrate   | decimal(7,3)  | YES  |     | NULL    |       |
| commissionmethod | varchar(50)   | YES  |     | NULL    |       |
| discontinued     | int(1)        | NO   |     | 0       |       |
| usageunit        | varchar(200)  | YES  |     | NULL    |       |
| handler          | int(11)       | YES  |     | NULL    |       |
| reorderlevel     | int(11)       | YES  |     | NULL    |       |
| website          | varchar(100)  | YES  |     | NULL    |       |
| taxclass         | varchar(200)  | YES  |     | NULL    |       |
| mfr_part_no      | varchar(200)  | YES  |     | NULL    |       |
| vendor_part_no   | varchar(200)  | YES  |     | NULL    |       |
| serialno         | varchar(200)  | YES  |     | NULL    |       |
| qtyinstock       | decimal(25,3) | YES  |     | NULL    |       |
| productsheet     | varchar(200)  | YES  |     | NULL    |       |
| qtyindemand      | int(11)       | YES  |     | NULL    |       |
| glacct           | varchar(200)  | YES  |     | NULL    |       |
| vendor_id        | int(11)       | YES  |     | NULL    |       |
| imagename        | text          | YES  |     | NULL    |       |
| currency_id      | int(19)       | NO   |     | 1       |       |
+------------------+---------------+------+-----+---------+-------+
33 rows in set (0.28 sec)

The field that we need to change in this table is unit_price, so we issue the following command:

mysql>ALTER TABLE vtiger_products MODIFY unit_price  DECIMAL(25,4);

In each price book, we have a specific price for each product.

mysql> describe vtiger_pricebookproductrel;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| pricebookid  | int(19)       | NO   | PRI | NULL    |       |
| productid    | int(19)       | NO   | PRI | NULL    |       |
| listprice    | decimal(25,3) | YES  |     | NULL    |       |
| usedcurrency | int(11)       | NO   |     | 1       |       |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

mysql>ALTER TABLE vtiger_pricebookproductrel MODIFY listprice  DECIMAL(25,4);

We have changed the fields in the database, so the price information will be stored with the proper number of decimals for our application.

The fields that we found are:

vtiger_inventoryproductrel: listprice decimal(25,3)
vtiger_inventoryproductrel: discount_amount decimal(7,3)
vtiger_productcurrencyrel: converted_price decimal(25,2)
vtiger_productcurrencyrel: actual_price decimal(25,2)

vtiger_productcurrencyrel:
adjustment       | decimal(25,3)
salescommission  | decimal(25,3)
exciseduty       | decimal(25,3)
total            | decimal(25,3)
subtotal         | decimal(25,3)
discount_percent | decimal(25,3)
discount_amount  | decimal(25,3)
s_h_amount       | decimal(25,3)

vtiger CRM 5.10 database tables

The list of the tables used by vTiger CRM 5.10 is available on this page:

vtiger CRM 5.10 Table list

How to do it yourself:

To extract the list into a file, open a cmd window. Change directory to
C:\Program Files\vtigercrm-5.1.0\mysql\bin

Create a file showtables.sql that contains the following lines:
use vtigercrm510;
show tables;

In the command line window, enter the following line, where xxxxxx must be replaced with your password for mysql:
mysql -u root -pxxxxxx < showtables.sql > tablelist.txt

Mysql will exectue the commands from showtables.sql and store the result in tablelist.txt

vTiger 5.1 notes

My notes on playing with vTiger CRM 5.1.

I hope this notebook will help others and share experience.