webERP Forum
Adding some dates - Printable Version

+- webERP Forum (
+-- Forum: webERP Discussion (
+--- Forum: Development Discussion & Specification (
+--- Thread: Adding some dates (/showthread.php?tid=257)

Pages: 1 2 3

Adding some dates - PakRicard - 06-06-2012

Hi all:

We are thinking about adding some dates to webERP as:

1) Creation date of one item
2) last date of change of stock category

It is pretty easy to code. The question is if it makes sense to keep this info for the majority of us. Will allow us kind of questions like "For how long have we had this item in this category?

P.S.: Yes, I know it is a do'ocracy, but prefer to ask and get beter ideas or approaches before I code them.

RE: Adding some dates - opto - 06-07-2012

I think that is a very good approach.

I would wish createddate, updateddate and updater id on every table.


RE: Adding some dates - PakRicard - 06-07-2012

(06-07-2012, 07:56 AM)opto Wrote: I would wish createddate, updateddate and updater id on every table.


Hi Klaus:
I was thinking more on a business approach than an audit approach (already solved). You will get full details on "who did what when" on setup - Audit trail.

My idea was more to get the update dates for some strategic fields.

As example when we add an item to our range it goes to stock category "test". After some time, its sales performance is evaluated, and then it's decided if it is moved to a "stable" stock category or to a "soon to be obsolete" category.

To decide this, we need the date of change of stockcategory, and the creation date of the item.

Creation date of the item could be done with the date of the first stock reception for that item. Change of category shoud be registered in a new field.

RE: Adding some dates - phil - 06-07-2012

I was thinking about adding a date changed field to stock master (prices and customer master) too... my driver was to only download to and update my POS system with those records that had changed - thus reducing the overhead of master file refreshes - as I could get a stock list of items where change date > the date of the last refresh.

I am a little unsure about the wisdom of adding a date changed stock category field as where would one stop, date changed description, date changed mbflag, date changed barcode, date changed etc of course all of this info can be determined already from the audit trail and it would double the size of the database with largely redundant data. I guess I would prefer not to do this.

RE: Adding some dates - PakRicard - 06-07-2012

Hi Phil:

How could I get the date of change of stock category from audit trail? I'm open to use it, but I guess it does save the full update, in audit trail we do not know which field from stockmaster table was changed, right? Or is there any SQL trick to get it?

Really I'm just interested in this date (and see your point that we'll open the door to many dates to be stored on the DB), so anyway to get it would be great, without forking my own Stocks.php that will do my life miserable at every change of versions.

RE: Adding some dates - phil - 06-07-2012

In the view audit trail you can search the table stockmaster and in the containing text you could enter the stockid=code it should return only the insert update and deletes of that item .... it would be nicer to add a search by TYPE so you could just get updates of the stockmaster for that item code... but better to do this me thinks through the data we already have rather than add mostly redundant fields to the stockmaster.

Looking at the audit trail view - this does need a bit of work to tidy it up - but the bones are there to build on.

RE: Adding some dates - PakRicard - 06-08-2012

Yes but on the audit trail option:
a) if only interested on changes of categoryid field, how come can be differenciated from changes in any other field? The update record is kept as SQL, and it is the same if you change 1,2, 3 or all the fields on teh query, no it won't work I guess.

b) also at stockmaster table we already have one of these date fields lastcostupdate, so I think one more will not cause to much harm. This lastcostupdate is very helpful, and the one proposed will be as well.

So I still think the new field option on stockmaster is the best one.

Any more POV's?

RE: Adding some dates - PakRicard - 06-09-2012

(06-07-2012, 06:26 PM)phil Wrote: Hi Ricard,

As mentioned on the forum, I would prefer not to add fields for date changed field - instead upgrade the functionality in the Audit trail please.


Hi Phil:

I have been thinking about it in detail and (to my knowledge), the best option is to add a field instead of the Audit approach:

1) Currently we already have one of these fields (lastcostupdate) and seems to be working fine and it is useful. No one complained about its existence so far. Particularly we use it extensively.

