Topic: Add columns to gl_trans table
I would like to know which files I have to modify to have a new column created in the gl_trans table whenever a new company is created.
Thank you.
- apmuthu
- Moderator
- Offline
Re: Add columns to gl_trans table
Creating new fields in tables in FA's core will break upgrade ability.
Re: Add columns to gl_trans table
So if I want to add a new column where a given field will store a sort of specific ID, would it be better to write a module?
I will still use all fields from _gl_trans table plus the one that I want to add to create a view report.
Thanks for the help.
- apmuthu
- Moderator
- Offline
Re: Add columns to gl_trans table
Module is the best way to go in general. Where a report needs to be modified for use in all it's instances in the same company, then it would be better to keep the modified version in company/#/reporting/repXXX.php so that it is used when the appropriate report in the FA menu system is called. This is valid only if there are no extra parameters to be passed on from the report request form. If you need more form fields than are there in the default report request form, then make a module of it.
Give me an example of extra field you would want in the gl_trans table and you may be able to accommodate it elsewhere using the Non Field DB Data method explained in the wiki.
Custom modules for complex requirements can have their own tables linked on a 1:1 or 1:N basis with the core FA tables. This way, when an FA upgrade occurs, you will not be left standing high and dry.
5 03/20/2015 01:30:25 pm (edited by hunter12 03/20/2015 01:34:38 pm)
Re: Add columns to gl_trans table
I want to create an extra field that would store and ID (string) value. This ID would be unique for every transaction.
For example,
+----------------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+------------+----------------+
| counter | int(11) | NO | PRI | NULL | auto_increment |
| type | smallint(6) | NO | MUL | 0 | |
| type_no | bigint(16) | NO | | 1 | |
| tran_date | date | NO | MUL | 0000-00-00 | |
| account | varchar(15) | NO | MUL | | |
| memo_ | tinytext | NO | | NULL | |
| amount | double | NO | | 0 | |
| dimension_id | int(11) | NO | MUL | 0 | |
| dimension2_id | int(11) | NO | MUL | 0 | |
| person_type_id | int(11) | YES | | NULL | |
| person_id | tinyblob | YES | | NULL | |
|tax_id |varchar(15)|YES| | NULL| | |
+----------------+-------------+------+-----+------------+----------------+
The tax_id does not have anything to do with the way FA assigns ID or reference numbers. It is something that the user can choose to assign.
This is required by the country accounting policies. However, since not all transactions need to be given a tax_id, tax_id field can be left null.
In this way, if I decide to give a transaction a tax_ID, it can be stored and then used in a report.
I do not want to link the tax_id to a supplier (which I guess could be done and then just use one of the fields from supplier table in the report). The assignation of the tax_id has to be optional and occur when I´m creating and saving a new transaction. In this sense it would behave as a memo_ field, which can be left blank if a user wants to.
Thanks a lot for the help.
- apmuthu
- Moderator
- Offline
Re: Add columns to gl_trans table
This can be done using the the Non Field DB Data method used for Bin Location in Inventory. Just put in an optional piece of text like "TID#1254352" into the memo_ field and have it parsed and used like the BinLoc pseudo field in Inventory. This would indicate your unique Tax ID number 1254352.