Changing Invoice ID's

[Ryan Gardner]

Hi... this may be in the wrong category, and I apologise in advance...

Basically, I have been using the Invoice starter solution for around a year to generate and record invoices for my work...

I have customised this a fair amount, and pretty much got it working as I would like. Having said this though, I have never changed the Invoice ID function. At the moment my invoices are automatically generating ID's as single numbers.

Instead of my invoice ID's just being a single number, I have decided to add a prefix to this.

I've done this successfully, and when I now create a new invoice, the id generated looks something like: RGINV0027

What I can't figure out though, is how do I change the invoice ID's of the 20+ invoices that I have already generated in the software? Cant figure out how to do this.

I want all of my previous invoices to follow this format of id (RGINV0000)

 

Thanks

[Steve Martino]

First and foremost, make plenty of back ups, do this on a backup, and check it multiple times before changing your working solution

First check your primary key and make sure you can modify it.  You'll change the settings back when you are done.

What I would do is create a script that does these steps.

Basic Steps:

Goes to invoice, captures primary key in a variable

Create second variable of new primary key by calculation which is basically: "RGINV" & OldPK

Check for related records, if no related records, skip

If related records (most likely line items), loop thru and change the foreign key to the second variable.

After looping thru Line items, change primary key in invoices

Repeat for next invoice.

I would watch it thru the script debugger to make sure it's performing correctly, pulls up right set of related records, etc.

Go back and make sure you lock down primary key from change.

Make sure you move that script out of your solution

Couple of thoughts.  Your ID fields will need to be text

Look at you ID field and make sure the next new record will be in the proper sequence

Second option-manually:

Make ID field modifiable.

Bring up an invoice, bring up all related records only in second window.

Use Replace Field Contents on the foreign key (match field) by calculation with "RGINV" & lineItems::fk  //assuming you are on line items.

Close second window, Change primary key on invoice by adding RGINV in front of the primary key.

Repeat for all invoices.

Edited May 23, 2016 by Steve Martino

[comment]

9 hours ago, Ryan Gardner said:

how do I change the invoice ID's of the 20+ invoices that I have already generated

Changing the existing IDs is rather trivial: show all invoice records and replace the contents of the InvoiceID field with a calculated result =

SerialIncrement ( "RGINV0000" ; InvoiceID )

However, this will break the relationship between existing invoices and their child line items. To restore it, you must replace the contents of the foreign key field in the child table with a similar calculation.

Both field types should be changed to Text before you do this. Make sure you have a backup, as there is no undo.