2) The Audit approach to retrieve information (other than for audit purposes) has a severe time limitation. After X months the information is deleted, so after X months we couldn't retrieve the information. Solution could be a no time limit for audit but the table would grow immensely (we add around 200.000 records to the audit per month).

3) We are not approaching any mySQL limit regarding number of fields per table, etc on stockmaster table, so should not be a problem adding one (or few more) date fields.

4) Seems logical that performance will not suffer for the cost of adding some fields. On the contrary, having an very large audit table with some milions of records will cause some performance problems retrieving data.

5) The audit function works fine for audit purposes (who did what when), but I do not think it is OK for business data extraction.

6) If the idea is to keep all changes on the audit table, what will we do with the prices table? We also keep old prices with their dates. Theoretically could be retrieved from the audit as well. Will we move into this direction? Also the same could happen to purchdata.

7) I agree that adding some fields will increase a bit the size of the database, but will keep the code easy to read and less lines to maintain. Disc bytes are cheap nowadays but brains to code aren't. So it seems wise to keep the simple code we have better than save some cents on disk usage.

8) I could not find a single technical or business reason against the option of adding a useful field to one table.

Well, I'm open to change my mind if I'm wrong. Any other developers agree? Disagree? What could be improved?

RE: Adding some dates - phil - 06-09-2012

If I had added a datechanged field for every field in every table this is just a foolish waste of space IMHO and creates a whole lot of wood which obscures the trees. What about the change before last change lets add another field? No much better to improve the audit trail search facilities IMHO and only record data that is relevant - I really hate to have fields which are rarely used. This is why we don't have 25 user defined fields and went the way of stockcateroryproperties. Yes disk is cheap - but there is no excuse for sloppy poor design with wads of redundant data.

Why do you wish to record this? Perhaps I am missing some important business logic?

RE: Adding some dates - PakRicard - 06-09-2012

(06-09-2012, 06:21 PM)phil Wrote: Why do you wish to record this? Perhaps I am missing some important business logic?

Hi Phil:

Business logic behind this is it is a simple way to control the life of the products.

Let's say we have 3 main stock categories (well, some more but for the sake of simplicity the example will work with 3):

a) TEST products. It contains all new products during the 1st part of their life. Its sales performance, quality issues, customer comments, etc are evaluated during this first stage on few selected locations or customer types. Could be seen as the "junior" stage. Products have been selected and ordered by the product managers, but we have no clue how will they perform.

b) ACTIVE products. It contains the majority of the products of the company. These are the already tested, we can order or produce in bigger quantity as the product was been already tested, etc. Could be seen as the "senior" stage. We expect them to behave in a certain sales performance and below average returns or problems.

c) BAD products. All products have a life span. So, when its sales performance becomes worst than before, or it becomes old fashioned or is cannibalizing any other products or any other business reason it is time for the company to get rid of it and not to be reordered never again.

To have the products structured in stock categories also allows to control the value of each stock easily via the balance sheet and see from the financial POV which how is the inventory categorized. .

Some of the most difficult questions in retail business are:
When is it time for a TEST product to become ACTIVE?
Or should we move it directly to BAD because was not a good choice of the product manager?
When is it time to retire an ACTIVE product into BAD and start to get rid of it?

To answer these questions we need to know for how long the product has been in its category.

So this field is the first stage needed for some "optimization stock" scripts as:

a) List of items that have been in Z category for more than X days with poor sales performance. Useful to move from TEST to BAD or to ACTIVE to BAD due to poor sales.

b) List of items that have been in Z category for more than X days with more than Y returns. Useful to move from TEST to BAD or to ACTIVE to BAD due to customer or technical complains.

c) List of items that have been in Z category for less than X days with good sales performance. Useful to move from TEST to ACTIVE due to good performance.

d) List of items in BAD category for more than 365 days. If it is a bad product and you still have it after a long period, it is probably better to destroy it, sell it as scrap material, reuse some components, etc than to keep it.

Well, as I said it will help to control the life cycle of products. In brick and mortar retail businesses, the optimization of shelf space and optimization of the stock is key top success.

Hope it is clearer now. Or is it any other way to do these kind of controls in webERP now without this new field?