It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → FA Modifications → Add custom fields into item
The data from my old system have some custom fields. Obviously, the item in FA does not have enough fields for that. Is there a way to do it or I have to do some coding myself? If it does need some coding, can someone point me to the right phps so I can look into it. Thanks!
FA does not have user fields suppot just now, so you are on your own. Most of database related code is stored in includes/db/* files, so if you want to make use of extra data fields you have to start your changes from here, and continue in user interface files (i.e. those called from main menu).
Janusz
I am wondering at the very beginning when FA creates the company during the installation process, where is the code to create all those tables in the db? I want to create those custom fields also when I create a new company. If I do the customization, might as well start it from the beginning instead of only for this company. I found out the table I need to modify is the stock_master table but it is only for this company though.
Put in ALTER statements at the end of the Chart of Accounts file (in sql folder) used and rename it to some custom name for future use in creating new companies with it.
I can add the custom fields into the stock_master table and also able to show the fields in items.php. But when I want to add an item, it won't because I have not updated the insert into statement yet.
Error message : Field 'long_description_1' doesn't have a default value
sql that failed was : INSERT INTO 2_stock_master (stock_id, description, long_description, category_id, tax_type_id, units, mb_flag, sales_account, inventory_account, cogs_account, adjustment_account, assembly_account, dimension_id, dimension2_id, no_sale, editable) VALUES ('P3612', 'Venice Blanco', 'df', 'dfw', 'gg', 'fgdsg', '1', '1', 'ea.', 'B','4020', '1520', '5020', '1520','1520','0')
I looked through the sql folder. It looks like it is only for creating tables when I create the company. It looks like it uses a function called submit center to add the new row. When I looked into this submit center function, it does not take me to the insert statement. Where is this insert statement supposed to be? Where is the location?
The function add_item is in inventory/includes/db/items_db.inc and the sql statement for the insert referred above is at line 49 onwards:
$sql = "INSERT INTO ".TB_PREF."stock_master (stock_id, description, long_description, category_id,
tax_type_id, units, mb_flag, sales_account, inventory_account, cogs_account,
adjustment_account, assembly_account, dimension_id, dimension2_id, no_sale, editable)
..
..From a design sustenance point of view, it might be better to have a separate table and link the primary keys on a 1-to-1 basis and use another insert statement in a new function that gets executed after this function's success. Then such scripts can be bundled into a separate Extension and installed into any FA instance. It will then possibly be compatible with future FA versions or be considered redundant if the functionality gets incorporated into the FA core.
I guess what you mean is to create an extra table for the custom fields and then link it up to the stock master table. Insert or update the stock_master table in the meanwhile also do it for the custom fields table. And at the end, all this work can be put in as a new extension. I will try to do that. I am pretty sure somebody will find it useful for some extra fields for the items.
I am looking into zh_HK-utf8.sql since this is where all the tables get created. I tried to create a table called stock_extra_fields and link it up to the stock_master table. In the stock_extra_fields, the primary key is called stock_extra_id and I should have that also in the stock master to create a 1 on 1 relationship. That's how I do it.
CREATE TABLE `0_stock_extra_fields` (
`stocks_fields_id` varchar(20) NOT NULL default '',
`long_description_1` varchar(200) NOT NULL default,
`long_description_2` varchar(200) NOT NULL default,
`long_description_3` varchar(200) NOT NULL default,
PRIMARY KEY (`stocks_fields_id`)
FOREIGN KEY (`stocks_fields_id`) REFERENCES 0_stock_master(`stocks_fields_id`)
) ENGINE=InnoDB ;
But the interesting thing is I don't see any other create table use REFERENCES to create relationship. How does FA create relationship for tables?
In addition, is there any docs to explain what each file and folder in FA does? What is its function? In that case, it will be a lot easier to study the code.
I tried doing this exact same thing a while back: from memory, you're really playing with the guts of FA, and the changes were required across many, many files, with lots of opportunities to break the system. I gave up in the end, it was just way too risky and time-consuming.
I would go with apmuthu's suggestion of a linked table, at least that way you're not providing yourself with such a headache at upgrade time.
FA has an includes folder in each major functionality's folder (sales, reporting, etc) besides a common one in the webroot. Each db folder in the major functionality's folders are basically models in the MVC architecture where themes serve the views, the actions coming from the URL GET parameters and the ACLs from the session variables.
Using a trigger to make the extra fields get inserted when the primary table's insert succeeds is one way to avoid writing php code.
As for docs, what little I have culled out is placed in the Wiki which would benefit from better organization as I have been left on a limb quite often when searching for my own nuggets hidden there in not very intuitive key word search terms.
Where possible, I have cross referenced in the wiki pages, the forum posts that pertain to it but not vice-versa very much.
Foreign Keys and references are due in FA v2.4 only.
If you don't use foreign key and references to link the table, how do you link all the tables with relationship? When I look at the Entity relationship diagram, it does have relationships between tables.
FA v2.3.x does not use the Foriegn Keys as yet (aka CASCADE ON DELETE, ON UPDATE, etc). It will be implemented only in FA v2.4. In PHP, the SQL statement will manually connect the tables using the JOIN syntax only. The keys will be there and the indexes will be available to speed up the execution.
The ERD is to show what links to what but is used to understand and frame the SQLs only.
Thank you for the clearance.
Successfully added the extra fields today. Thank you for all the help.
Wouldn't you want to share how you did it?
would you please share the code ??
i need to add Color , height & width to the item..
so is there some tutorial to do it? or some extension?
Regards.
here's how I did something similar without altering sql
I created 2 new items - item# none -and- item#003 (both editable)
the result you can see here: https://ibb.co/nNFpab
Then in rep109.php line 122 I replaced with:
if($myrow2['stk_code'] !="none" && $myrow2['stk_code'] !="003" ){
$rep->TextCol(0, 1, $myrow2['stk_code'], -2);
$oldrow = $rep->row; }
the result you can see here: https://ibb.co/nNFpab
so by adding item#"none" in your sales order, you can have as much info as you like without PDF file showing item# in file.
for better page view, you also might want to change line 52 into this:
$cols = array(4, 60, 300, 375, 400, 450, 490, 525);
this sounds good.. but i dont know if it works in my case...
what i want is a field when i add a new product.. where i can add color and width to each product i enter.. so i guess altering db is a must.
These are product attributes. You can make one item for each combination of colour and width. That way you will be able to manage it's stock individually too.
the workaround in your case might be: when you add new item, make sure "Editable description:" is checked. Than you can alter each item description as needed.
Otherwise you'll have to do what apmuthu said above.
@apmuthu
the items are purchased..
So how do i add item attributes so that at item creation i have multible text boxes to add the needed data for each item.
@cristiart
i am trying to add special field for each item.. i dont want to add color and width in the describtion.
maybe we could use item type and have special set of fields for each item type.
You need to create a new item for each combination of attributes. Otherwise, there is no place other than in the description to place your attribute specifics.
@apmuthu.
Thanks.. all clear now.
FrontAccounting forum → FA Modifications → Add custom fields into item
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.