Getting next record number

[audiopro]

I am using the following to get the next invoice number.

	SELECT FRAMES
	SET ORDER TO INVNO
	GO BOTTOM
	TOP_INVOICE=FRAMES.INVNO
	TOP_INVOICE=TOP_INVOICE+1

It works fine on my computer but on my client's computer, TOP_INVOICE is sometimes set to the value of 1, rather than the highest invoice number+1.

Any thoughts?

Keith

http://www.studiosoft.co.uk

 

[Toman]

Keith,
have a look at thread184-1587483
Tom.

 

[Mike Lewis]

Keith,

Could it be a multi-user issue? If a second user is trying to grab an invoice number before the first user has rewritten the his number, the numbers will get out of sync.

Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

You need to look at the INVNO field contents.

First, what is the field type?

If it is Character, then creating an Index on it may produce un-expected results depending on what the ASCII value of the first characters are.

NOTE - if a Char field, the same issue could possibly result in the method suggested by Tom above due to the Index being built on ASCII values.

If the INVNO field is an Integer or Numeric field, then the results of the Index should be as expected.

Back to if it is a Char field, then for the purposes of finding the highest numerical value I'd make the index something like: INDEX ON INT(VAL(Invno)) TAG IntInvno

Good Luck,
JRB-Bldr

 

[Dan Freeman]

I would definitely examine the data. Sounds like you may have a record where INV_NO is set to 0.

It could easily end up as the last record in your file if you ever SET ORDER ... DESCENDING and don't put it back to ASCENDING before executing this code.

But I truly and fervently hope that you've wildly simplified your code for purposes of example. There are many other things the code should do to ensure a unique and new INV_NO.

 

[audiopro]

Thanks for the replies guys.
The data type is numeric and the app has worked ok for a long time. It is on a network but only 1 user is entering data at a time.

I put a browse into the code to check if the rec pointer was being sent somewhere strange but it always points to the last record.

There are records with INVNO set to 0 but why would one of thos appear at the end of an ascending table?
The code supplied is the code used in the app, what else is required to find the last record in a table?

My thoughts are:-
Convert the INVNO field to INT and Reindex the table.




Keith

http://www.studiosoft.co.uk

 

[mjcmkrsr]

Hi,
My approach


SELECT MAX(INVNO) FROM FRAMES INTO ARRAY laTOPINVOICE[1,1]
TOP_INVOICE = laTOPINVOICE[1,1] + 1

hth
Mark

 

[audiopro]

I tried that as a possible fix a few days ago but Select Max takes over 5 seconds to complete on a table of just over 8k records.

Keith

http://www.studiosoft.co.uk

 

[JRB-Bldr]

"There are records with INVNO set to 0 but why would one of thos appear at the end of an ascending table"

Have you tried a REINDEX ?

Perhaps the index has become corrupted.

Also if there are records with INVNO = 0, are they legitimate?
If should either be assigned a legitimate INVNO or DELETED (followed by a PACK).

Good Luck,
JRB-Bldr

 

[mjcmkrsr]

Hi,

"I tried that as a possible fix a few days ago but Select Max takes over 5 seconds to complete on a table of just over 8k records."

SELECT MAX(...) should be produce the result in an instant - there seem to be other problems with your table or your network. Try PACK and REINDEX and check your network traffic.

hth

Mark

 

[Dan Freeman]

The symptoms you describe mean either you have considerable I/O issues on the network, or you have more than one person entering data at the same time.

I'm guessing that after you generate the new INV_NO you do something like:

APPEND BLANK
REPLACE INV_NO WITH (your variable)

Another user is trying to generate a new INV_NO and hitting the result of APPEND BLANK before the REPLACE. It's a classic mult-user programming issue that's been written about incessantly over the last 20 years. At the VERY MINIMUM you're missing the necessary multi-user locking.

On the other hand, 8k records is paltry and there is no way SELECT MAX() on an indexed field should take 5 seconds.

Something isn't adding up here, but I can't see enough detail to tell what.

 

[audiopro]

I am certain that there is only one user entering data. The network consists of a networked laptop in a retail shop and a desktop in the office. When the guy is in the shop, he cannot be in the office.
There are legitimate invoices with the number '0'. The type of stock he holds has to be invoiced but until sold it is given an invoice number '0' and a customer number of the owner.
I have tried a reindex today but have yet to speak to my client to see if the problem still exists. The app in question works for weeks without issues and then does the INVO number 1 thing.

Keith

http://www.studiosoft.co.uk

 

