Record Disappearing ...?

Delete the indexes and rebuild them. Before you rebuild them and after you
delete the mdx, pack the table. Does the problem become immediate on its
return?
--
Christopher F. Neumann

[dBASE Gold Charter Member 107]
Blue Star Visual dBASE graduate
IWA-HWG Web Technologies Certified
IWA-HWG Web Programming Certified
ICCP TCP/IP Network Analyst
Data Communications Engineer
IEEE-CS Affiliate
http://cfneumann.us

"Just hanging out on the Sagittarian arm of the Milky Way"

I have a complex form that does Quotes, and eventually transfers them when
the customer follows-through to an Invoice.
In some cases, if items are in stock, and the customer wants just those
(the rest when they are in stock), the user can click a button, and copy
just some of the data to an Invoice, and the rest stays in the quote (the
items in stock are converted to an invoice).
The client has noted that sometimes (apparently randomly), the old quote
disappears.
When he packs the database and reindexes the tables (a long process, as
the reindex routine hits all tables -- it's the Rebuild Indexes routine
created from the dUFLP, and one table has over 1million records in it ...
argh), the wayward quote reappears.
Weirder, if he runs the pack routine (database.packTable( ...)), sometimes
the data in the indexes appears out of sequence.
Any ideas? Thoughts? This is disturbing. The client is trying to run a new
(well, relatively) system parallel to the old DOS system I upgraded, and
it's disconcerting to have these odd things happen.
I realize that without seeing the code (and it's a bear ... trust me --
this form is a monster), it's hard to second guess what is happening, but
I thought I'd see if anyone had any ideas based on the description.
Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/
*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Ken Mayer [dBVIPS]

2008-11-13 13:32:47 UTC

Post by Greg Hill
Make sure that all resulting EOF navigation is well managed and that no
records are committed at such time of unexpected eof()
I had a similar problem over a year ago but I have not encountered it since
I made some adjustments where potential eof saves can happen.
Hopefully that makes sense.

Hmm. I have an error log entry (the client sent me three entries from
11/3), one of which gives an "Error: In use by another ..." on the
quotes table. Unfortunately, this doesn't tell me anything about what
was happening. The statement is an "active=true" statement in the
constructor code. Not real helpful.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Lysander

2008-11-13 13:59:33 UTC

When he packs the database and reindexes the tables (a long process, as
the reindex routine hits all tables -- it's the Rebuild Indexes routine
created from the dUFLP, and one table has over 1million records in it
.... argh), the wayward quote reappears.
Weirder, if he runs the pack routine (database.packTable( ...)),
sometimes the data in the indexes appears out of sequence.

You get exactly that behaviour if - somehow - a "re-use" of key fields
takes place. For example you insert a record with a unique "order-ID",
delete that record, and insert another one with the same order-ID...

The outcome is as following:
- you don't find records by "seek" or "rowset.find()" although they are
there
- you see records that should not be there
- records seem to be lost, although they are there.
- an indexed search will point to the wrong record

All this happens sometimes, but not always, which makes finding the
source of the problem considerably hard.

If this sounds familiar, then double-double check whether it is
impossible that the same record get's entered twice.

Greg Hill

2008-11-13 18:30:58 UTC

You get exactly that behaviour if - somehow - a "re-use" of key fields
takes place. For example you insert a record with a unique "order-ID",
delete that record, and insert another one with the same order-ID...
- you don't find records by "seek" or "rowset.find()" although they are
there
- you see records that should not be there
- records seem to be lost, although they are there.
- an indexed search will point to the wrong record
All this happens sometimes, but not always, which makes finding the source
of the problem considerably hard.
If this sounds familiar, then double-double check whether it is impossible
that the same record get's entered twice.

You jogged my memory. That is true, if I recall correctly I fixed it by
adding another field to the index that made it unique.

Greg Hill

Ken Mayer [dBVIPS]

2008-11-13 23:12:04 UTC

When he packs the database and reindexes the tables (a long process,
as the reindex routine hits all tables -- it's the Rebuild Indexes
routine created from the dUFLP, and one table has over 1million
records in it .... argh), the wayward quote reappears.
Weirder, if he runs the pack routine (database.packTable( ...)),
sometimes the data in the indexes appears out of sequence.

You get exactly that behaviour if - somehow - a "re-use" of key fields
takes place. For example you insert a record with a unique "order-ID",
delete that record, and insert another one with the same order-ID...
- you don't find records by "seek" or "rowset.find()" although they are
there
- you see records that should not be there
- records seem to be lost, although they are there.
- an indexed search will point to the wrong record
All this happens sometimes, but not always, which makes finding the
source of the problem considerably hard.
If this sounds familiar, then double-double check whether it is
impossible that the same record get's entered twice.

