Manually/automatically set new invoice sequence number for new year

[mheiduk]

Is your feature request related to a problem? Please describe. It is common to start a year with a new sequence number for invoices, eg. 1/2021, 2/2021, ... and in the new year restart at 1/2022, 2/2022, ... At the moment, the sequence number will use the number from the last invoice.

Describe the solution you'd like Two possible solutions: Manually reset the sequence number in the settings via an input field, or automatically reset it when a new year starts.

Describe alternatives you've considered Tried to manually set the sequence number during invoice creation, but the next invoice won't take it into account.

[mohitpanjwani]

This feature is already in progress.

We're working on adding a YEARLY_SEQUENCE component to number customization settings which will allow this.

Also we're adding the ability to update the sequence at invoice level. For example If someone wants to start with sequence 10 instead 1 from a particular invoice then that will be possible.

[sebmatton]

Hi,

I'm also facing the same issue. With previous version of crater I was able to update the sequence number manually then all next invoices would increment starting from this new value, but it is no longer functional. For now I'm still getting 2021 values instead of 2022 (I'm using 7 digits counter).

It is still the beginning of the year so I can handle it manually for now, but I would be nice to fix it soon if possible :-)

What is your schedule for this feature ? Is there something we can do to help ?

[SimonErich]

Hi, facing the same problem. We need to write new invoices for this year and would like to start from 0. Is there currently a workaround to reset the numbers withou tthis feature? Tried to set the first invoice to 0 in the form, but it doesn't let me do it.

[sebmatton]

The only way to temporarily solve this issue for me was to edit the database.

