0

format Invoice No in Invoices template

Dear forum,

 

Is there a way to format the Invoce No in YY-000 instead of YYYY-000? So for instance: 19-001 instead of 2019-001. I tried to modify the "Trigger on create" field in Invoice table like this:

 

let y := year('Invoice Date');
let c := cnt(select Invoice where year('Invoice Date') = y);
'Invoice No' := format(y, "YY") + "-" + format(c, "000")

 

But it didn't work. Any suggestions?

 

Thanx,

Daniël

5 replies

null
    • Sean
    • 5 yrs ago
    • Reported - view

    This works, but there might be other combinations that work also.

     

    let y := format('Invoice Date', "YY");
    let c := cnt(select Invoice where format('Invoice Date', "YY") = y);
    'Invoice No' := y + "-" + format(c, "000")

    • dlehmann
    • 5 yrs ago
    • Reported - view

    Thanx, but for some reason it doesn't work here... I'll experiment som more.

    • Sean
    • 5 yrs ago
    • Reported - view

    Screen Shot 2019-01-02 at 11.01.00 AM

    • dlehmann
    • 5 yrs ago
    • Reported - view

    Aaaah, had to create a new record of course! This is what does the trick, thank you.

    • Sean
    • 5 yrs ago
    • Reported - view

    If you want to update existing records, you can attach this code to a button and change the myYear variable to suit your needs.

     

    let myYear := "2016";
    for r in select Invoice where substr('Invoice No', 0, 4) = myYear do
    r.('Invoice No' := replace(r.'Invoice No', "20", ""))
    end

Login to reply

Content aside

  • 5 yrs agoLast active
  • 5Replies
  • 1780Views