Missing Records

[Kevin McDuff]

Help! I've been having some funny things going on with a table and I'm wondering if anybody knows what might be happening. The table is the parent table of a set and a couple of missing records have been discovered.

In the first case, I verified that the missing record had not been deleted nor overwritten (an old copy of the table showed the record number location of the record-it was the eighth record created of about a thousand and now the sixth and seventh record follow each other and the eighth is just not there). I'm sure the table was not packed because records that had been deleted earlier were still in the table. This one record, all of a sudden, just disappeared. Reindexing didn't help.

Today, another record turned up missing after the table's indexes were reindexed. But it wasn't just gone; instead, in its place, was an exact duplicate of the next record in the table (although the file number field in the record must be unique).

I'm very concerned about the stability of the data in a table where records just seem to disappear. Anybody have any idea what might be going on or experience anything like this themselves? Thanks.

[Tom Cone Jr]

RE: Missing Records

Kevin, when checking on things like this be sure to work with the default form or the default browse for your table. This will rule out glitches that may be associated with your own forms, thereby narrowing the range of possible errors.

Also, it may be helpful if you tell us a bit more about your indexes. Are any of them 'filtered indexes' by any chance?

How about your data entry form. Is it based on the set, and does it include a tabbed subform for the child table(s)?

Is data entry occurring over a network, or are you experiencing this problem on a single stand-alone workstation?

Last thought, how current is your version of Alpha Five? The latest official patch from the company is build 230 for vers. 4.03 I believe.

-- tom

[Kevin McDuff]

RE: Missing Records

Tom, thanks for helping again. For this problem, I have been working exclusively with the default browse, so that parts taken care of, at least.

As to your other questions:

I know of the problem of using filtered indexes, and, yes I do have some filtered indexes, which I haven't replaced yet with an alternate strategy. Basically, the indexes are on major fields (file number, last name+first name, opened date, but there is also a group of indexes on some of the same fields that filters out closed files; in addition, another couple of indexes orders the table in file number order, but uses the expression: IF(LEFT(FILE_NO,2)>="90","19"+FILE_NO,"20"+FILE_NO) to order the table chronologically taking into account Y2K since the first two characters of the file number begin with the last two digits of the year the file was opened followed by a hyphen and incremental numbering.

Data entry is indeed done on a tabbed subform based on a set. The eight tabs include: 1) parent table data re the file; 2) data about insured in one-to-one linked table; 3) data about claimants in one-to-many child table; 4) additional parent data; 5) a browse list of a one-to-many child table re: time; 6) a browse list of a one-to-many child table re: AR; 7) a parent memo field for notes; 8) buttons for printing reports. So it's a pretty complex set (although data entry is not done for time or AR on this form--those tabs are provided just for review purposes). Hope it's not too much.

The data entry is occurring over a Novell network. I had a network technician test the network this week for any possible issues and he found so (serious) problems.

I'm not on-site right now, but I'm pretty sure I've got them up to build 230. I'll check tomorrow though.

Let me know if there's anything else that would help. Thanks.

Kevin

[Tom Cone Jr]

RE: Missing Records

Kevin, I think you're dealing with a pretty complex form, with lots and lots of indexes. If there are ways to simplify things I would think it might help with stability and reliability. Dr. Wayne published an article called 'Simplifying your Application' or something close to that, which may help give you some ideas.

In my own work I force myself to use the absolute fewest indexes possible. Only those needed to navigate through the table or set during data entry, if possible. As you know each index file must be kept current as data entry occurs in any of your tables, from any workstation on the network. If the index is needed only at report time, I don't build one. I use queries instead. Yes, there's a bit of a delay while the query is built, and this delay happens each and every time, but I find, on balance, it works better. This may be different in other circumstances, especially on larger databases. Most of mine have fewer than 30,000 records in them.

Complexity is also something I try to avoid in the index definitions. My motto is keep it simple, keep it simple, keep it simple.

I swore off tabbed subforms for data entry a long time ago. Will use them occasionally for navigation, or for reference, but not for data entry. I never felt comfortable that I understood what was going on well enough to be able to anticipate all the ways the user might inadvertently save a record, by accidentally tabbing somewhere else... or by pushing a save record button expecting a child record to be saved, and wind up saving a parent record instead. It can probably be done, and you're probably doing it fine, but I don't even attempt it. This is something I am hoping will be covered in depth at the conference in December, by the way.

The duplicate record is especially troublesome. Others have reported similar things. You might search the message threads for 'duplicate record'. My own guess is that it's one of two things. It might be possible in some instances for a user to push a 'enter new record' button twice in quick succession. This possibility can be nipped in the bud if the button script disables the button, does its work, and then enables the button on the way out. The other guess is that the work station may get 'out of synch' with the server over the network connection. Maybe the network isn't fast enough or something, causing a record to be saved twice, perhaps when the network fails to acknowledge a sent packet, so the workstation sends again. Something like that, maybe...

I realize my ramblings haven't offered anything specific (and a lot of personal opinion!), and for that I apologize. Hopefully you'll see that they need a newer patch and all will be well. I'll keep my fingers crossed.

Good luck!

-- tom

[Kevin McDuff]

RE: Missing Records

Tom,

Don't apologize for your "ramblings." It's all very informative to me. I appreciate your help, as always.
Thanks.

Kevin