Hmm. It shouldn't be possible. I've put a lot of work into that ...

Sigh.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Ken Mayer [dBVIPS]

2008-11-14 13:32:12 UTC

The most frustrating thing is that the client is sending me some
"step-by-step" bits to follow and I cannot get the program to reproduce
the behavior ... sigh.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Bruce Beacham

2008-11-15 08:37:27 UTC

Post by Ken Mayer [dBVIPS]
The most frustrating thing is that the client is sending me some
"step-by-step" bits to follow and I cannot get the program to reproduce
the behavior

Has he given you the starting data, so your changes are applied to
exactly the same files?

Multiuser?

Bruce Beacham

Ken Mayer [dBVIPS]

2008-11-15 14:35:39 UTC

Post by Ken Mayer [dBVIPS]
The most frustrating thing is that the client is sending me some
"step-by-step" bits to follow and I cannot get the program to
reproduce the behavior

Has he given you the starting data, so your changes are applied to
exactly the same files?

Unfortunately, no. It takes him awhile to get to things like that.

Post by Bruce Beacham
Multiuser?

Yes.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Ken Mayer [dBVIPS]

2008-11-15 15:21:30 UTC

Post by Ken Mayer [dBVIPS]
The most frustrating thing is that the client is sending me some
"step-by-step" bits to follow and I cannot get the program to
reproduce the behavior

Has he given you the starting data, so your changes are applied to
exactly the same files?

He just sent me a copy of the table in question, but ... I'm out of town
today. Figures. On the way out now ... timing.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Ken Mayer [dBVIPS]

2008-11-19 01:11:56 UTC

After looking at the table from the customer, I figured out the problem:

The table uses a UNIQUE index on a QuoteNumber field, which is used to
find a Quote. The reason for this is that the line items for the quote
are in the same table as the main quote data (poor design, but it would
take too much time to fix to a better data model).

Problem is that the user can:
** Turn the Quote into an Invoice
This deletes the Quote from the Quotes table, and copies to the
Invoice table (This is great if the customer goes forward with
the order ...)
** However, in the Invoice table, the user can then VOID the invoice
(Assume the customer wants to wait, or ... -- Invoice must
be processed in some fashion ...)
Which returns it to the Quote table, using the original Quote number.

However, the Unique index tag in the .MDX file is remembering the
currently still existing (due to Soft Deletes in the table) record with
that quote number, and adding the quote back in does not update the
index. Therefore a search points to the deleted record, which cannot be
displayed.

The only fix that I can see at this time is to pack the table. This is
problematic because it's a multi-user system ... sigh.

Anyone see any OTHER way around this?

TIA --

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Lysander

2008-11-19 07:08:45 UTC

Post by Ken Mayer [dBVIPS]
Anyone see any OTHER way around this?

I don't. Maybe someone does.

I had a similar situation about 7 years back which cost me dearly until
I found out what's going on. That's why I advised you to look for
something like this. The effects were too familiar.

I did not find a solution, then, either. Not even with the help of very
potential dbase pro's.

The only conclusion I burned into the back of my brain forever: never
allow re-entering a uniquely indexed value back in the same table.

Lysander

2008-11-19 14:00:22 UTC

Well, unfortunately in this case, the client prefers not to be creating
new Quote numbers for an invoice that is being backed out to a Quote. Sigh.

If you want to avoid PACKing on every delete of a quote, the only thing
you can do (in my opinion) is to make the quote-number only a part of a
bigger index.

Say base-index C4 plus quote-number C8 = total C12. Now you can make
THAT into the new unique ID in the quotes table. When transporting the
quote over to invoices, just cut the last 8 chars out of the
field-value, or have a duplicate field that carries the quote-number
only (but withOUT a unique index on it).

If you back out the invoice to a quote, you can re-use the quote-number
part, but create a new base-index part.

Needless to say that using a SQL server would solve all such problems
nearly automatically for you :)

Ken Mayer [dBVIPS]

2008-11-19 22:56:04 UTC

Well, unfortunately in this case, the client prefers not to be
creating new Quote numbers for an invoice that is being backed out to
a Quote. Sigh.

If you want to avoid PACKing on every delete of a quote, the only thing
you can do (in my opinion) is to make the quote-number only a part of a
bigger index.
Say base-index C4 plus quote-number C8 = total C12. Now you can make
THAT into the new unique ID in the quotes table. When transporting the
quote over to invoices, just cut the last 8 chars out of the
field-value, or have a duplicate field that carries the quote-number
only (but withOUT a unique index on it).
If you back out the invoice to a quote, you can re-use the quote-number
part, but create a new base-index part.
Needless to say that using a SQL server would solve all such problems
nearly automatically for you :)

