Vertical Market Functionality


Slightly cryptic naming of this page .....

Many businesses require specific data entry fields against a service that they offer

eg. 1 A Hotel offering the service of accomodation, requires the night of the accomodation, the room number and number of guests, name of guest etc
eg. 2 A courier business offering the service of delivery of packages, requires the pickup address, destination address, weight of the packate, volume of the package etc

Similarly a business that sells items of a particular category may require specific fields for just that category. Our demo of airconditioners may require fields for kw output heating, kw output cooling, inverter, outside unit noise dBa, indoor unit noise dBa etc.

The specific fields may be required to define the item properly as static data against the item or they may be required to be entered on every sale of the item/service.

It occurs that a generic way to add fields for input at the time of order entry would be valuable to many such vertical market accounting applications and afford a very flexible set up that could cover many obscure situations. Where the field is required at the time of the order/invoice entry, then these fields should print out on invoices and order inquiries.

It also occurs that services of different kinds will have different fields that are appopriate we could define these service properties either at the item level or against the "stock category" - the word stock is used here to mean "item". I am not sure which level would be best. under the example above of a courier business the item might be

A4 courier bag accross town

but there could also be A4 courier bag accross 1 sector, 2 sectors, 3 sectors, A5, Foolscap etc - so it might make more sense to define a stock category that requires the extra fields and have all these items as part of a "courier bag" stock category and not have to define the properties of each item separately.

Similarly accomodation could be:

a deluxe room
a single room
self catering apartment etc

if these were all defined as members of the stock category - accomodation then the same properties would apply accross each service albeit that the pricing and description of each item would be different.


To code this I am thinking that we will need a couple of new tables:

CREATE TABLE stockcategoryproperties
propertyid int auto_increment,
categoryid int,
PropertyName varchar(50),
NumericValueExpected tinyint default 0,
MaximumLength tinyint default 50,


categoryid being a foreign key to stockcategories catid

we could extend this to have a field showing the possible values for the property if it was completed perhaps a comma separated list of the possible values stored as one field and exploded out into separate values as a combo box
Perhaps also a field to allow some kind of validation of the value entered ie >0 etc.



and we also need a

salesorderdetailsproperties
propertyid
salesorderdetailsid
propertyvalue varchar(50)


propertyid being a foreign key to stockcategoryproperties this would allow for an unlimited number of properties.

That's the easy bit since we now need an additional form to enter a stock categories properties - a relatively easy one and some additional forms in order entry - the most complex area of the system!

<Phil - 14/8/07>I have coded this up to the extent that we now have unlimited properties against stock categories and we can choose whether the stock category property creates fields for the input of new items of the category in the new item screen or whether it creates additional required order entry fields. This was released with version 3.07 and a couple of bug fixes into 3.071. I have not done the order entry modifications required to effect the vertical market service options ... always looking for sponsors though </Phil>

To enter item properties against orders we need an additional table similar in a way to stockitemproperties but that is referenced to a sales order line item as well - not forgetting that it is possible to have the same item on a sales order more than once.

CREATE TABLE `stockorderitemproperties` (
`stockid` varchar(20) NOT NULL,
`orderno` int(11) NOT NULL,
`orderlineno` int(11) NOT NULL,
`stkcatpropid` int(11) NOT NULL,
`value` varchar(50) NOT NULL,
PRIMARY KEY (`stockid`,`orderno`,`orderlineno`,`stkcatpropid`),
KEY `stockid` (`stockid`),
KEY `orderno` (`orderno`),
KEY `stkcatpropid` (`stkcatpropid`),
KEY `value` (`value`)
) ENGINE=innodb;

With each order item property we need to record the sales order and the order line number that the property relates to. Using just a single table would mean that a record is required for each sales order item property entered against a sales order. This could get to be a busy table - but only a single table is required irrespective of the stockcategory of the items and the same logic would be able to be used for any property/item. Not sure the best indexing to use though - maybe a unique auto_increment primary key is better.

We would need:
1 Modifications to allow entry of these properties similar to the code in Stocks.php against the line item order entry in SelectOrderItems.php - with potentially many properties for each item.
2 To think about how the value is error trapped and this might entail further fields in the definition of the property in stockcatproperties.
3 Code to display the order item properties on a packing slip/dispatch note - PrintCustOrder.php (preprinted stationery) and PrintCustOrder_generic.php. The order item properties could well have important information for the order fulfillment folks
4 Code to display the order item properties on invoices - PrintCustTrans.php and PrintCustTrans_Portrait.php
5 Code to display the order item properties on quotations - PDFQuotation.php

What about also - maybe having a similar properties against serial items - or batches. This is the next level down but the same advantages would apply to these.

Valid XHTML :: Valid CSS: :: Powered by WikkaWiki