Is anyone finding records occasionaly disappearing/overwritten when using OI's seq key facility?

We have had user with four workstations (out of about 10 total)hammering in maintenance records for several years using OI's seq key facility for new record keys. Several times over the years they have complained that records are disappearing and being overwritten. If they were bankers, etc. the loss of even a single record in this way could be rather serious - but luckily they aren't bankers!

It seems that newish records are being overwritten by slightly newer ones. The users are clearly not simply overwriting every prompt on the screen in turn - so it must be an occasional system problem.

The users don't hammer in records continuously. Often they enter/edit a record, save it and then do something else before tackling the next record. If two or more do this then the same default key may be on two or more screens at the same time. We've tested this situation (and others) and OI gives the key to the first to lose focus from the key prompt and subsequent users get a "locked" message and save is disabled.

However, we still get these occasional reports - and UK Support imply we're still not the only people reporting occasional problems with the seq key facility.

I conclude that the most likely factor in the occasional glitch must be connected with having the same default key on several screens at the same time. I cannot think otherwise how this glitch could arise as record locking must prevent overwriting in all other circumstances.

The first attempt at an answer therefore seems to be to default to something like "New Record" and via a lostfocus process replace this with the next seq key (and update %SK%) only when the user grabs and locks the record.

Any relevant experiences, ideas or comments out there?

Thanks

Richard Guise

Tornado Property Systems Ltd.

The SEQKEY option is notorious and has been so since day one. There are numerous posts about its problems (which you have encountered) along with ways to fix it. Mostly it involves rolling your own routine. A replacement largely depends on whether you can stand "holes" in the sequence. The general principal is to assign the key at the time of save and not in the beginning (GOTFOCUS). At the time of save, you need to read the control structure (%SK%, see if the key is available). If it has been used for an actual record, lock the control, increment, check, etc. until you find a free value). Mostly, I use the temp key NEW which can never be saved. This is always free. Usually, I put up a MSG box telling the operator what actual KEY was assigned at save.

Don Miller

C3 Inc.

Don

Thanks. Back in the old days… I wrote my own SeqKey routine due to serious problems. However (fool I was!) I thought RTI had got it sorted. Since my posting Mike Ruane has admitted they get occasional reports - seems when lots of users are hammering in data. We have only four or five in this part of the system - but they do leave their screens sitting cleared with default key - thus several can have same default key at the same time.

By the way, I'm glad my bank don't use OI - maybe they've got other ways of losing my account / transactions / money!

I've hammered this one without reproducing any problem. Due to this and rarity of problem I surmise it must occur when more than two (3, 4, etc.?) have the same key.

Hence my default is "New Entry" with lostfocus which then finds and displays next available key (and updates %SK%) when user commits to edit new record (but can abort saving). By a little care it's possible to avoid gaps in the key sequence due to aborted creates.

However, it seems by key lostfocus system has already locked "New Entry" and recorded it in @ID, OrigResultRow@ and RowLocks@ - so these need to be changed.

I've only done this today but it seems to work and has the ingredients of a solution.

If anyone wants ay more detail on the routine (or a copy by email) please email me on rg@tornadopropsys.co.uk.

Regards

Richard Guise

Tornado Property Systems Ltd.

Richard,

Your description seems very similar to a problem that we originally reported over two years ago here. This was looked at by Cameron and Gene but it never got anywhere.

I then reintroduced the topic six months later in this thread. At this time WinWin was handling primary support and both Mike and Bob got involved.

To summarize, SEQKEY can be a problem, but not exactly for the same reasons that AREV had. In AREV's case, the problem was primarily related to losing the lock on the %SK% record in the dictionary which allowed multiple workstation to grab the same key. A fix for this has been documented, which was based on causing the main application menu to execute at a higher TCL level.

We suspected that AREV's problem would not exist in OI simply because the concept of execution layers doesn't exist in OI quite the same way it does in AREV. So far, AFAIK, this has held true. We have never seen a lock on %SK% get lost. Instead, our problem was a failure for the READ event to fire when a user accepted the default key too quickly after saving the previous record. This would result in a failure for the record itself to get locked. The end user wouldn't notice the signs that a new record had not been read (e.g. not seeing in the title bar) and would continue on to input data. At the same time, another user would successfully get the key AND begin inputting the new record.