Um, yeah ... at this point, I just want the app done-done. Sigh.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Ken Mayer [dBVIPS]

2008-11-19 13:36:39 UTC

Post by Ken Mayer [dBVIPS]
Anyone see any OTHER way around this?

I don't. Maybe someone does.
I had a similar situation about 7 years back which cost me dearly until
I found out what's going on. That's why I advised you to look for
something like this. The effects were too familiar.
I did not find a solution, then, either. Not even with the help of very
potential dbase pro's.
The only conclusion I burned into the back of my brain forever: never
allow re-entering a uniquely indexed value back in the same table.

Well, unfortunately in this case, the client prefers not to be creating
new Quote numbers for an invoice that is being backed out to a Quote. Sigh.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Marilyn Price

2008-11-19 16:13:38 UTC

In article <***@news-server>, ***@_nospam_goldenstag.net
says...

Post by Ken Mayer [dBVIPS]
Anyone see any OTHER way around this?

I don't. Maybe someone does.
I had a similar situation about 7 years back which cost me dearly until
I found out what's going on. That's why I advised you to look for
something like this. The effects were too familiar.
I did not find a solution, then, either. Not even with the help of very
potential dbase pro's.
The only conclusion I burned into the back of my brain forever: never
allow re-entering a uniquely indexed value back in the same table.

Well, unfortunately in this case, the client prefers not to be creating
new Quote numbers for an invoice that is being backed out to a Quote. Sigh.
Ken

Another possibility is to add another, dummy, field that would be
combined with the quote number for the unique index portion, but might
give the status for the quote - "A" for active, "I" for invoiced
already, or "A", "B", "C", incrementing each time it bounces back. In
the first case, you would probably filter for active quotes only....
Just bouncing around ideas....

--
Marilyn Price
M. P. Data

Ken Mayer [dBVIPS]

2008-11-19 22:57:30 UTC

Post by Marilyn Price
says...

Post by Ken Mayer [dBVIPS]
Anyone see any OTHER way around this?

I don't. Maybe someone does.
I had a similar situation about 7 years back which cost me dearly until
I found out what's going on. That's why I advised you to look for
something like this. The effects were too familiar.
I did not find a solution, then, either. Not even with the help of very
potential dbase pro's.
The only conclusion I burned into the back of my brain forever: never
allow re-entering a uniquely indexed value back in the same table.

Well, unfortunately in this case, the client prefers not to be creating
new Quote numbers for an invoice that is being backed out to a Quote. Sigh.
Ken

Hmm. Interesting. I am not sure how much work it would be to get it all
in there. I may just add a call to the PACK code, and if the table is
being used at the time, have a message tell the user they may need to do
the pack soon.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Ken Mayer [dBVIPS]

2008-11-19 23:01:06 UTC

How about when you move a quote out of your quote table, you give the quote
a different number before deleting. Something that will never show up as a
"real" quote.
Then if you move the quote back into the table, reusing the old number is ok
since the deleted one has a differnt number.
I did something simular by prefacing the old quote with a "*" and filtering
them out rather than deleting them. Then if I reopen them, I can just search
for the "*" and remove it.
We later changed to a long field so had to change the way we did things
altogether.
Nothing beats having a unique indentifier for every record.

Yeah, but in this case, the data is designed REALLY poorly ... if I were
doing this from scratch, I'd have a table that just had the info for the
quote that was the master record for the line-items ... I didn't design
the database, and I have everything created to use it "as is" -- a
complete re-write would be horrid.

The idea of changing the number before deleting it is not bad. However,
the problem is that sometimes only part of the Quote gets deleted ...
("In Stock" items go to Invoice, the rest stays in the Quote ...). But
... it might still be do-able. I am not sure. Hmm.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Rich - AutoTraker Inc.

2008-11-19 21:00:13 UTC

How about when you move a quote out of your quote table, you give the quote
a different number before deleting. Something that will never show up as a
"real" quote.
Then if you move the quote back into the table, reusing the old number is ok
since the deleted one has a differnt number.

I did something simular by prefacing the old quote with a "*" and filtering
them out rather than deleting them. Then if I reopen them, I can just search
for the "*" and remove it.
We later changed to a long field so had to change the way we did things
altogether.

Nothing beats having a unique indentifier for every record.

Rich...

Post by Ken Mayer [dBVIPS]
Anyone see any OTHER way around this?

I don't. Maybe someone does.
I had a similar situation about 7 years back which cost me dearly until I
found out what's going on. That's why I advised you to look for something
like this. The effects were too familiar.
I did not find a solution, then, either. Not even with the help of very
potential dbase pro's.
The only conclusion I burned into the back of my brain forever: never
allow re-entering a uniquely indexed value back in the same table.

