How would you approach this client request

[NNlogistics]

I have been in the process of developing an Order entry Application. It's
been coming along quit well. A unique product ID was hashed out, all the
associated forms, tables and queries are complete. Now the client has asked
for the ability to be able to add a line item to an order with no Product ID
which means no pricing, description, etc. He will be using for a catch all.

I not sure how to approach this. It comes out of left field. Has anybody
dealt with this before. I probable need some help in making this adjustment
in think, without tearing up anything I just completed.

Hope this makes sense and this is the proper venue for this type of question
Thanks for any assistance

 

[Allen Browne]

Presumably you have an OrderHeader table, along with an OrderDetail table
for the line items. The OrderDetail will need fields like this:
- OrderDetailID AutoNumber primary key
- OrderID Number relates to OrderHeader.OrderID
- ProductID Number relates to Products.ProductID
- Quantity Number how many
- UnitPrice Currency price each
- Descrip Text description to use for this row.

In this table, OrderID will be a required field (so you can't have a line
item that belongs to no order), but ProductID is not required. You can still
create the relationship to the Products table with referential integrity,
even though the foreign key is not required.

Now you need some code in the AfterUpdate event procedure of the ProductID
combo in your subform. This code will look up the current price of the
product and its description, and drop them into the 2 fields.

You really need the UnitPrice anyway: otherwise all your exising orders will
go wrong on the day you change the price of a product.

 

[Paul Shapiro]

What about adding an Order.descriptionForLineItem attribute? Report
generation would be a little messier, because you'd probably want to include
this field at the end of the order item list in your invoice.

Or you might be able to allow nulls on OrderItem.productID, but then you
need to use something other than (orderID, productID) as the OrderItem
primary key. Maybe (orderID, lineNumber) would be ok? You could add a unique
index on (orderID, productID) and set it to ignore nulls. That way they
could add as many extra line items as they want but would still be
restricted to a single line item per product in any particular order.
Paul Shapiro

 

[NNlogistics]

--
Thanks for any assistance

Presumably you have an OrderHeader table, along with an OrderDetail table
for the line items. The OrderDetail will need fields like this:
- OrderDetailID AutoNumber primary key
- OrderID Number relates to OrderHeader.OrderID
- ProductID Number relates to Products.ProductID
- Quantity Number how many
- UnitPrice Currency price each
- Descrip Text description to use for this row.

In this table, OrderID will be a required field (so you can't have a line
item that belongs to no order), but ProductID is not required. You can still
create the relationship to the Products table with referential integrity,
even though the foreign key is not required.

Now you need some code in the AfterUpdate event procedure of the ProductID
combo in your subform. This code will look up the current price of the
product and its description, and drop them into the 2 fields.

You really need the UnitPrice anyway: otherwise all your exising orders will
go wrong on the day you change the price of a product.

Thanks Allen

You got me back on track.