Serials incrementing

[LaRetta]

An explainable but unexpected behavior surprised me today:

Table with 2 fields:

ID ( auto-enter serial number, increment by 1) ... sitting now on 1

text field set to validate always, disable user override and as Unique

Import 1000 records using ADD only - (knowing there are duplicate text records and I will get 10 unique new records as a result). Enable 'Perform Auto-Enter' (because I want the serials entered during the import and to also increment in field definition when done). Result, 10 records are imported - just what I hoped. But two things ...

1) If I look in my auto-enter serial, it has jumped, not to 11 but to 1001.

2) If I import same data again and (rightfully) no records import because they fail unique validation (all are now in system), the serial increment jumps to 2001 ... and all for 5 new records.

I don't necessarily mind having holes in my serials but this is a total waste. It also means that FileMaker is wasting time incrementing its serial needlessly when validation says the record should not be added to begin with.

But auto-enter happens before validation, right? And one can't perform validation until it is a RECORD in the table. I don't recall the version when this reversed (auto-enter and validation). Anyway, it just seems that if validation is keeping a record out via an IMPORT error then it shouldn't increment its serial. So how do you suppose FileMaker processes it? 1) create the record, 2) test the validation and, if failure, 3) delete record back out but oops ... forgot to back out the auto-enter increment on it?

This import method is still valuable but its good to know about these things or am I misinterpreting? I suppose I can take the non-lazy way and set the IDs after import but I prefer letting FileMaker handle the IDs. Or I could use a temp table to de-dup the list first. Input appreciated.

The mega-jumps in auto-enter serial on tables with deletions forbidden just gave me a jolt until I figured this out!

added a sentence for clarity

[David Jondreau]

Have you tried setting the serial to increment "on commit" instead of "on creation"?

BTW, I switched to UIDs a few months ago and love it.

[LaRetta]

Hi David,

Setting to 'On Commit' was a good idea and I just got around to testing it but no, that too increments even for records *not imported. If I import 100 new records (and serial starts at 1), serial will switch to 101 (because first time none fail validation). But if I import those same 100 records and found set result is 0 (because of complete validation failure) the serial still increments to 201 for the next new record. It increments by the number of incoming records; which could be large.

*imported but not left in system because of validation error

Holes in serials sometimes matter. If it matters then I'd better be prepared to explain holes in serials when any new record fails import due to validation error or use another approach on those tables. I plan on hybrid - tables where serials matter, I will de-dup the incoming batch first (if imports must be allowed); once in the table, they must stay. I'm just glad I'm aware of it.

I am not convinced on uIDs, David. Even if I went that route, how would an auditor know if a record was deleted if it was based upon a uID instead of a serial? And, strange as it may sound, I've never used the 'On Commit' setting.

Thanks again!

[David Jondreau]

I've never had to deal with an audit trail, so I don't have a good answer, except I've seen adverts for audit logging systems that only take one field per table and a custom function or two.