5 Replies Latest reply on Sep 7, 2016 4:33 PM by philmodjunk

    Best Way to Create "Sub" Serial Numbers?


      Hello! Was going to ask this in the New Users group, but the link to create a post there is borked.


      So, I am creating a quotes database. We issue a quote to a customer, save it in this database, easy stuff.


      However, when the customer requests another version of the quote or wants an additional item added to the quote, the solution my boss and I came up with is to create what is essentially a "sub" quote on a separate, but related, table.


      What I can't manage is when I click "add item", which creates the new record on the second table, that the QuoteID field transfers and add something like .1 or .a to the end.


      I've seen similar questions but the answers either a) don't seem like they're for the same version of filemaker, b) don't make a lick of sense to a newbie, or c) are literally "don't do that" in the particular scenario.


      Any ideas? Any points to good tutorials? Am I missing something??




        • 1. Re: Best Way to Create "Sub" Serial Numbers?

          When I had a quoting solution, everything was "open" until the quote was accepted and locked down and pushed to an Invoice. Items could be 'removed' (but never deleted, because we know the client changes their mind!) Items could be 'added', but if they needed to be additions to previous quote, they were designated as additions (a flag field) and date added. Removed items could be brought back (another flag field), final quotes printed and archived (locked from further changes).


          This was a client's specifications, so get clarity on what might be needed.


          Pushing to another table may make it difficult to print the final line items, final totals and boiler plate (if any).



          1 of 1 people found this helpful
          • 2. Re: Best Way to Create "Sub" Serial Numbers?

            I don't know the "business rules" involved in your situation, but wouldn't it be better to create a new quote with the requested changes, and mark the old quote as "superseded by <newquoteid>"?  That way you get a historically accurate view of the quote history for a customer.  Just a suggestion.... 


            The other thought that occurs to me would be to simply add/remove items the customer has requested, and have a field for "change log" for the quote, with the user adding a note for each change made.

            1 of 1 people found this helpful
            • 3. Re: Best Way to Create "Sub" Serial Numbers?

              I would not  put sub-quotes into a separate table.


              I think you will need a script to generate the new quote number.

              Copy the current number into a variable.

              Put that and ".a" into another variable.

              Check that quote number does not exist.

              If it does, try ".b" etc.

              Create the new quote with this new number.

              1 of 1 people found this helpful
              • 4. Re: Best Way to Create "Sub" Serial Numbers?

                Thank you all for your responses. I will discuss with the powers-that-be.


                We had indeed discussed having every iteration of a quote be it's own new quote (with the original noted for record-keeping purposes), but in our business customers will often request quotes for multiple somewhat-related parts. A recent example had a customer requesting quotes for 6 different parts at once, so it would be nice to keep all 6 together under one umbrella quote rather than issue them 6 quote numbers. This is very similar to our PO system, where there's a PO number, but then the PO has up to 5 parts... so 25000 is the PO, then .1, .2, etc off the end to designate separate parts. Maybe I need to reverse engineer the current PO system to see how that designation system works?

                • 5. Re: Best Way to Create "Sub" Serial Numbers?

                  I would never ever link records via such a "sub serial" method that requires calculating your serial numbers.

                  If you need them for communication, presentation purposes and/or to keep the boss/customer happy, Put the field into your record and display it for find and sorting purposes but do not use it to link records in relationships.


                  There are a number of glitches--including the possibility of getting two records with identical ID numbers if two users generate a "sub quote" at the same time for the same customer, that are avoided if you don't do this. I speak from painful personal experience where I currently have to support a DB created by my predecessors where such changes to PrimaryKey ID's are SOP for certain situations and we waste a lot of time correcting data as a result.


                  Simply create a new ID number (serial number or text from Get (UUID) for each new record using auto-enter settings. Link a new record to a preceding record by setting a separate field (call it _fkPreviousQuoteID or something) in the new record to the ID of the preceding quote in the series. This scheme need not be visible to the users.