>>>>Agreed, now here is an interesting situation. I'm logging calls and each call gets a new incremented number, begining with 1 for the first call of the month. I record a lot of detail against each call and my call form is modeless. I can receive another call before I complete the first one. The new call number is dependent upon the previous one which is in transaction. Further, I really want my call numbers to be sequence contiguously. But, if I cancel the first call, the second is in transaction and already has what it thinks is the next call number, 2. Hmm. not trying to be argumentative, this is a real situation.
>>>>
>
>
>>Hi Erik
>>Yes I agree with all of that and in fact I do have a timestamp on each record and a surrogate PK hidden from the user of course. The call ID is a visible number that the user knows the call by. This is a client request and I have to live with it and its really not out of the norm except that it gets tricky to handle the incremented number when more than one call can be in progress and any one could be cancelled. I guess this steps outside the issue of Transactions but the problem really arose for me when I was trying to use a primary key table to get the next PK. I realize that I have to get the key before I start the transaction. Of couse PK do not have to run serially, there's more than enough integers for all :)
>
>Stephen-
>I re-read your problem and realized that I missed the main point of the question: how to handle calls that are cancelled. I think the problem is not a technical, but a logical one. The only ways (logically) to handle the situation the way that you wish to is to not commit a record to the table until all calls that started earlier are commited (not really practical), or to accept the fact that the call id number could change after being commited. For example: A workstation receives a call at 3:00. Another workstation receives a call at 3:15. The second call completes first. The hard fact is that at the time you need to commit this record, you don't know what its number is, because it depends completely on whether or not the first call is logged or cancelled. The only way I can see to do this is to run a batch periodically on the list and assign each surviving call a consecutive number according to its timestamp. This means that a user can't use this number for anything, because it could
>change, either from a batch process or if an earlier call is finally completed.
>If I were faced with this, I would try to convince the client that this is a logically impractical solution, and he should settle for timestamps and non-contiguous id numbers.
Hi Erik
Yes I agree and Jim seems to have a similar approach. The problem really stems from the client wanting to run the sw just like the old paper system. Of course you can do anything you want on paper. It really becomes an issue of client training but you know sometimes you can't getem to budge. The real short answer here is that I have to deliver like yesterday and I didn't account for this in the fn specs Oops. Sounds like I need to package and sell it as a change request :-).