Counter problem

[TomBarrand]

Within a purchase order table I have a column that is a counter for each purchase order line. The counter is used as this data is being read into another system and to make it easier for the user the lines are incremented. The problem I have is that when a line is deleted from the table the counter will not be correct. Is there are update statement I can run after the delete, that makes the counters correct for a purchase order.

PONO Counter Descrip
1 1 Shirt
1 2 Trousers
1 3 Tie
2 1 Shoes
2 2 Shirt

 

[mukund]

Hi TomBarrand,
The best solution to this problem, is to delete all the records when a single detail record is deleted and then add the remaining records. This ensures that the Counter is always correct.
That is the way we handle it in our PODetail table. If any item of the PO is deleted, then all the items in the PODetail are deleted and the remaining items are inserted.

Hope this helps.
Mukund.

 

[jhall156]

It seems to me that information about deleted PO lines would be useful. Having a status field showing the status of PO details is an easy way to handle this. Using a char(1) field that could be blank for normal, 'X' for cancelled/deleted, 'V' for voided (even though it would be a violation of third normal form if this 'V' is in the header file) 'C' for received/completed etc. Also if your PO transactions leave any footprint in other files e.g. GL distribution or inventory transactions and the combination of your PO + line is a foreign key in those tables you could have a problem.