Making auto Serial Number go back one number

[Bruno Dini]

Hello, I am making this database, and one of the features of it will be a clients database.

I don't want everybody to add clients, but I want everybody to see them.

I made a layout with all the information, that cannot be modified in browse mode with an "ADD" button.

When pressed this button will create a new record and go to another layout modifiable in browse mode, so the person can put the information.

This layout has 2 buttons, one OK button that will lead to the information layout and commit the record.

Now comes the question. I have another button, called cancel, this button has the script "revert record/request" and go to information layout.

The problem is that this clients records have a auto enter serial number, and even when I press cancel button it will create it. So if I cancel a record, the next one won't be sequential anymore.

Is that a script step so the serial number would reduce one number?

By the way, the serial number has letters too, eg: C002, C003

Hope I'm clear with my question.

Thanks in advance.

Bruno

Edited April 22, 2008 by Guest

[mr_vodka]

Your primary serial key should be meaningless. If you want another field that displays the C002 or C003 then you can look into a self join with the max function but again DO NOT USE THIS AS YOUR KEY.

[CobaltSky]

John,

I don't believe Bruno has said whether the value in question is a key field - all he's said is that it is required to be sequential.

Bruno,

The script command Set Next Serial Value[ ] can be included in a revert script to change the serialisation. However you should be careful if the solution is multi-user (another user may have created a record between when the current user creates the record and when they revert it...).

[mr_vodka]

Hiya Ray,

Yes I understand that he did not explicitly say that he was going to use this as a key field. However, based upon on his previous posts and assessed skill level, I just wanted to make sure that he doesnt make the mistake of using it as a key. :

[Bruno Dini]

I understood.

So I need a auto entrer serial field to by my primary key, and another field to be my code.

Sorry I'm newbie, but how can I use the max function to do this code field?

Thanks in advance.

[FestiveEmbalmer]

John is right; you really want your primary key to be meaningless. Like a soul!

But seriously, think of it like a soul. The number is meant to individually identify that specific instance of the table entity in the database in a way that the system can quickly and easily relate other specific entities to. If it has a meaning to you, that almost always means that you will want to change it at some point to reflect a change in the meaning. The fact that the number is sequential doesn't actually help its function unless you need to know the creation order of your records, in which case you should have a "creation timestamp" field for that purpose anyway.

From the information you provided, I would either:

1.) change the field options for your serial number to "On Commit" and then set your script to take the user to the editable layout before creating your record so that if they do not finish it doesn't use up a soul...er serial number.

2.)Make copies of all the fields you want to allow them to edit that are GLOBAL versions. Then write a script that creates a new record, transfers all of the data from the global copies to the desired fields and then clears the globals. No wasted serial numbers.

hope that helps,

matt

[comment]

The 'correct' way to do this (i.e. the way the application provides for this purpose) is to set the auto-entered serial number to be generated "On commit". Then set your layout to NOT commit records automatically, and have buttons for committing/reverting the record.

Another option is to receive user input into global fields, then create the record by script.

Rolling the serial number back is NOT a good idea, because another user might have created a new record in-between.

EDIT: Slow connection today, I see I am relegated to repeating...

Edited April 22, 2008 by Guest

[Bruno Dini]

Hello all!

Thanks all for the help.

I solve that the way comment said, not commiting automatically the record. It worked perfectly for me.

By the way, this serial number is for identification only, I won't use it as my primary key.. :-D

Thanks again for the help!.

[Bruno Dini]

Another question, due to this solution, can I avoid the "clicking outside a field" to commit a record?

I want the record to be commited only when the button is pressed.

[comment]

See:

http://fmforums.com/forum/showpost.php?post/288278/

[Bruno Dini]

Thanks a lot :waytogo: