INTEGER (AUTO INC) - SERIES SKIP 3

[spysab]

Hi!

i have a field which is integer(auto inc).
so upon making new entry, new index will be assign.
but why when i cancel my attempt to create new entry, table is being revert but the index series skip its sequence?
how can i maintain its sequence when i cancel my attempt to create new entry? or is it even possible?

aside from this code in cancel command button, what else must be done? properties i think?

TABLEREVERT(.F., "JOBORDERS")
RELEASE THISFORM

 

[Mike Lewis]

What you are seeing is completely normal.

Visual FoxPro does not manage gaps in generated sequences. Gaps can be caused by reverting an appended or inserted record or by failing to update the base table and so on. In all cases, the unused value is lost, and the next generated value remains the same as if the append or insert operation succeeded.

Nor can you avoid the gaps by coding around them. You cannot UPDATE or REPLACE the value in an auto-inc field. The only solution is to alter the table's AutoInrement NextValue property, but that requires exclusive use of the table and is difficult to do in a multi-user environment.

Just accept the situation. It's not really a problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[spysab]

so meaning i cannot use an auto-increment field as memo series or invoice series for it will really skip.
how can i put a sequence +1 every new entry to become a transaction reference? that if i cancel the attempt to create new, sequence will remain +1 to last entry..

to be specific, it is putting automatic order series number which is alpha numeric.. or even if only numeric, it doesnt matter for as long as it won't skip.

 

[Mike Lewis]

Auto-inc fields are really only useful for internal IDs or primary keys. That is, where the value of the integer is known to the system but not to the user. They should not be used for invoice numbers or other fields that have signficance to the user.

To maintain your own sequence of serial numbers, there are two approaches.

First, immediately before inserting a new record, go to the last record in the table (that is, the one most recently inserted), look at its serial number, increment it by one, and use the result for your new record. That is the easiest solution if you just have one user inserting new records.

In a multi-user environment, you need to maintain a separate table of index keys. Essentially, this contains the value of the last key that you inserted into your main table. When you want to insert a new record, try to lock the record in the index key table. If the lock succeeds, increment its value by one, use that value in your main table, then go back to the index key table, overwrite it with the new value, and unlock the record. The locking / unlocking is important as it prevents two users using the same key. If the attempt to lock it fails, wait a few milliseconds and try again.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You'll have a problem with sequences anyway. As in multi-user environments, two users could add something and one cancels and the other not. Then it isn't even just a problem about the split second of the moment of saving.

Anything you want to have a final and mandatory sequence number needs to be queued up to the definite storage and only after that, you can know the number.

Autoinc is designed to protect against double numbers, so it's not reverted. It's also more perfect for that case than anything you can roll yourself, as it can make use of the locks it needs anyway for any write, automatic locks you even have without manually locking anything. But the design is not for gap-free numbering, just also think of the case two users get number N and N+1 and the user having N cancels, the user having N+1 then doesn't automatically change to N. The number N+1 in memory of the client would need to have a quantum mechanically entanglement with the central counter to know it has to change and that's not what we have (and also won't ever have with quantum computers). The design of the counter is only to care for itself, not care about gaps and fill them in later.

I once programmed a table holding the next 10 numbers of which one is reserved by a user and when he cancels it is put back into that pool. That was making it more comprehensible, but that customer still was aiming for the impossibility of never having any gap. With this pool, you do have a gap until the next invoice takes the gap number. And then you still have a later invoice with a lower invoice number.

If you need numbers in chronological order you have one extremely simple solution, which also satisfies the IRS: You never cancel and revert/not store invoices, you give them a voided status.

So any number generated by autoinc means you store that record, you never revert it, if the invoice should be voided, you do exactly that, you void it. That's what accounting teaches, doesn't it? Even if you implement the queue idea and invoices only get their number at saving and not before, you do have the problem of an error about the invoice being recognized after it has its number, so voiding is something you'd implement anyway.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Steve-vfp9user]

I'm still picking my way through VFP9 but I have created an app whereby it allocates job numbers in sequence and works like this:

There is a field in the main table that stores our records called JOBNUMB N(6)

I have a table called DEFAULTS.DBF which contains a numeric field called NEXTNUMB N(6)

When a user creates a new record and completes it, the NEXTNUMB from the DEFAULTS table is allocated to the JOBNUMB

USE MYTABLE SHARED
APPEND BLANK
mthisrec=RECNO()

* Create the record here etc *

nmessage=MESSAGEBOX("Confirm, save the new record?",4+32+0,"System Message")
IF nmessage=7
  DELE
  RETURN && Cancel and delete the record without allocating the next number
ENDIF

USE DEFAULTS SHARED  && Get the next sequential number
mnextnumb=NEXTNUMB
REPLACE NEXTNUMB WITH NEXTNUMB+1

USE MYTABLE SHARED  && Back to the original record
GO mthisrec
REPLACE JOBNUMB WITH mnextnumb

So if you want sequential numbers, try this.

Good luck

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Mike Lewis]

