Why do you want to "encode" such information into an ID? It can be done, but greatly complicates your solution design, can create duplicate values if you aren't very careful and usually is not needed in a FileMaker solution as you can display the relative info as plain text during a relationship.
You can auto-enter a serial number or UUID text in each customer record to uniquely identify each customer.
In a related table, you can create one new related record for each Inquiry, Quote, Job, etc each with it's own unique ID defined in the same fashion. Another field can link this table to your customer table by the customer ID value. A status field can plainly identify the type of record in this table as "Quote", "Job", etc with no need to "encode" that info into an ID value.
I know of three cases where you end up having to do this kind of "secret decoder ring" ID even though a relational database does not need it:
1) There is a legacy system in place that you have to support with your new solution that uses the ID.
2) There are printed labels that you have to produce that "pack" this info into the limited space of the label so that knowledgeable humans can "decode" the info at a glance without having to look up the ID in your database solution.
3) The client/your boss insists on this system despite all efforts to convince them that it is neither necessary nor a good idea.
In all such cases, DO NOT link your records by this ID. Use a simple straight up auto-entered serial number or UUID as the key used to link one set of records with another. Set up such an ID field as something that can be printed, used for sorting and searching or just displayed on relevant layouts as just another "label field" in your system and not a match field used in your main "backbone" relationships.
You have hit the nail on the head in your suggestion number (2) in that it is to allow the human brain to get their head around it :-)
The main reason for wanting to keep the number part the same is to be able to easily reference the whole job from start to finish. Obviously, not every enquiry will convert into a quote and not every quote will turn into a job, but I would prefer to not have multiple 5 figure serial numbers flying around for the same job, and keep the same one that is generated at the enquiry stage to stay with it until it hits the end of the line. If that means that the end of the line at the quote stage then so be it, but if it means the end of the line at the invoice stage, then so be it as well.
When it comes to me actually linking the records together, pdf course you are right, I would be using a uuid for this to avoid any potential duplication errors from creeping in.
I suppose the ID in this instance is not to be used as an ID for Filemaker to do anything with, but more for ease when it comes to referencing the job and the parts that make up the job (enquiry to invoice).
Are you saying that if I just allocate this serial id at the start of the process (i.e. enquiry), I just then keep that running through with the rest of the parts (quote, job, invoice) and not faff around with all the other prefixes and suffixes? As I mentioned before, I would still utilise uuids for linking them together in the relationship diagram.
I am perhaps looking at this from a rather old school perspective where we use to use spreadsheets and paps records to keep track of such things ....
But if the requirement is there, despite using a hidden UID, I had set up a Serial Numbers table where each record represents a data table. If a new record is created, I look up the next value in the linked record, insert it as the number value and add up the number in the Serial Numbers table record after commit.
For example, a client uses a meaningful project and task numbering system. You can not ask the client to forget about it. So, even though the relationships are maintained using meaningless ids, in parallel also such clients number system is maintained. But to enforce it may require quite some effort in scripting.
Fortunately, the client does not have a requirement to do this, so I can do what I wish on this, just want to give them something that makes sense and is robust enough to not cause issues at a later date! Their current method of using lots of different unjoined methods means that quote and job numbers are not linked, and they don't even log their enquiries on an official system (unless you count post it notes!)
My advice is not to do it if you are not required to do so. It's much more trouble than it's worth in a modern relational database solution.
but I would prefer to not have multiple 5 figure serial numbers flying around for the same job
Precisely what you wouldn't want to do here and which, if you follow my suggestion, is completely unnecessary. The ID's would not be visible nor accessible to the user. You'd select records--whether the customer, the job, the quote, the... by name from a list and the ID's would do the linking behind the scenes.
So I don't see the need, at all, for such a complicated system of ID's, you would only need a unique ID for each customer, a unique ID for each Job, quote, etc with a relationship based on Customer ID to link the customer to their other records where name, description and date enable users to tell one from the other.
Yep fully understood Phil, and I will go down that route. Thanks for your help.