GLTags

Synopsis

The GL tag system is an innovative system in webERP that allows individual GL transactions with a tag, and then reports can be generated based on that tag. For example, an organisation may operate several motor vehicles, and incur expenses for those vehicles. GL accounts could be set up for those expenses, such as:

1. Motor Vehicle Fuel
2. Motor Vehicle Insurance
3. Motor Vehicle Maintenance
4. MOT testing

Expenses for each vehicle would be posted to these accounts, but to report on individual vehicles would be hard. To get around this problem, we would set up a tag for each vehicle. Then, when an expense is posted to the GL it would be tagged with the appropriate vehicle. At any time we would be able to report on the ongoing costs for each vehicle.

Limitations of this system

The current tag system as outlined above has 2 limitations.

Firstly the number of tags that can be created is limited to 256 by the data type use in the tags table.

Secondly only one tag can be applied to each GL transaction. For example, to extend the above example, the organisation may be engaged in different projects, and vehicles can temporarily be assigned to those projects. It would be helpful to be able to tag an expense transaction with both the vehicle tag, and the project tag, and so be able to produce two reports, one for the vehicle and one for the project.

Solutions

Getting around the first problem is simply a matter of changing the type of the tagref field in the tags table from TINYINT to INT(11). This would allow for a huge number of tags to be created. The required SQL would be

ALTER TABLE tags CHANGE COLUMN tagref tagref INT(11) NOT Null;

The second issue will be solved by creating two new tables:

CREATE TABLE `gltags` ( `counterindex` INT(11) NOT NULL DEFAULT ‘0’, `tagref` INT(11) NOT NULL DEFAULT ‘0’, PRIMARY KEY (`counterindex`, `tagref`), FOREIGN KEY (counterindex) REFERENCES gltrans(counterindex), FOREIGN KEY (tagref) REFERENCES tags(tagref) );

This would look something like:

counterindex	tagref
12345	5
12346	114
12346	125
12346	138
12347	8
12347	9

The counterindex field refers to the counterindex field in the gltrans table. So in the above table transaction 1235 only has the tag 5 attached to it, transaction 12346 has 3 tags associated with it: 114, 125 and 138, and transaction 12347 has two tags associated with it, 8 and 9.

and..

CREATE TABLE `pctags` ( `pccashdetail` int NOT NULL, `tag` int NOT NULL, PRIMARY KEY (`pccashdetail`,`tag`) )

which would look like:

pccashdetail	tag
276	5
277	114
277	125
277	138
278	8
278	9

This allows for an almost unlimited number of tags to be created, and an almost unlimited number of tags to be associated with each transaction.

Updating current system

We need to transfer the old tags to the current system. As there could only be one tag pre GL transactions it is simply a matter of copying over the counterindex/tagref pair from gltrans to the new gl tags table by:

INSERT INTO gltags (SELECT counterindex, tag FROM gltrans);

and then removing the tag column from the gltrans table

ALTER TABLE gltrans DROP COLUMN tagref;

Scripts to change

1. CustomerReceipt.php
2. GLAccountCSV.php
3. GLAccountInquiry.php
4. GLAccountReport.php
5. GLJournal.php
6. GLJournalInquiry.php
7. GLTagProfit_Loss.php
8. GLTags.php
9. InternalStockRequestFulfill.php
10. Payments.php
11. PcAuthorizeExpenses.php
12. PcClaimExpensesFromTab.php
13. PcExpenses.php
14. StockAdjustments.php
15. SuppTransGLAnalysis.php
16. SupplierCredit.php
17. SupplierInvoice.php
18. includes/DefineSuppTransClass.php