• From: Philippines
  • Registered: 08/10/2015
  • Posts: 1

Topic: Auto Numbering on Item Code

Based from my initial test, Item and Sales Kit code should have a unique code.   I would say that it is like Part Numbering System by which from my understanding should be unique for every item.  Did anyone creates a code that automate the numbering of Item Code / Sale Kit?.  On Set-up Tab - Form Setup, I saw that the user can control the sequence of each document number but still has an option to put different number if the user want to.  Can we do that as well in Item Code numbering?  I am not that good in PHP, so hope to get answer from this forum.  Thanks.

  • From: India, TN, Karur
  • Registered: 05/16/2014
  • Posts: 1,195

Re: Auto Numbering on Item Code

Yes you can add auto item codes for it.  Check wiki there is some informations about it.

Subscription service based on FA
HRM CRM POS batch Themes

kvvaradha's Website

  • gj6n68
  • Senior Member
  • Offline
  • From: UK, India
  • Registered: 02/14/2016
  • Posts: 81

Re: Auto Numbering on Item Code

Hello,

Just wondering if this has been possible at all ?

I searched wiki but couldn't find anything about auto increment, though when I search in database via php, I can see there is option for auto increment, but if I try to apply it, I get an error.

This would be a helpful tweak if possible at all.

Thank you,
GJ

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Auto Numbering on Item Code

Each Sales Kit has a separate item_code of it's own in FA. The Sales invoice will only reflect the individual items and not the Sales Kit name. In fact, the item_codes table has some redundancy in the description of the Sales Kit name (see attachment)  that is duplicated for each constituent item in it. This normalisation issue may the cause of it's inability to provide the unique auto numbered item_code to the constituent items of a SalesKit when more than one constituent item exists. As the said table has a separate auto-numbered id field manipulating each entry individually is not hampered when just the id of the record is provided. But when several constituent items are inserted in sequence this will be an issue if the actual item_code is not properly coded into them and the next one is invoked each time.

Wonder which field or what logic distinguishes a SalesKit from a normal item......

@joe: this needs investigation.

Post's attachments

SalesKit_schema_data.png 19.8 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

  • gj6n68
  • Senior Member
  • Offline
  • From: UK, India
  • Registered: 02/14/2016
  • Posts: 81

Re: Auto Numbering on Item Code

Hello,

Though I am not fully at grips with the concept of sales kit and separate item codes within them, I believe if we were to provide a system like that in transaction references where there would be a defined prefix for sales kits and item codes, won't it make them both always unique ? And then even if they were used in combination, they would never conflict I believe.

For example Sales Kit = SK### and Item code = IC###

Even if they were combined ever, they won't conflict.

Now above can only be done if the codes can be both alphanumeric etc.

Hope I am making some sense.

Regards,
GJ

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Auto Numbering on Item Code

Actually the Uniqueness for the said couple of fields is enforced by an index. It could have been the compound primary key in which case your need would be satisfied.
FA for the most part has tables with just one single primary key to make it easy to code record manipulations (select, update,delete).