Well, unfortunately in this case, the client prefers not to be creating
new Quote numbers for an invoice that is being backed out to a Quote. Sigh.
Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/
*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Bruce Beacham

2008-11-19 08:43:35 UTC

Post by Ken Mayer [dBVIPS]
Anyone see any OTHER way around this?

How about running a sequence of high negative numbers on the quote
numbers in conjunction with the normal numbers. Before deleting a
quote, change its quote number to the next number in the high negatives.
Or even (if the unique index process allows this) always the same
negative number - after all it then gets deleted and vanishes.

Bruce

Ken Mayer [dBVIPS]

2008-11-19 13:37:47 UTC

Post by Ken Mayer [dBVIPS]
Anyone see any OTHER way around this?

Hmm. Not sure how well that would work. It's an interesting idea ... it
might have some odd consequences, though.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Ken Mayer [dBVIPS]

2008-11-19 23:46:56 UTC

Post by Ken Mayer [dBVIPS]
Anyone see any OTHER way around this?

At this point, it *appears* that changing the Quote Number to a negative
version of itself, then deleting those records with that negative Quote
number may be solving the problem. It's hard to say for sure until the
client has tested it, but at a quick test, it looks like it's working.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Bruce Beacham

2008-11-20 09:02:02 UTC

...but at a quick test, it looks like it's working.

That's encouraging. I like your idea of using the quote number itself.
My only hesitation would be if more than one deletion occurred on a
quote number: if you already have a deleted quote with that negative
number, changing to negative the number of a second quote may make that
second quote disappear in the unique index before you have a chance to
delete it. So be sure you don't have the unique index active when you
are doing this manipulation.

Bruce

Ken Mayer [dBVIPS]

2008-11-21 02:39:40 UTC

...but at a quick test, it looks like it's working.

It shouldn't be specifically the active index at that point ...

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

unknown

2008-11-19 18:55:03 UTC

On Tue, 18 Nov 2008 17:11:56 -0800 Ken Mayer [dBVIPS]
Sender: "Ken Mayer [dBVIPS]" <***@_nospam_goldenstag.net>
wrote the following in:
Newsgroup: dbase.programming

Post by Ken Mayer [dBVIPS]
The table uses a UNIQUE index on a QuoteNumber field, which is used to
find a Quote. The reason for this is that the line items for the quote
are in the same table as the main quote data (poor design, but it would
take too much time to fix to a better data model).
** Turn the Quote into an Invoice
This deletes the Quote from the Quotes table, and copies to the
Invoice table (This is great if the customer goes forward with
the order ...)
** However, in the Invoice table, the user can then VOID the invoice
(Assume the customer wants to wait, or ... -- Invoice must
be processed in some fashion ...)
Which returns it to the Quote table, using the original Quote number.
However, the Unique index tag in the .MDX file is remembering the
currently still existing (due to Soft Deletes in the table) record with
that quote number, and adding the quote back in does not update the
index. Therefore a search points to the deleted record, which cannot be
displayed.
The only fix that I can see at this time is to pack the table. This is
problematic because it's a multi-user system ... sigh.
Anyone see any OTHER way around this?

Would it work if you, instead of adding the quote back, undeleted the deleted quote with RECALL, for
example:

use <Invoice table>
RECALL for QuoteNumber = 117
use

Ivar B. Jessen

Ken Mayer [dBVIPS]

2008-11-19 23:03:40 UTC

Post by unknown
On Tue, 18 Nov 2008 17:11:56 -0800 Ken Mayer [dBVIPS]
Newsgroup: dbase.programming

Would it work if you, instead of adding the quote back, undeleted the deleted quote with RECALL, for
use <Invoice table>
RECALL for QuoteNumber = 117
use

I thought about that. I am not sure. That's the problem. It may actually
be the simple thing to do ... but it may not be that simple. Boy, I hate
things like that.

The problem is (as noted in another response to you -- just a moment
ago), sometimes only part of the Quote is deleted. The problem might
occur if the *first* record of the quote is moved to the Invoice, but
other items are not. That record is likely to be the one in the unique
index -- and deleting that would still leave the others.

I am thinking that Bruce's suggestion may be the way to go -- turn the
number negative, just before deleting it. That way the index will
update, and I should be okay. I hope.

Ken
--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm
http://www.goldenstag.net/dbase

Greg Hill

2008-11-20 03:58:20 UTC

If the following has already been suggested, disregard<g>

I didn't have time to read all the reply's and maybe I am missing something
but anyways, I had a similar problem, adding to the index a field that
caused it to be unique fixed problem, i.e.
index on invoiceNo+dttoc(datetimeField) tag invoiceNo unique

That fixed it for my issue.

Greg Hill

Ken Mayer [dBVIPS]

2008-11-20 05:55:15 UTC