Category Archives: Open Source Software

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.