Name Specific IDs with Sequential Numbers

[Jim McKee]

This is an example file for radiohead on FileMaker Cafe who is looking for a way to create customer IDs using a combination of the customer's last name followed by a sequential number starting at 100, and incrementing by 1 for each new customer with the same last name. You can read radiohead's post here.

Essentially this solution uses a custom dialog for First and Last name entry, validates the entries, then sets the ID field from a relationship-based aggregate calculation. It's probably not the most elegant solution, but it works pretty well. It does require that users cannot access the "New Record" menu command so they can't bypass the "New Record" script. This will require some password configuration or using the SecureFM

[LiveOak]

This is one of those things that is a really bad idea. Records ID's shouldn't mean anything (especially if used as keys). If they do, someone always wants to correct them (name change at marriage?) and winds up creating a bunch of orphaned records.

Jerry Robbins (one of the major players of the FileMaker community) gave a presentation at DevCon 2003. His requirements for Primary Keys are that they be:

1) Unique - they are different for each record

2) Existent - every record has one

3) Persistent - they don't disappear

4) Consistent - they don't change

5) Meaningless - they don't MEAN anything that would cause 4) to come to question

I won't bore you with all the stories were I've lost the argument to customers on this count and two years downstream problems have occurred.

-bd

[Kurt Knippel]

What people need to realize is that the Customer Number (i.e. Knippel0254) which is used by the organization for tracking and identification purposes is DIFFERENT from the customerID (i.e. 734526-008482-078432) which the database uses to identify and relate to the specific record which contains the data "Knippel0254".

The database should not use "Knippel0254" if at all possible and the user should NEVER see 734526-008482-078432. One has meaning and that meaning could change, the other is meaningless and should never change.

[Jim McKee]

bd & Kurt ...

You guys are both absolutely correct. The marriage/name change issue came to mind immediately when I read what radiohead wants to do.

The Customer name-number ID in his solution should not be used relationally because of its fragility. Frankly, I don't know what purpose it serves.

If he is using it relationally, he could built a contingency script for a name change situation that would find and 'update' the IDs in "child" records. But, this could get very sticky, and even stickier if the FileMaker system uses the ID to interact with QuickBooks or some other external accounting system. I don't even want to go there. It's best to avoid this completely by adhering to the primary key requirements outlined in LiveOak's post.

[Anatoli]

RE: If he is using it relationally, he could built a contingency script for a name change situation that would find and 'update' the IDs in "child" records.

IMHO -- Primary key shouldn't be changed -- ever. All other values should be changeable in related databases -- in single place.