3 Replies Latest reply on Jun 27, 2017 10:17 AM by keith.r

    Create a Invoice Number?


      I'm working with a table from a in-house development POS system. This table is just a list of line items transactions without a way to relate records. I would like to synthesizing a Invoice Number for each record.


      The available fields are:

      Purchased Date

      Serial Number (unique)

      Customer ID (unique)

      Product ID



      My first thought was to synthesize a Invoice Number based on the Purchased Date and Customer ID. Basically I added the values together:


      Invoice Number = Customer ID + Year ( Purchased Date ) + Month ( Purchased Date ) + Day ( Purchased Date )


      I was feeling smug until further review I noticed the Invoice Numbers wasn't unique for Purchased Date + Customer ID calculation.


      Invoice Number 66481 = 1+7+2015 + 66458

      Invoice Number 66481 = 2+13+2015 + 66451

      Invoice Number 66481 = 3+27+2015 + 66436


      Back to the drawing board. Hopefully someone with better understand can point me in the right direction.