Steve,

Your code follows the second suggestion that I made in my most recent post (above). But it won't be robust in a multi-user environment, because there is nothing to prevent two users grabbing the same job number at the same time (admittedly, that's unlikely, but not impossible). That's why I specifically mentioned the need to lock the record.

If I may make a couple of further comments:

Your are inserting the new record, then asking the user if they want to save it, and then deleting it if the user says no. That seems an awkward approach. Rather, you should ensure that the table is buffered, and then issue TABLEREVERT() if the uses doesn't want to do the save. But it's also awkward from the user interface point of view. Better to give the user a Save button; explicitly clicking this button means they want to go ahead and save the record. And also a Cancel button, which means they don't want to save it.

The other issue is that you seem to be opening each table in turn every time you are about to access it. It would be much more efficient (and take less code) if you open each table in a separate work area and leave it open throughout the process.

You said you are still picking your way through VFP, and for that reason I know you won't object to my pointing these issues out to you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Steve,

that's ok. In the end, if everyone can live with the fact of only knowing the new jobnumber, invoice number or any other sequential number after saving, because you just increment when saving.
That can also be achieved when the counter in your separate default table is int autoinc. You only make use of it, once you're sure you save and not cancel out/revert the new record of the main table.

There's a slight problem still because that sequence number field should have a candidate index not allowing double numbers. So while two or more clients add records, you have the problem of two or more default 0 values (or NULLs). You don't get that solved by buffering alone, as the indexes are already updated with data of new records even when the workareas themselves are buffered.

And it's no good idea to rely on the uniqueness of data by not defining a candidate index for that matter.

You need at least one more step, only saving to the final table after having all data including the sequence number. Which also means no usage of autoinc even for the ID of records. That overall throws you back into using other means of ID generation like GUIDs. Because creating data with foreign keys is quite hard without IDs that finally can be stored.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Your are inserting the new record, then asking the user if they want to save it, and then deleting it if the user says no. That seems an awkward approach.

Well, my guess is * Create the record here etc * is a placeholder for the interaction with the user, input of data for the record, likely with the same form also used when editing an existing record you GO to instead of using the APPEND.

I also assume the main table is used buffered. Yes, DELETE is an awkwardness once you use buffering, because reverting a record already means deleting it off the buffer and never saving it, no need to delete it first, unless it's an old record that also needs to be deleted in the DBF. But of course, in the bigger picture, you may add and delete multiple records each in buffers of multiple tables involved before you finally decide to save or cancel.

You're right though, this all will all be much easier, eg you don't need to GO back to a record, once you use more than one workarea. Use a table IN 0 to make use of an unused workarea, SELECT 0 before using a table, if you want to work on it or SELECT alias after USE IN 0. In the long run, you only USE tables once in Load/Init/Data Environment and thus don't even USE IN 0 or SELECT 0 very often, you rather SELECT workarea name to work on a specific table.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Steve-vfp9user]

Mike

You said you are still picking your way through VFP, and for that reason I know you won't object to my pointing these issues out to you.

Absolutely not and I value your input which is always most beneficial.
Thank you

Olaf

Likewise. Another beneficial and comprehensive response as always.
Thank you

This isn’t my thread so apologies for jumping on!

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Olaf Doschke]

Every participation is welcome. Your suggested solution is something, that can still work out in some scenarios, one possibility to overcome the index violation problem is a filtered index. Or getting more complicated and use a buffered updatable view, which a) doesn't inherit the DBF indexes and so b) also doesn't add new rows before you actually commit the view with TABLEUPDATE(). This just kills the usefulness of autoinc, as said. In some way designing your table in a way it can handle multiple APPEND BLANK would be nice to allow your way of programming unless you do that as Mike interpreted just before leaving a form. When you ask users to input all data for a new record before you add it, use INSERT-SQL. Even if you just do one replace after append blank you go through index creation table and field rules with the append and then once more with the replace, you effectively do an insert and an updatre where just an insert is sufficent. The good usage of APPEND is for having a record for form control binding.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name