How or Can you do this? Auto Increment with Flare

So I've looked through the community and at the PO Approvals template. I want to be able to formulate a PO number like this:

Incrementing Number - Year - Location - Dept


first 4 digits would auto increment . last 2 digits of current year. when entering their purchase order request they would either specify or if we can tie a user to a location then auto populate the location.

They would pick the Dept as well. It would be nice to be able to tailor the Locations to the depts and the expense category they can use - based on user.

On the location we would take first 3 letters of location chosen to create the PO Number

On The Dept- We would take the first 6 letters for the PO Number

Ooo! I just did this the other day with my Transaction Reference Number - Similar concept to the PO.

Incrementing Number
You could essentially use the number of growing rows in the table as a cheat, which I did.

However, being financial, you won't want to have duplicates if data was ever to be deleted, so it might work better if you had a Key-Value table which allows you to have a seed number which you increment to everytime you request a new number.


This one is easy.
Ensure you get YEAR with YEAR(TODAY())
Then use RIGHT, to get the two digits on the right

Location, Dept, Expense category
These are all tables and relations!

1 Like

Hi @MJ , nice to see you here again! :slight_smile:

@karbonphyber gave some great ideas! I'm just going to add a couple other notes too.

For a guide on how to create unique row IDs, you can follow the steps in this article to increment an ID by one for each new PO order. And just as @karbonphyber noted that you can use RIGHT() for the year, you can use LEFT() to specify the characters in the text to write in the last pieces of the ID.

Tying all the pieces together, your formula would look like:

=CONCATENATE([Unique ID],"-",RIGHT(YEAR([Date]),2),"-",LEFT([Location],3),"-",LEFT([Department],6))

Hope this helps, and let us know if you have any other questions :honeybee: