Auto-Increment Annoyance

[Lars Stroebaek]

Hello,

I am not sure if this is the right subforum, but I have a question related to the auto-enter function which has arisen due to the import function in filemaker (11).

Basically, I have a very simple database which is just a converted Excel sheet. Each record has an ID number, which has been set to auto-increment by one every time a new record is added. My issue is that new records are added by importing from Excel, and on occasion this has resulted in extra, empty records being made by accident. This is fine as I just delete those records, but then when I create new records next time, I find that the auto-incrementing of ID numbers continues from where the deleted records stopped, not where the current records stop. In other words, if I create records 4,5,6,7, but 6 and 7 were errors and I delete them, then next time I create new records it starts at 8 instead of at 6, leaving a gap between 5 and 8 that did not need to be there since starting at 6 again would not have displaced any later records.

My question is, as implied above, is there any way to get filemaker to (automatically) ignore deleted records for the purposes of auto-entering serial numbers, as long as there are no later records, or will the serial number function always consider all numbers to be already in use once they have been entered, even if they are deleted straight away?

Thanks for reading.

[Keith LaMarre]

You'd have to run a script (or manual procedure) that replaces the imported value with the correct serial number and then "update" the auto-entered value.

But don't worry - you won't ever run out of numbers...

[Lee Smith]

My question is, as implied above, is there any way to get filemaker to (automatically) ignore deleted records for the purposes of auto-entering serial numbers,

Sequentiality is over rated. AND having gaps in your serial numbers is a normal part of what happens anyway. In other words, Gaps will be created in the file as you use it, i.e. whenever you delete a real record. The danger here is, whenever you strive to eliminate gaps, you run the risk of losing relationships that were based on that field. Besides, you are probably the only one who will see that there gaps anyway. So, what's the purpose of having no gaps?

Especially when you run the risk of inadvertently creating duplicate serial numbers, or effecting the serial numbers that already exist. IMO, I would just delete the blank records, and let FileMaker create gaps.

Lee

[Lars Stroebaek]

Incredibly late response I know, but I see your point very clearly. I'll explain to my users that gaps are a part of life and not to fuss about tidiness. Thanks for the replies.

Lars

[comment]

not to fuss about tidiness.

Actually, if you think about it, the gaps are there BECAUSE of Filemaker's tidiness.

Why do your users need to even see the serial numbers?

[bcooney]

on occasion this has resulted in extra, empty records being made by accident.

Sounds like Excel's last cell is not the in the same row as the last record, and so FM sees "empty" rows as records. You could select the data range and import that (or import a named range).