Field Formatting - Auto Entry - Customer ID
I'm new to Filemaker and wonder if someone may be able to help with regard to formatting a field.
I would like a field to automatically generate data based on the first three letters of a customer's surname field followed by a sequential 3 digit number.
For example, a customer called John Smith would have a customer ID of SMI001. A second customer called Peter Smith would need to have an ID of SMI002. If a third customer was called Williams they should have an ID of WIL001.
If it is possible to perform this calculation, I hope to be able to create a relationship with other tables whereby once the ID is entered the customer's details appear. For example, customer details appearing on a quotation table.
Any help would be much appreciated, I have already spent hours trying to work this out myself, but I'm confident someone on the forum will have the knowledge.
Many thanks in advance.