It's possible to assign a unique ID "on command" using a related table, but it's not clear why you would need to do this every time you "create a record" as this implies that you are not creating new custom records, but rather records that should be related to customer. Such records should be linked by a primary key defined in Customer that is a simple serial number or UUID string.
The "filing codes" that you need would be stored in fields in the customer table not these new related records. You would have one such value created automatically for a "new" customer (can be auto-entered serial number) that can then be changed to an archived value when you move the physical files to your archive storage. This new serial number can be generated via a script that creates a related record that contains a serial number for the archive serial number series of values.
Thanks for trying to help but I am not sure I understand what you mean as I am quiet new to Filemaker.
But I think you get what I am looking for specially in your second paragraph.
I think it is a good idea to keep the "FilingCodes" Field in the Customer Table.
Do you have any idea what the formula on creation of the auto-entered serial number would look like ?
My intuition is that it is pretty basic;
if it's Archive, just have to lookup the last highest value for "FillingCodes" that contains the letter "A" and increment by one;
if it's Normal, lookup the last highest value for "FillingCodes" that do not contain the letter "A" and increment by one;
But my skill are to poor to figure it out properly;
Doesn't every customer start out as "normal" and then change to "archive"?
I don't really see a "formula" here, but rather two different states for a given customer record with the second filing code assigned at the tie a "normal" customer changes to "archive". Or am I misunderstanding the process?
No, in my process a customer is either Normal or Archive, but a Normal does not necessary will become an Archive.
But it can, and an Archive can become a Normal again.
We only Archive customer that have few chances of coming back. and We archive directly on creation if we know the customer is not going to come back.
So on creation a customer is ether Normal(Regular if you want) or Archive.
And I cantype in manualy their PhysicalFileNumber, we just have to look at the next empty number of the in the Files drawer, but there are two drawers , one is near the reception and is used for Normal, and one is stored far away for the Archives (files we are rarely supposed to use again but it might happen)
Add two new tables to your solution. Give each these fields:
Define FilingSerial as an auto-entered serial number and in each table, set the next serial value as needed for your next label of that type.
For normal the normal customers table, define FilingCode as a text field with this auto-enter calculation: Right ( "00000" & FilingSerial ; 6 )
For archive, use: Right ( "A00000" & FilingSerial ; 7 )
link both of these tables to your customer table by customer ID fields and enable the "Allow Creation" option for the new tables.
To to generate a "normal" filing code, perform this script:
Set Field [ NormalFilingCode::_fkCustomerID ; Customer::__pkCustomerID ]
You do the same to get an Archive FilingCode but refer to the other table in the set field step.
You can can put the FilingCode fields from these related records on your customer layouts to show any assigned filing codes.
This method is pretty safe. If your file is hosted from a server and two users generate filing codes at the same time, they will not get duplicates. If you accidentally run the same script twice from the same customer record, the value will not change.