1 Reply Latest reply on Jul 19, 2011 8:18 AM by philmodjunk

    Est/Job/PO/Invoice Number

    RichMarkie

      Title

      Est/Job/PO/Invoice Number

      Post

      Hi all, I need to create a database that has a common serial number across a few tables...

      For instance - I want to create an estimate for a customer - when this estimate is created, the serial number E30000 is created. When the customer confirms this estimate to an order - I can raise a job from the estimate and the job number becomes 30000. When I raise a purchase order to a supplier for this job - the PO number is 30000-1 for the first PO and further PO's for this job move up -2, -3 etc. When I raise an invoice to the customer I also need the job number to move across but I30000.

      I've had a search around for any links etc but can't work this one out...

      Rich 

        • 1. Re: Est/Job/PO/Invoice Number
          philmodjunk

          What you propose is not the optimum way to link your records. I sometimes think of these numbering systems as "ways for humans to pretend they're the computer"Wink because they assign a unique number, but with additional "meaning" included so that a human looking at the number can "decode" information from that number when additional status fields could communicate that info in plain language.

          Linking your records by a simple serial number (no letters, no sub series numbering attached) is much simpler to implement and maintain. I suggest using a hidden simple serial number to link your records and to use this numbering system (if you can't talk your boss/client out of using it all together) in a separate field used to "label" your records for search and sort purposes, but not for use in relationships between records.

          Using that approach, Your serial number field can show a value such as 3000 and an additional status field can display an "E" until the estimate becomes an Order. You can produce E3000 by combining the two fields in a calculation or by simply placing the two fields side by side on layouts.

          All your Purchase Orders from this parent record would be numbered 3000--that way they can all link back directly to the same original Estimate/Order record, but a separate field can number them in sequence and you can combine that number with the sequence field whenever you need to display PO 3000-1, 3000-2 etc. (And you'll need to put safeguards in place on this sequence numbering to prevent two different users from generating the same sequence number for the same PO if you host this database over the network.)