6 Replies Latest reply on Dec 23, 2016 8:25 AM by J_File

    Modify a serial number for different parts of database




      I want to be able to generate a serial number at a "Customer Enquiry" stage, but then keep that number as it goes through the process all the way through to invoice. There must be an easy way to do this, but can't quite work it out. All I would do is change some prefix before the serial number, and maybe add a suffix as well for multiple records. e.g.


      ABC-ENQ-00011  (Enquiry)

      ABC-QUO-00011  (Quote)

      ABC-JOB-00011-A  (Job Reference A)

      ABC-JOB-00011-B  (Job Reference B)

      ABC-INV-00011-A  (Invoice for Job A)

      ABC-INV-000011-B (Invoice for Job B)


      The splitting into A/B etc, may be because an enquiry may lead to multiple Jobs, but still from the same enquiry (and quote).


      I was thinking along the lines of generating the initial Enquiry number via the Serial UUID function, concatenate some text at the front. Then somehow use that serial UUID to follow the "Job" through to invoice, using different concatenation options for each stage of the process.


      Any help would be most gratefully received.



        • 1. Re: Modify a serial number for different parts of database

          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.

          • 2. Re: Modify a serial number for different parts of database

            Thanks Phil,


            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 ....



            • 3. Re: Modify a serial number for different parts of database

              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.

              • 4. Re: Modify a serial number for different parts of database

                Thanks golife.


                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!)

                • 5. Re: Modify a serial number for different parts of database

                  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.

                  • 6. Re: Modify a serial number for different parts of database

                    Yep fully understood Phil, and I will go down that route. Thanks for your help.