Auto-enter that gets the highest number+1

[krmullins]

Is there a calculation that would allow me to get the highest value in a field and then ad 1.

I can't think of a function that I could use to get the highest value in the dataset.

Thanks

Kevin

[bcooney]

Max ( ) with a self join? Hopefully, you're not trying to generate a serial ID for use as a key with this function.

[comment]

get the highest value in the dataset.

It depends on how you define "the dataset" - it could mean the found set, or all records in the table.

Either way, you can only get the highest value in the dataset AT THAT MOMENT - so if two users create a new record each at approximately the same time, you can very well get a duplicate value.

[krmullins]

Max ( ) with a self join? Hopefully, you're not trying to generate a serial ID for use as a key with this function.

It is going to act as an ID, but not the only one. There is an absolute ID that is a standard serial number, but what I need this to do is be a bid number but the users sometimes start a new record and then delete it so the bid numbers have holes and they are really tired of those holes. I am trying to recreate what other Databases do with serial numbers by only incrementing on the highest number.

[krmullins]

It depends on how you define "the dataset" - it could mean the found set, or all records in the table.

Either way, you can only get the highest value in the dataset AT THAT MOMENT - so if two users create a new record each at approximately the same time, you can very well get a duplicate value.

I wouldn't want it to be influenced by the found set, but that just might not be possible. There is really only one user at a time so duplicate values at this stage are not a problem but it is good to always keep that in mind.

[comment]

Have you considered issuing a serial number on commit only, and preventing users committing a new record until they are sure?

[krmullins]

I had thought of that a few years ago, and something didn't work out, but now I can't for the life of me remember why. I should look into that again.

Thanks

Kevin

[gary2k8]

Not sure if this is the solution you want; I figure a simple way -

If...

Sort Record ( # ) Ascending

Go to Record LAST

Set Var $New #; # + 1

done

Edited August 16, 2010 by Guest