The first user could never truly save the record because, as far as the form was concerned, no databound information had been entered. End users didn't know this either, they assumed the record was being written because they could clear the form without a savewarn message.

The moral of the story was that we took WinWin's advice and programmed our events more defensively so that end users were warned if they happened to be navigating the window without a record having been read. Others obviously resolve this by writing their own sequential key logic.

BTW, Revelation, in the second thread that I posted, this issue was logged into the SPR. Is it still hanging around there?

dbakke@srpcs.com

SRP Computer Solutions, Inc.

Richard

We also developed our own "SeqKey" routine.

We don't display a default at all, but require the user to press F6 (or hit the windows style new document button on the tool bar) when they are ready to enter a new transaction. Only at that moment do we lock %SEQKEY% record, allocate the value to the requesting user, update %SEQKEY%, and unlock it.

As you suggested, handling the case where the user decides they don't want it after all is not too difficult.

Robert Lee

Many thanks, Don B!

This makes more sense than Mike Ruane's emailed comment to me that OI seq key problems happen occasionally with large numbers of people continuously hammering in data, as this user has just 4 or 5 who aren't frantically intensive.

However, the user's IT people managed to slow very fast PCs/network to a slow crawl. With speed being the relationship between the user and the kit, it wouldn't have been at all hard for the user to do quite a lot before the system got round to doing much at all. In such a situation I'm surprised the scenario you describe wasn't more frequent.

However, they've now moved into new offices with a rocket-propelled network which their IT haven't had the chance to wreck yet. I'm hopeful therefore that they're OK now - at least for a while!

Despite this I find it astonishing and unacceptable that the software is able (albeit on slow kit) to lose data - and the more so that RTI haven't bothered to do anything about it or even to issue a clear health warning. Developers should not be expected to write around such basic known shortcomings - let alone find out about them only when their clients start to lose data.

Mike's emailed response to such sentiments was that tinkering with a well-established routine would "open a can of worms". I respect Mike and appreciate very much what he has done and is doing - but I really think in this instance he should put the integrity and safety of users' data a long way in front of his canned worms!

Don, Don & Robert

Just been back to the old thread of two years ago in which the problem seemed to be identified as failure of READ event when users were too quick.

I've just experimented and it seems that read event takes place BEFORE key prompt lostfocus. What my test procedure (see earlier posting in this thread) therefore does is to use lostfocus to change key AFTER the read event (in prompt, @id and OI window common variables ROWLOCKS@, etc.).

This gives the opportunity to set a flag in a read quickevent procedure (which happens after the read itself) and for the key prompt lostfocus to check and reset the flag, thereby ensuring that, if the read event is skipped, this is detected.

Anyone think of any fallacies or problems with this?

Should be able to close this one now.

However, if one is doing one's own seq key routine it's important to get the logic watertight to prevent ending up worse off. In this context some of the seq key logic I've seen on this forum has looked a bit dodgy.

Maybe it's worth outlining what I view to be the logic required to be safe and let any wise men (and ladies) correct me.

1) Lock and read value from %SK%

2) Check if record exists and, if not, increment value and retry until new record found.

3) Update %SK% if necessary and unlock %SK%

4) Lock new value and, if not, increment value. Try locking (to see if in use) and, if OK, try reading (to see if already exists).

5) Unlock new value and use.

In step 3 %SK% is reset to the value about to be used, bearing in mind that the user might abort and the number become available again.

Compared with some of the routines described I have added the read in step 4. This is because aborted saves (and %SK% resets, etc.) can cause gaps in the sequence, e.g. records with keys 11,13 & 14 may exist. User A gets 12 and, although 13 & 14 are unlocked, user B should get 15 as his new key. This will only be achieved if test reading as well as locking is included in step 4.

In view of the other topic (missed read process causing users to edit and save phantom records), I note the difference between two techniques (of which I am adopting the second) :-

1) Feeding a new key value into the key prompt - which is what the RTI procedure does and which seems to be associated with problems - as well as the inconvenience of a "locked" condition when (as is not infrequent) two user are offered the same key value.

2) Using a non-key default (such as "New Entry") and then changing the key as a postprocess on the key prompt (after lock and read). In this case, as well as changing the key on-screen, in @ID and in the screen's common variables, it is also necessary to lock the new key(or leave it locked - see above) and unlock the dummy "New Entry".

I hope this helps those addressing this subject.

If I meet unexpected problems I'll post details in due course.

