How to make a serial field that updates from last existing number, not last created

[kedawerx]

Hello. Today was my first experience using FM 8.5 Advanced...or any version of FM. I am trying to set-up a project management database. To do this, I had hoped to modify one of the starter solutions, but this has proven to be more difficult than I imagined.

My first challenge is to create a project number field that updates when jobs are created. I have discovered the serial function, but have a problem w/it. If I create two records (e.g. 0001 & 0002) than delete the second record (0002), the next record I create will be numbered 0003. I would like to make a numbering system that is smart enough to base the new number off of the last existing number. For example, in the situation above, the newest record (after the first 0002 is deleted) would be numbered 0002.

After I tackle this problem, I will append a three letter customer ID to the number, and use this combination on invoices, etc.

Thanks in advance for any help you can provide.

[Ocean West]

there are extensive discussions on reasons why it is IS IMPORTANT for your internal KEYS to not care about gaps in sequences. Or that they contain do not contain any human recognizable meaning.

Use a STANDARD serial number field for your database relationships create another USER based field for your field. Via scripting you can set the field to the Max(ID) +1 ... However unless you catch it right away you will still have gaps in sequence If you delete 0002 and the next record is 0009 it will not use 0002 unless you have some detailed scripted method to achieve it.

[Genx]

Im with Stephen, much better, easier, safer to just stick with non human readable standard incremental serial values...

I.e. theres nothing wrong with 110023 ... clients are used to it anyway, and its nothing different in the business world.

[kedawerx]

Thanks for the replies.

I ran a search, but wasn't able to find the article you mentioned. In any case, it seems that the main justification for non-sequential numbering is simplicity. Am I reading this correctly?

Also, in the 'Max(ID)' example, should 'ID' be replaced with the name of my field (e.g. projectID)?

Lastly, can anyone point me to a good primer on FM scripting. The documentation that ships w/FM is very poor.

Thanks.

[Lee Smith]

I ran a search, but wasn't able to find the article you mentioned. In any case, it seems that the main justification for non-sequential numbering is simplicity. Am I reading this correctly?

You should be able to find numerous typic on Serial Numbers by searching for the keywords Auto Enter Serial Number. Actually, the numbers will be sequential when based on the Auto Enter Function. However, you will see gaps when you delete a record. Think in terms of these serial numbers just being unique to the record they are attached to, and that the user need not need to see or access them.

Also, in the 'Max(ID)' example, should 'ID' be replaced with the name of my field (e.g. projectID)?

Yes.

Lastly, can anyone point me to a good primer on FM scripting

Check out the Resource Topic Area for recommendations on Books, Videos, etc.

HTH

Lee