Custom ID

[FaeryHigh]

How can I take pieces of fields to generate an ID?

Example:

First: John

Last: Smith

Company: Every Company

Street: 123 E. Main St

City: Springfield

State: IA

Zip: 54321

ID: EC123IA (Company-Street-State)

[bcooney]

You can create a calc field that concatenates the values to form an ID using text functions Left() etc...but don't use this as a key field, of course. Key fields are usually auto-enter serial number fields and must be unique to each record in the table. This is what should be used in relationships.

[FaeryHigh]

Yes this if for relationships.

Could you give me a broader example?

[JesseSFR]

I think bcooney just saved you a TON of headache. You can create serial numbers in filemaker. Just create a number field. Then change it's to Auto-Enter Serial number and you are all set. You will have to of course go ahead and update your past records if you already have some in there.

To do that, just put the ID on the layout and select Replace Field Contents from the Records menu. Now all you have to do is select Serial Number and make sure that Update Serial Number in Entry Options is set.

You now have Id's on all your records.

[T-Square]

FaerieHigh--

Jumping in here, what happens when a person moves? Do you change their ID? If so, how do you fix all the relationships? Or, what happens if later someone ELSE has the same calculated ID? You might end up with *two* people with the same ID, and then their accounts are permanently cross-linked.

There are a LOT of threads in these Forums about advantages and problems with using overloaded variables as record IDs, and most of them point out the advantages of simple serial number IDs.

By "overloaded", I mean using one variable to store more than one bit of information--in this case, it's an ID AND it tells you what state they're from! I am reminded of the old Saturday Night Live routine for the floor-wax/dessert-topping spoof ad, which provides a perfectly absurd example of the problems of over-extended items.

David

[Anuviel]

You could just use a serial number auto enter as your record id - it does not need to show on the record so there will be no problems later. Then create your custom id and have the users use that to pull the records and for ease of use.