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

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!

• ###### 1. Re: Create a Invoice Number?

Although I don't think this is the best way to do it.  Maybe:

Invoice Number = Customer ID & "-"& getasnumber( Purchased Date )

• ###### 2. Re: Create a Invoice Number?

Why not just have a sequential serial number incrementing by 1 on creation acting as your invoice number?

Alternatively if you change your calculation to be Invoice Number = Customer ID & Year ( Purchased Date ) & Month ( Purchased Date ) & Day ( Purchased Date ) it will not add the numbers together, but put them in a string, but then what happens if a customer needs two seperate invoices on the same day?  your invoice number would still not be unique.

• ###### 3. Re: Create a Invoice Number?

I was so fixated on math that I didn't consider using a string. Doh! For post analysis of the data this is prefect.

Thanks for the suggestions!