The fields stock_id and item_code are both generally VARCHAR(20 except a few of the former are CHAR(20) in all tables they are part of. The varied definition is probably an error or some index justification.

grep stock_id sql/en_US-new.sql

  `item_code` varchar(20) NOT NULL default '',
 `item_code` varchar(20) NOT NULL,
  `item_code` varchar(20) NOT NULL default '',

grep item_code sql/en_US-new.sql

  `stock_id` varchar(20) NOT NULL default '',
  `stock_id` varchar(20) NOT NULL,
  `stock_id` char(20) NOT NULL default '',
  `stock_id` varchar(20) NOT NULL default '',
  `stock_id` char(20) NOT NULL default '',
  `stock_id` varchar(20) NOT NULL default '',
  `stock_id` char(20) NOT NULL DEFAULT '',
  `stock_id` varchar(20) NOT NULL default '',
  `stock_id` varchar(20) NOT NULL default '',
  `stock_id` varchar(40) default NULL,
  `stock_id` char(20) NOT NULL default '',
Post's attachments

item_code_indexes.png 19.4 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

  • gj6n68
  • Senior Member
  • Offline
  • From: UK, India
  • Registered: 02/14/2016
  • Posts: 81

Re: Auto Numbering on Item Code

Sounds good so far.

So what would I need to do for the item codes to get auto populated and have auto increment ?

Is there a little piece of code which I can manipulate without disturbing the whole setup ?

Please let me know.

Thank you,
GJ

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Auto Numbering on Item Code

There is another issue that appears. When we try to add one more item to a SalesKit, the SalesKit (the one we are adding to) appears in the selection drop down box and we can actually get into a circular binge! If some other SalesKits are chosen then it may be okay and that too only if this current one is not part of the SalesKit we are adding.....This may not really be a problem if the Sales Kit being added to another Sales Kit gets populated by it's constituent items!

If we can replace the primary key id with the stock_id and item_code as a compound primary key in the item_codes table and re-code all the classes and functions to adhere to it, then maybe we can do what you want. It will probably break a few extensions as well!

@joe: good to have.

apmuthu's Website

9 Reply by barbarian (edited by barbarian 10/22/2017 05:28:13 am)

  • From: Indonesia, Japan
  • Registered: 01/08/2014
  • Posts: 190

Re: Auto Numbering on Item Code

For sales kit within sales kit problem, I think in the item code, there should be an additional column to indicate whether it is a normal item or a sales kit or just add additional table of sales kit that record the sales kit code. That way, the dropdown menu can be generated without including sales kit, if we assume sales kit can not contain another sales kit. On the other case, maybe just add some validation so that the user can not select sales kit that has same code as the sales kit that are being edited in the screen. It would prevent the user to create sales kit inception.

As for the auto increment, I will need to implement auto increment number for my project so in the future, maybe I will have some question or suggestion. In my mind now, I will create a table that hold the sequence number for normal item and sales kit. In the normal item form, I will query this table and when the item is inserted, I will increase the number in the database. The only problem is primary key duplication. That way you can define some rule, maybe give some prefix.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Auto Numbering on Item Code

The SalesKit circular issue is a bit more complex even if the self choosability of level 1 is implemented..
SalesKit SK1 is created to have some items.
SalesKit SK2 has some items.
SalesKit SK3 has some items and SalesKit1 as part of it.
SalesKit SK3 is now added to be part of SK2.
SalesKit SK1 will now be able to choose SK2 which in turn has SK3 with in turn comprises of SK1 itself!.

A complete self join based tree lookup with multiple 'inheritances' would need to be implemented.

So let us keep it simple even if slightly stupid!

apmuthu's Website

  • gj6n68
  • Senior Member
  • Offline
  • From: UK, India
  • Registered: 02/14/2016
  • Posts: 81

Re: Auto Numbering on Item Code

Hello,
I have to admit I didn't know the complexity of sales kit and he circular referencing.

Does this mean that we cannot at all apply auto increment just on item codes ? Or would it still be possible to create something like transaction references for item code field only, and let sales kit module work as it is ?

I mean instead of inputting the item code manually can't we automate it, how would that conflict with stock id convention.

apmuthu wrote:

The SalesKit circular issue is a bit more complex even if the self choosability of level 1 is implemented..
SalesKit SK1 is created to have some items.
SalesKit SK2 has some items.
SalesKit SK3 has some items and SalesKit1 as part of it.
SalesKit SK3 is now added to be part of SK2.
SalesKit SK1 will now be able to choose SK2 which in turn has SK3 with in turn comprises of SK1 itself!.

A complete self join based tree lookup with multiple 'inheritances' would need to be implemented.

So let us keep it simple even if slightly stupid!

  • From: Indonesia, Japan
  • Registered: 01/08/2014
  • Posts: 190

Re: Auto Numbering on Item Code

The problem here is I think if you want to auto increment both sales kit and item separately. Because of the database, sales kit and item can not have the same number. So, you can not have item with item code 1 and sales kit with item code 1. If you want to auto increment, you can not separate item and sales kit numbering. Use one counter for both item code and sales kit, then there will be no problem.

gj6n68 wrote:

Hello,
I have to admit I didn't know the complexity of sales kit and he circular referencing.

Does this mean that we cannot at all apply auto increment just on item codes ? Or would it still be possible to create something like transaction references for item code field only, and let sales kit module work as it is ?

I mean instead of inputting the item code manually can't we automate it, how would that conflict with stock id convention.

apmuthu wrote:

The SalesKit circular issue is a bit more complex even if the self choosability of level 1 is implemented..
SalesKit SK1 is created to have some items.
SalesKit SK2 has some items.
SalesKit SK3 has some items and SalesKit1 as part of it.
SalesKit SK3 is now added to be part of SK2.
SalesKit SK1 will now be able to choose SK2 which in turn has SK3 with in turn comprises of SK1 itself!.

A complete self join based tree lookup with multiple 'inheritances' would need to be implemented.

So let us keep it simple even if slightly stupid!