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

    Create a Invoice Number?

    keith.r

      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

      Price

       

      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.

       

      TIA!