Issues with sequential number
S

Jorg provided me with this formula a while ago to create an auto-incrementing order number.

 

let myyear := format(today(), "YY");
let myOrderNr := max((select Purchases where substr('Order number', 3, 2) = myyear).number(substr('Order number', 6)));
if cnt(myOrderNr) < 1 then
'Order number' := text("OR_" + number(format(today(), "YY")) + "_0001")
else
'Order number' := text("OR_" + number(format(today(), "YY")) + "_" + format(myOrderNr + 1, "0000"))

 

I'm now trying to create a similar formula. Format is STATIC TEXT_ORDER DATE_INCREMENTING NUMBER

so

 

BLA_190625_001

The date is being pulled from a composite table, though I also have a formula pulling in the date in the table where this reference number is to be. I'm just having trouble wrapping my head around the concept, I don't know why. I understand I need to use the max()+1 string to look at the previous records number and so on...

 

I just can't even begin to process it haha.

Ninox Profile

Dear Sarah, 

Our weekly free webinar would be a good opportunity to bring up your question. Our experts will help you there and explain how to create your formula according to my sample.

At our Ninox webinar tips and tricks will be presented in the first 30 minutes, the remaining 90 minutes will be used for a live support where the participants' concerns will be solved. 

You can register here for the webinar, which takes place every Tuesday at 18 o'clock CEST: https://zoom.us/webinar/register/WN_zoSk2qyNSz6vLRFF1zlakg

Thank you. 

Best, Jörg

Reply