Making a "combined" invoice number?

[Dean D. Babic]

Hi all,

this might be interesting for someone.

How to create Invoice Number with Letter and Year and Number, like this:

"I" is letter

23 is year (obviously for this year, next is 24)

Number is increased from last one (take the zeros into account)

which makes it I-23-00001, I-23-00002, etc....

So might be like I-23-09999,I-23-10000...

Any solution?

[Yeoresley Dominguez]

en una aplicación que se necesita un numero consecutivo  para cada año, opte por esta variante en e modulo de servidor , solo restaría adicionar en la concatenación las letras.

def getnumero(item):
    recs = item.task.execute_select("""
    SELECT
    case  
      when count(*) = 0 then CONCAT('CT.',RIGHT(year(GETDATE()),2),'-00',1)
      when count(*)+1 < 10 then CONCAT('CT.',RIGHT(year(GETDATE()),2),'-00',count(*)+1)
      when count(*)+1 > 99 then CONCAT('CT.',RIGHT(year(GETDATE()),2),'-',count(*)+1)
      ELSE  CONCAT('CT.',RIGHT(year(GETDATE()),2),'-0',count(*)+1)
      end numero
      from FICHA_CT;
    """)
    for r in recs:
        return r

[Yeoresley Dominguez]

Otra opción que me ha sido útil casualmente en un aplicación de facturas es mediante el modulo de cliente, después de haber trabajado con el campo fecha de la factura, de forma automática se crea e introduce el numero de la factura teniendo en cuenta un consecutivo dentro del año

function on_field_changed(field, lookup_item) {
var item = field.owner;
if (field.field_name === 'fecha' ) {
var ann,
rec,
ent,
ide;
ann = item.fecha.value.getFullYear();
item.anno.value = ann;
item.mes.value = item.fecha.value.getMonth()+1;
ent = task.entidad.copy();
ent.open();
if (ent.tcp.value){
ide = ent.tcp.display_text;
}
else{
ide = 'Fac';
}

let ret = task.facturar.copy({handlers: false});
ret.open({fields: ['id'], funcs: {id: 'count'}, where: {anno:ann}});
rec = ret.id.value;  

if (rec <=0){
item.numero.value = ide + '-' + ann+'-001';
}
if (rec >0 && rec<9){
item.numero.value = ide +'-' + ann+'-00'+(rec+1);
}
if (rec >8 && rec <99){
item.numero.value = ide +'-' + ann+'-0'+(rec+1);
}
if (rec >98){
item.numero.value = ide +'-' + ann+'-'+(rec+1);
}  

}

}

saludos y espero le haya sido de ayuda como muchos de las ideas y soluciones que ustedes suben

gracias a todos

[Dean D. Babic]

Thank you, will try that!

[Dean D. Babic]

This thread is related:

https://groups.google.com/g/jam-py/c/VbG9kIqP95o/m/6RCdBziZAQAJ

At the and, I resorted to below code which does not have 0 in front, but using the ID and full year.

It only "fires" on a new record.

function on_before_post(item) {
    if (item.is_new()) {
        let acc = task.account_transactions.copy({handlers: false});
        acc.open();
        while (!acc.eof()) {
            // console.log(acc.id.value);
            acc.next();
        }        
        var ann;
        var next;
        next = acc.id.value + 1;
ann = item.entry_date.value.getFullYear();
        item.number.value =  'I-' + ann + '-' + next;
        item.refresh_record();

}
}

So the number 10029 is the NEXT ID in the database. Because it is executing before the POST, the NEXT 

is the correct value (well, just a guess here, if "million" people are using it maybe is not correct). However,
the downside is using EDIT LOCK is a must for the table. 

@Andrew, is the above statement correct for many simultaneous connections creating a record?

[Nikola Popovic]

I am in the process of evaluation of the two platforms: 1. nuBuilder (PHP) and 2. jam.py. The goal is creating Record Keeping system. The main problem is to increment record number automatically from the last record number, but in the heavy multi user environment (i.e. 100 users). Each record must have a unique serial number during a year (Record identifier is not only serial number, it contains several parts - classification etc. ). Yeoresley Dominguez (and Babic also) in his messages give correct solution, but I am interesting are there any answer on question: "@Andrew, is the above statement correct for many simultaneous connections creating a record?".
Thanks in advance

[Harshavardhan Patil]

This might be a bit crude, but I am using the following code to generate various serial numbers for Sales Orders, Customer Numbers, Work Orders, Job Cards etc. 
I am doing it on the server side, before posting the record. It works for now.  Still need to consider other cases such as when a record is deleted etc. Still working on these. 


def on_apply(item, delta, params, connection):
    d = delta
    d.edit()
    so = item.task.so_list.copy()
    if (d.rec_inserted()):
        so_num = generate_so_num(item)
        d.so_number.value = so_num
        d.po_status.value = 1
        d.so_status.value = 1
    d.post()

def generate_so_num(item):
    copy =item.copy()
    copy.open()
    today = datetime.date.today()
    year = (today.year - 2000)
    if copy.record_count():
        copy.last()
        last_sonum = copy.so_number.value
        last_sonum_year = int(last_sonum[2:4])
        if last_sonum_year == year:
            slno = int(last_sonum[-3:])
            so_num = 'SO' + str(year*1000+slno+1)
        else:
            so_num = 'SO'+ str(year*1000 + 1)
    else:
        so_num = 'SO'+ str(year*1000 + 1)
    return so_num

This generates a number of the form - SO23001, SO23002 etc. 
Client Side:

function on_after_apply(item) {
item.refresh_record();
}

[Dean D. Babic]

Cool, 

thanks for contributing.