Auto generating ID fields

[TheLizardKing]

Hello everyone this is my first post in these forums. I originally learned how to create databases using Access for Windows. I then used that knowledge to create a database for work using Filemaker 6. I now am intrigued about the table structure for version 7 since it is more familiar to me. I am a Mac guy, but the college I went to taught us solely in the Windows environment.

Anyway, I recently purchased version 7 and just wanted a little help getting off the ground. I would like to know the best and/or easiest way to create ID fields that automatically generate unique numbers. I would be using this for at least two tables (work orders and invoices) Any help would be greatly appreciated.

[The Shadow]

You create a field of text or number, then choose "Options...", on the auto-enter tab, choose "Serial Number". I believe this corresponds to Access's "AutoSerial" type?

If you choose text as your field type, you can use letters in your start value, ie: A00001 and the letters will be maintained as the number is incremented.

If you wish for the system to enforce uniqueness, you switch to the validation tab and select the "Unique Value" checkbox.

[TheLizardKing]

Excellent, and thanks for the quick response Shadow. That helps me immensely. One more thing however, if i wanted to manipulate the auto generated field and create my own ID, would FM allow me to just click in that field and change it. I love the fact that you can set it to a text field and use letters as a startup because I currently use letters to signify some different types of contacts in my Address Book. For example, C1234 stands for a customer and S1234 would stand for a supplier. I then use the letters for my finds. For example if I wanted to just do a search for all of my suppliers I search by just typing in an "S" in the ID field.

What I'm thinking is that I would like to change the ID field in the Address Book to an auto generating field beginning with the letter "C" (because that is the majority of my entries) and then be able to change that "C" to an "S" if I were to add in a new supplier. But would this possibly destroy all of the IDs that were all ready manually entered and that are currently used to relate to my Work Orders file?

[The Shadow]

Yes, you can still change the value that's auto-entered. Its really more like a "default" value in SQL terms.

The auto-entered serial change won't have any effect on existing records (no destruction possible) - it just states what value should be used when new records are created.

If you already have records, make sure you put in a starting customer ID on the auto-enter page so it doesn't conflict with ones you already have.

[TheLizardKing]

More good news! Now I only wish that I didn't use phone numbers to manually enter IDs in the past. I suppose I could use CC1 as my initial default value and be problem free ...except for the fact that I would be dirtying the data a bit.

[CoZiMan]

But I have to say that when I think I've 'dirtied' the data I just build a quick script to loop through all the records searching and replacing the 'dirt'. Cleans it up mighty fast and then I just delete the script. Works great for solution upgrades also. Just triple check the process

[jrb12]

A red flag popped into my mind as I was reading this thread. It was caused by your idea of modifying what seems to be your primary key, after its been assigned. If in the future you use this key to relate other tables back to your contacts, you could break those links by changing the key value or provide yourself with a headache updating the value in other tables. You hint at this already with the Work Orders table. I'd suggest you keep the key value as assigned and put the type code, 'C', 'S', etc, in a separate field. You can always create a calculation field that combines these two for use in searching or whatever.

Cheers,

John.

[Jana]

If you want to maintain an auto-entered serial number but also be able to identify wether it is a customer or suppler number, consider maintianing your auto-entered sequential serial ID number but have a separate Type field which identifies C or S. Then create another field which automatically concatenates the Type with the ID.

[bruceR]

TheLizardKing said:

Excellent, and thanks for the quick response Shadow. That helps me immensely. One more thing however, if i wanted to manipulate the auto generated field and create my own ID, would FM allow me to just click in that field and change it. I love the fact that you can set it to a text field and use letters as a startup because I currently use letters to signify some different types of contacts in my Address Book. For example, C1234 stands for a customer and S1234 would stand for a supplier. I then use the letters for my finds. For example if I wanted to just do a search for all of my suppliers I search by just typing in an "S" in the ID field.

Very bad practice. There is a basic rule about primary key fields: they should NEVER have meaning. What happens when your customer becomes a supplier or is both? The S/C should be stored in a a separate field.

[Tripod]

I agree--a primary key should be functionally dependent from all other fields in the database. Compound fields are to be avoided as primary keys. For instance, what if a customer becomes a supplier at a later date? Yeah, probably wouldn't happen, but primary key is best left as only a primary key, and not dependent on any other considerations.

Welcome to the forums!

--Tripod

[Jana]

In my suggestion, I believe retaining the unique ID auto-entered serial number would allow for use as the primary key for use in relationships etc., but if you wanted the customer or supplier identified in your layout, the concatented field would show the desired data while retaining the unique ID for relationships etc.

[TheLizardKing]

Sounds like a great idea CoZiMan but unfortunately I don't know much about scripting. I learned VB a few years ago and HTML but still haven't gotten around to learning how to write scripts. Although that is one of the things I'm interested in learning about next

[TheLizardKing]

Yes yes you are right. Its true that having a supplier as a customer is rare, but it has all ready happened in the past. And as far as the Primary Key having no meaning - I suppose I did violate a very fundamental rule. The problem is that there are 400 entries in the Address Book that all ready have the letters present. You know what they say about hindsight.

[TheLizardKing]

HI Jana and thanks for posting to this thread. How would I go about creating a field that "concatenates" the ID and the field that signifies C and S. (there is an E designation as well for employees). I remember doing something like that before in Microsoft Access and it caused lots of headaches.

And thanks Tripod for the welcome!

[-Queue-]

A concatenated field is a calculation field that would look something like:

uniqueID & " " & designationfield

[TheLizardKing]

I would just assign that calculation to the field? Wow seems pretty easy. Thanks Queue