[JRB-Bldr]

If you have access to changing the code itself so as to minimize future problems, think about using some other NOT SOLD value for Invno instead of 0.

Since it is a Numeric field, perhaps something like: -9999.

Yes, as new items are entered they will automatically obtain a NOT SOLD Invno value of 0, but since you have to populate the other fields why not also populate the Invno field?

Good Luck,
JRB-Bldr

 

[audiopro]

Since it is a Numeric field, perhaps something like: -9999.

Not sure how that would solve the problem, using -9999 as a default would result in an invoice number being -9998 if the present circumstances were repeated.

Using unsold invoice number '0' has only recently become a problem and I am thinking tne vause is some problem with the table's index rather than the code itself. I will know better when I have spoken to my client after this weekend's trade.

Keith

http://www.studiosoft.co.uk

 

[JRB-Bldr]

You could always change your Index expression to
INDEX ON Invno TAG IntInvno FOR Invno > 0

So whether you kept your NOT SOLD Invno = 0 or set it to a negative value, when your Index was Active you would not be able to 'see' your NOT SOLD items.

Regardless, have you done
SELECT FRAMES
SET ORDER TO
and confirmed with a BROWSE when NO Index is active that there are Invno values > 0?

And when you do your
SELECT FRAMES
SET ORDER TO INVNO
have you done a BROWSE to examine the values with the Index active?

And are there ANY Invno values which are greater than 0 under that circumstance?

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Actually, if you GO BOTTOM in order of the INVNO index you don't go to the last record of the dbf but the last in INVNO order, and the only thing disturbing that is, if the INDEX was last used DESCENDING, then bottom becomes top and you get to 0. This effect can bite you even minutes or hours after the last usage of the software. If the order was set descending just using SET ORDER TO tagname does keep it at DESCENDING. The only thing you need to take care of in your one user scenario is that you explicitly set the order ASCENDING, eg

SELECT FRAMES
SET ORDER TO INVNO ASCENDING
GO BOTTOM
TOP_INVOICE=FRAMES.INVNO
TOP_INVOICE=TOP_INVOICE+1

That's what dan also said initially.

You could also do a simplistic check of of TOP_INVOICE not being smaller than a threshold you determine via SELECT MAX() at the start of the app.

Bye, Olaf.

 

[Olaf Doschke]

Actually I tried setting order descending, then closeing the table reopening and the index reverts to ascending. Nevertheless I think there are circumstances this happens or happened. Which version of foxpro are you using? This may be a fixed bug, so I can't reproduce, but it may be in your version.

Bye, Olaf.

 

[Dan Freeman]

Olaf,

When I'm debugging this sort of strange behavior, I always start by asking myself "Self (because that's always how we speak to ourselves), how would you cause this if you WANTED to cause this?"

I can always think of at least one way to cause it, and I usually find at least one of those ways is the culprit.

I'm not convinced that this is index corruption because the current symptom is a repeatable behavior. Index corruption causes irritatingly random behavior, not repeatable behavior.

I remain convinced we don't know enough here.

 

[Olaf Doschke]

I'm not talking about index corruption. I said there was a bug (and I'm sure it was in previous vfp versions), that the index order ascending/descending is stored with the cdx and thus if you SET ORDER TO the last used sort direction is used.

If this sortorder is DESCENDING, then GO BOTTOM will indeed go top to a record with invno 0 (audiopro confirmed there is such a record) and thus returns 0+1=1 as the next invno.

I don't believe it's the machanism of APPEND BLANK happening at user2 right before the GO BOTTOM of user1, which leads to next invno being 1. audiopro said there was only one user, so this would not happen.

One thing you could do for sure, audiopro. After calculating TOP_INVOICE, check:

IF TOP_INVOICE=1
   ? recno()
   ? descending()
   ? alias()
ENDIF

Bye, Olaf.

 

[audiopro]

I have had a check of

IF TOP_INVOICE < 10
WAIT WINDOW "REPORT INVOICE NUMBER ERROR" NOWAIT
ENDIF

for quite some time which is how my client knows there is a problem.
The app will be used again tomorrow so I will know more then. The current version has a REINDEX on start up but this has not yet been run as it will not auto download until tomorrow morning.
I will also add ASCENDING to the ORDER statement but I am interested to see if the problem is solved by reindexing.

It is a mystery and as stated earlier, the problem only occurs on my client's network and not on my own, this is what makes me lean towards a corrupted table rather than a coding error.

Keith

http://www.studiosoft.co.uk