Here's a procedure that I've used for a while in OI:

I have a Button control labeled "New Trans" or whatever.

When the user clicks this, internally, I assign the value "NEWREC" for the key. This can never be saved in any form.

The user fills out the form and then presses the Save button. There is also a Cancel button.

Then, before the write:

TOP:

LOCK DICTFILE,"%SK%" THEN

READV NEXT_NUM FROM DICTFILE,1 THEN

  INCR:

  READV JUNK FROM MYFILE,NEXTNUM THEN

• RECORD IS IN USE SO:

      NEXTNUM+=1

      GOTO INCR

  END ELSE

      LOCK MYFILE,NEXTNUM ELSE

• THIS SHOULD NOT HAPPEN BUT I ACCOUNT FOR IT ANYWAY

• SINCE ANOTHER USER COULD HAVE SET THE LOCK BEFORE UPDATE

         GOTO INCR:

      END

• set a flag to make sure that the save is ok

      OK2WRITE=1

      GOSUB CHECK_SAVE:  ;* this does a lot of work

      IF OK2WRITE THEN

         WRITE RECORD_CONTENTS ON MYFILE,NEXTNUM ELSE

• PROCESS THE SAVE ERROR

         END

• if the save cannot continue for a number of reasons, then

• don't use the key.

      END ELSE

         GOSUB PROCESS_ERROR

         GOTO EXIT_PROC

      END

      WRITEV NEXTNUM ON DICTFILE,"%SK%" ELSE

• PROCESS THIS ONE

      END

• UNLOCK ALL LOCKED RECORDS

      UNLOCK MYFILE,NEXTNUM

      UNLOCK MYDICT,"%SK%"

  END

END ELSE

• THIS WOULD BE A FATAL ERROR .. THE CONTROL ITEM IS MISSING

• BUT YOU COULD SET IT AND CONTINUE IF YOU'RE BRAVE

END

END ELSE

• WAIT LOOP

• I SOMETIMES USE A LOOP COUNTER TO SEE IF SOMEHOW THE SYSTEM IS

• FROZEN

GOTO TOP

END

In general, this procedure seems pretty bullet proof so far. Note that is useful particularly since in one of my tables I maintain two sets of counters (%SK1% and %SK2% to represent current week and next-week activity). The only diffrence is which control structure is checked.

Have a nice sip ..

Don M.

C3 Inc.

Don M

Thanks. I've glanced at your routine and apologise if, after a hard day, my mind is not at its sharpest. I think your routine has a "feature" which I don't think I'd include. If you can't read record but it's in use, then the routine won't increment but it'll loop until the record is freed. I would want to immediately go for the next key without waiting.

One of the issues is to deal with the situation that someones grabs a record and then aborts - in terms of how one updates %SK% allowing or avoiding gaps in the sequence.

Cheers

Richard

The way I do this, if gaps aren't allowed is to do all the action on the actual Save/Write. That way, if the user aborts the save, the next number won't be used. It will only be incremented when the actual save is performed. This won't help the problem of the user being allowed to delete a record. The ways I use to fix this are either:

1. An MFS that copies the original record to a "Saved" file which can then be reviewed, etc. In this case, I save the user/station info in the saved record. This leaves an audit trail to boot. This will cover the possibility of deleting from the Editor or TCL in AREV. OI makes it harder to do this outside of a form (assuming the S/LIST TCL is not available).

or

2. Disable the Delete function in AREV / OI Windows/Forms. This is quick and dirty, but has worked.

Don Miller

C3 Inc.

Don M

Thanks. As I said, I only skimmed. Finding the new key on the final save would indeed in many ways simplify several aspects (but you can't show it to the user during editing). However, you're also finding the key between the read and the write and, depending on the OI processing order (esp locking), I imagine you too will have to alter the screen's common variables and maybe @ID to suit.

I do similar things with MFSs (are we opening another line of discussion on the power and value of MFSs?). On my live data files I date/time stamp record creation, alteration & deletion; copy to archive file on deletion; read from archive file if no live record (and tag a flag field to indicate if an archive record); etc. I also have an MFS on the archive file's dict which does likewise so that the archive file's dict only has indexed fields and others are read from the live file's dict. Using these I can also switch my T-List report writer to use live or archive files or both (provided the files aren't too big to merge two select lists!). This latter is very useful indeed, as you can imagine.

Richard

View this thread on the forum...