This is replacing a database that is in place so it needs to be like this...
But that does not mean that you have to use this identifier to link tables in your new database that replaces the old. Keep it in the system as a data field used for searches, sorts and labeling, but use a simple serial number field or a text field that auto-enters Get ( UUID ) for you primary key.
You can use a self join relationship to produce this identifier, but keep the unique values validation as you can still get duplicate values if two users try to generate this value at just the wrong split second interval of time.
First, separate the text and the number parts of this ID into two separate fields: cIDText and Sequence. You can set up a text field with an auto-enter calculation to combine them and validate for unique values.
cIDText can be a simple calculation field with a text result type: Left ( LastName ; 3 ) & Left ( FirstName ; 1 )
Now set up a self join relationship by creating a new table occurrence of your table and linking it to the current table occurrence by cIDText:
YourTable::cIDText = YourTable 2::cIDText
Now you can use this calculation, either in an auto-enter calculation or the set field step of a script for creating the new record:
Max ( YourTable 2::Sequence ) + 1
If you set this up as an auto-enter calculation, be sure to clear both the "do not evaluate if all referenced fields are empty" and "Do not replace existing value" check boxes.
Your Auto-enter calculation to produce the ID code in a text field would then be:
cIDText & RIght ( "000" & Sequence ; 3 )
I'm assuming the number part is unique per the text part. For example you could have SMIJ001 & JOND001
You'd use a relationship like the one pictured based on the text part. "SMIJ"
This is basically a rewording of what Phil says.
I would use a script to set the identifier rather than an auto enter field. I tried this out and the sequence doesn't increment if you set "Do Not Replace" and if you don't then the sequence number increments any time you make a change to the record. Like if you change John to Jonathan the id increments. Using a script with Phil's calcs should work.Just one change
"If(IsEmpty(YourTable 2::Sequence);1;Max(YourTable 2::Sequence) + 1)"This set's the first record, because if you add 1 to NULL ( "" ) the result is NULL.
I tried this out and the sequence doesn't increment if you set "Do Not Replace" and if you don't then the sequence number increments any time you make a change to the record.
Because the data is from a related table occurrence, it will not increment any time you edit the record. I've tested this in a file previously.
I must be doing something different. I set this up in a test file just now and it's incrementing. Only the sequence (Serial in mine) number is coming from the relationship.
It's too bad I can't attach my file. Here are my calcs though.
You can't upload a file, but you can share one using a file sharing site such as Drop Box.
Compare your file to mine: https://dl.dropboxusercontent.com/u/78737945/CategoryBasedSerialIDs.fmp12
If you edit the contents of "other field" the contents do not change.
I suspect that your auto-enter calculation refers to a field from the wrong table occurrence.
I opened yours In FM 13 and it looks pretty much the same as mine. When I changed Fred to Freddie the CategoryIDField incremented. I can change "Other Field" and it does not increment. I think because FirstName and LastName are part of the calc changing them causes the increment.
And in that case, you are changing the value of a name field and it should increment in most cases.
But you raise a good point that I hadn't considered. (This is yet another reason NOT to use this type of calculated ID if you can avoid doing so.)
I agree, not a good idea to use a calculated ID. But I'm thinking, once set, the CategoryIDField should never, ever change as it will be used as a key in relationships.
Here we do use a calculated ID's but it's much simpler. We just concatenate a single letter on the end of our serial numbers. Examples 12345C for contacts table, 12345V for invoice table and so on. This way when someone sends you an ID number you know what table they are referring to.
as it will be used as a key in relationships.
But to repeat, this should never EVER be used as a key in relationships!
Use an auto-entered serial number or UUID for that.
The only reason to use this ID in the first place is when legacy systems/business practices require it or when you need an "encoded meaning" label that must fit within a very tight space requirement. But you can support all of that in your database without using such an ID as a match field in relationships.
True. Still it shouldn't change as it's a unique reference to the legacy system. If you ever have to refer back to the old system, you'll need it.
As I said before, you have a good point, though the necessity for this can depend on how it is used in the original system.
Wow, you all are great, I was very impressed to see all your help.
Brain you are correct, the 1st four letter will depend on the name and the number wild change only if there is another name with matching 1st four letters.
I am not sure how that works with 2 tables, I will have to try out,
I with there was a way to just check unique in an calculation.
You guys rock!!! I can not Thank you enough!!!! I was not sure if it was even possible.