Contract Serial IDs

[bcooney]

My client has a Contract database. I use UUID for primary keys, however, I have an auto-enter serial to display the Contract ID on the form.

Client wants to enter last year's Contracts that were created before FileMaker. So, I opened up the serial field to allow edit (but maintain Unique validation). It's getting messy. As they enter old contracts, they are overwriting the next serial id. Of course, they now have gaps...with which they are not happy.

Suggestions for a better approach? Script the Contract ID using a max +1 calc (perhaps using ESQL to obtain that value?).

[comment]

Client wants to enter last year's Contracts that were created before FileMaker.

If they want to enter records out of their chronological order, then of course there will be "gaps" when the records are viewed in chronological order. The best remedy for this, IMHO, is to stop caring. The important thing is not to have duplicates.

 

As they enter old contracts, they are overwriting the next serial id.

I see no reason why one would or should cause the other.

 

So, I opened up the serial field to allow edit (but maintain Unique validation).

I wouldn't.

 

[bcooney]

"The best remedy for this, IMHO, is to stop caring. The important thing is not to have duplicates."

I have said this.

I need to open up the serial to allow edit. How else will they enter the "old" contract id.

[David Jondreau]

Sounds like there are two issues...Allowing users to enter old contract IDs and not having gaps.

You can solve the first by having three fields total. Auto-enter serial, OldID, and a calculation that displays the Old if present otherwise shows the Auto-enter.

How do not have gaps is another issue completely. One possibility would be to create a few hundred records that represent the old contract with a blank serial ID, and then start the serial ID at the highest old contract number.

 

[bcooney]

I would be interested to know why scripting the contract id is not something you think I should do, as I said above, this is a display filed, not a key. 

[comment]

You didn't say they need the old IDs. If they do .. Let's say there are 500 or less old contracts, and that the new ContractID is auto-entering serial numbers starting from 501.

You now need to enforce the following constrains:

1. Users are not allowed to modify auto-entered numbers above 500.

2. Users can enter old IDs (i.e. numbers less than 500), provided they are unique.

There are several ways you could implement this, and I believe all of them would benefit by forcing users to create the old contracts via a script only. Personally, I would just pre-create the first 500 records for them (including the IDs), and let them fill these out at their leisure.

[bcooney]

Again, what's the harm with allowing edit of an auto-enter DISPLAY serial that is validated unique? And...the next id is set by script using max+1?

[comment]

Again, what's the harm with allowing edit of an auto-enter DISPLAY serial that is validated unique? And...the next id is set by script using max+1?

Validating as unique is not enough by itself; it requires users to know what to do when the validation fails.

[bcooney]

Good point. Thanks.