Firstly, thanks for your interest in my query.

The department that requires this functionality currently generates a seperate 'order number' for each line item. This number is unique to the product and sequential. The documents themselves are then filed away in datal and order number sequence and retrieved based on the date and number..

The department only prints it's invoices at month end and these invoices could relate to 50 or more documents that have been approved and filed away over the course of the month, to file under any sequence  that would not be sequential in both datal and numeric terms would they feel be confusing, as clients ringing up often refer to the date of a document, but do not have many other details to hand, but  this date can be then checked on the system along with the 'order number'  and the document can then be easily found. 

The system could be changed of course but the department are happy with it and they certainly don't want a system that is harder to follow than what they already have. They  wish to avoid any confusion or misfiling in relation to lower numbered documents being stored in amongst more recent documents which have higher numbers, because the alternative number sequences offered to them so far are not truly sequential, with the possible exception of the TIMESTAMP field .

Firstly, thanks for your interest in my query.

The department that requires this functionality currently generates a seperate 'order number' for each line item. This number is unique to the product and sequential. The documents themselves are then filed away in datal and order number sequence and retrieved based on the date and number..

The department only prints it's invoices at month end and these invoices could relate to 50 or more documents that have been approved and filed away over the course of the month, to file under any sequence  that would not be sequential in both datal and numeric terms would they feel be confusing, as clients ringing up often refer to the date of a document, but do not have many other details to hand, but  this date can be then checked on the system along with the 'order number'  and the document can then be easily found. 

The system could be changed of course but the department are happy with it and they certainly don't want a system that is harder to follow than what they already have. They  wish to avoid any confusion or misfiling in relation to lower numbered documents being stored in amongst more recent documents which have higher numbers, because the alternative number sequences offered to them so far are not truly sequential, with the possible exception of the TIMESTAMP field .

It really comes down to two options:

• Create a process to generate the unique number when each order is placed.  This could be something in SQL that runs shortly after the fact, or maybe a TaskCentre task.
• Generate the number using information already provided by iMIS.

The second option may not be too horrible.  I hear that date is the first sort key, which you obviously can get from the order.

For the rest, you could use (ORDER_NUMBER * 10) + (LINE_NUMBER - 1).  (If you may have more than 10 lines, use 100.)  This would leave gaps, but you know for a fact there won't be duplicates.

Editing an existing order could throw off the sequencing if that's also important.

If it absolutely, positively has to be in sequential order with no gaps, you're almost definitely stuck with a custom solution.

--
Bruce Wilson
Director, Technical Services
RSM McGladrey, Inc.

Here's a sketch of an Order_Lines trigger that might help. 

CREATE TRIGGER Order_Lines_Insert
   ON  Order_Lines
   AFTER INSERT
AS 
BEGIN
 SET NOCOUNT ON;

 declare @newctr int
 declare @ordernum float
 declare @linenum float
 
 -- Using sp_asi_GetCounter will give you an integer counter value, auto-incremented with each call.
 -- If you want to use it, first, create a new row in the Counter table like this, which sets the initial value to 100:
 -- insert Counter (COUNTER_NAME, LAST_VALUE, LAST_UPDATED, UPDATED_BY, PAGEPAD1, PAGEPAD2, PAGEPAD3, PAGEPAD4, HAS_CHECKSUM)
 --  values ('DocumentID', 100, GETDATE(), 'MANAGER', 'PAGEPAD1', 'PAGEPAD2', 'PAGEPAD3', 'PAGEPAD4', 0)

 -- Note that this trigger will apply to all order lines as it's written. If you don't need a document ID for each record, don't want them for web orders,
 -- etc., then wrap this in logic to determine if a document ID is needed, based on the Product's category or whatever is appropriate.
 exec sp_asi_GetCounter 'DocumentID'
 select @newctr = LAST_VALUE from Counter where COUNTER_NAME = 'DocumentID'
 
 -- alternatively, set @newctr = (your business logic for the document ID)

 set @ordernum = (select ORDER_NUMBER from inserted)
 set @linenum = (select LINE_NUMBER from inserted)
 update Order_Lines
  set UF_1 = convert (varchar(30), @newctr)
  where ORDER_NUMBER = @ordernum and
  LINE_NUMBER = @linenum

END

Can I take this post as an indication that it is generally safe to use Orders.UF_1 as we wish?  All those extra, unused fields are a tempting target for things like this, but the risk of not remembering to test that before their next upgrade is a big concern.  Not looking for guarantees, but it's definitely encouraging to see you think the same way.

The alternative I would probably have used is to create a separate table with fields for ORDER_NUMBER, LINE_NUMBER, DOCUMENT_ID, and simply insert them there.
--
Bruce Wilson
Director, Technical Services
RSM McGladrey, Inc.