The system currently uses the highest value of the sequence_number field in the invoices table, then increment it for each new invoice (specifying a custom invoice number when filling the form doesn't affect this DB field).

My solution for now was to edit the first invoice row in the DB and set the sequence_number value to a new value (which must be the highest value of all invoices), in my case: "2022001". You must also edit the invoice_number field (which is the invoice number displayed in the GUI and the PDF) accordingly either in the DB of using the form.

Then all new invoices will continue with the new values (2022002, 2022003,...).

NOTE: Be careful when you edit the database, this could permanently damage all the data in case of misuse.

[swiderskimarcin]

any updates on this ?

[unapersona]

I ended up adding one line to the setNextSequenceNumber function to get the max sequence number only for the current year.

It works for now, but please be careful if you change your code.

$last = $this->model::orderBy('sequence_number', 'desc')
    ->where('company_id', $companyId)
    ->where('sequence_number', '<>', null)
    ->whereYear('invoice_date', date('Y'))
    ->take(1)
    ->first();

[swiderskimarcin]

@unapersona There is a problem with this solution. It's now strictly linked to invoices but setNextSequenceNumber works also with payments and estimates, so you won't get next payment or estimate number ... Thats an issue!

It will work if you use 'created_at' instead of 'invoice_date', 'created_at' exists in invoices, payments and estimates, but now you will get new sequence even if you set invoice, payment or estimate date as previus year (but is that an issue ? i'm not sure). That will always depend on date when you create document but it's not the 'invoice_date', 'payment_date' or 'estimate_date' which you can change manually.

[unapersona]

Thanks @swiderskimarcin for the heads up, so far I only tried it on invoices.

[enoversum]

setNextSequenceNumber

@unapersona If you don't mind me asking, which file did you alter exactly? Just trying to make my life easier instead of sifting through all the PHP files in the package :) .

[swiderskimarcin]

file to update is /app/Services/SerialNumberFormatter.php

[enoversum]

@swiderskimarcin Perfect, thank you!

[lakano]

Thank you for sharing this patch ! This was blocking me since yesterday to send new invoices!

[enoversum]

Just a word of warning: If you had already created invoices this year before applying the patch, the sequence number will still be going up for your invoices from last year's number for forthcoming invoices, despite the patch being applied. In my case I had my first invoice created without the patch, so it was 2023022, even though I changed it manually to 2023001, and the database still stores the sequence number as 022. You'd have to go into your database directly (PHPMyAdmin or similar), edit the invoices table and change your first invoice’s sequence_number to 1, so consecutive invoices will now count up properly. See screenshot.

[djchoyr]

Just a word of warning: If you had already created invoices this year before applying the patch, the sequence number will still be going up for your invoices from last year's number for forthcoming invoices, despite the patch being applied. In my case I had my first invoice created without the patch, so it was 2023022, even though I changed it manually to 2023001, and the database still stores the sequence number as 022. You'd have to go into your database directly (PHPMyAdmin or similar), edit the invoices table and change your first invoice’s sequence_number to 1, so consecutive invoices will now count up properly. See screenshot.

I have tried to change the variable but I have not been able to do so. I use pgadmin and that value of the table appears as attached in the image.

[enoversum]

@djchoyr I'm sorry, I'm not familiar with pgadmin, nor do I speak Spanish ☺. You could always try adminer for editing your database with just a simple PHP file you throw on your server and then login through the browser. My screenshot is from exactly that, but of course, that's not the only database editor.

[djchoyr]

Thank you very much. I'll try adminer to see if there's luck 😉

[swiderskimarcin]

@djchoyr If you dont have a lot of invoices created in 2023, just remove them and add again / after this patch the number will start from 1 - you just cant have any invoice with date of 2023 before patch

[swiderskimarcin]

@djchoyr or stick with @enoversum tip but dont change anythink in db table definitions just update the invoice row and manually change "sequence_number" for each invoice starting from 1 (same for payments, estimations) and invoice_number in way that you have it specified in settings ( this is formatted inv number - you can specify this sequence in settings)

[djchoyr]

I tried to use adminer but it tells me that the database is not compatible 😞

I use crater and adminer in docker.

A message comes out: Unable to connect to PostgreSQL server: SCRAM authentication requieres libpq version 10 or above

[djchoyr]

I have not yet created invoices in crater for 2023. I was waiting to be able to use the app correctly.

If I modify the php file, I can't generate the payments with the correct number. The field comes out empty

[swiderskimarcin]

@djchoyr You must change the code to check created_at field not invoice_date to work with invoices, payments, estimates, please read whole thread, I've uploaded image before and explaind it.

[djchoyr]

I tried it. But it didn't work for me. The invoices were created in the correct order. But the field of payments appeared empty.

I'll try again and comment on the result

[swiderskimarcin]

@djchoyr payments will be empty if you add line of code to check invoice_date, byt if you change code like this it will be ok.

https://github.com/crater-invoice/crater/issues/691#issuecomment-1369266830

ps/ same think like in invoices you can't have payments, estimates in 2023 before patch.

[swiderskimarcin]

@djchoyr and don't touch db if you dont have invoices, payments, estimates in 2023, just add a line of code.

[djchoyr]

@djchoyr payments will be empty if you add line of code to check invoice_date, byt if you change code like this it will be ok.

#691 (comment)

ps/ same think like in invoices you can't have payments, estimates in 2023 before patch.

I have tried to change the php file. When I edit it and leave it as in the image, the invoice and payment numbers remain as they were. If I leave it as invoice_data, the invoice numbers are put to one, but when you generate a payment it does not look like the reference in the payment field.

[swiderskimarcin]

@djchoyr

So you have invoices and payments that was CREATED in 2023 (in db you probably have created_at as 2023 year in your case inv and payments). No other option! change their created_at field to last year if thats helps you.

You can't relay on invoice_date because you always will lose numbers for payments and estimates.

[djchoyr]

@djchoyr

So you have invoices and payments that was CREATED in 2023 (in db you probably have created_at as 2023 year in your case inv and payments). No other option! change their created_at field to last year if thats helps you.

You can't relay on invoice_date because you always will lose numbers for payments and estimates.

I have created some test invoices. But I have deleted them again before making the changes in the php. I have made the changes to the file without any invoice, and without any payment in 2023. 🤷‍♂️🤷‍♂️

[swiderskimarcin]

@djchoyr Try to understod SQL query and look for invoices or payments in db where year 'created_at' is 2023, I cant help you more.

[djchoyr]

@djchoyr Try to understod SQL query and look for invoices or payments in db where year 'created_at' is 2023, I cant help you more.

Al final he conseguido solucionar el problema. Entrando en la base de datos, he eliminado la columna sequence_number de las facturas y también la misma columna de los pagamentos. De este modo ya me permite crear los pagos con los números correctos y lo mismo ocurre con las facturas.

Gracias a todos. Un saludo