Count to create a unique record ID

[Ar_Ad]

Hi all...

I'm sure this has been asked before so apologies for asking again. I have searched but any article I think might give me the guidance I need ends up getting confusing...possibly due to my lack of understanding but I think at this stage, my best option is to ask myself.

I've created a purchase order system and it's all working fine except for one thing. For the purchase order number, I want it to be "Year-Cost Centre-Number" with the number component being a sequential count of previous orders for that same cost centre. That sequential count is where I'm running into bother - so far all I've managed to achieve is a sequential count of all purchase orders which works, but it's not my ideal set up. 

My base structure is:

• Purchase Requests table - this is the main table and is meant to manage the whole purchase order process
• Cost Centres - table of all cost centres, linked back to the Purchase Requests table
• Purchasers - table of anyone with permission to request purchases (not relevant to the PO number)
• Approvers - table of anyone with permission to approve purchases (not relevant to the PO number) - hopefully will be replaced by collaborators after a successful (🤞🏻) trial.
• Suppliers - table of any supplier purchases are requested from (not relevant to the PO number)

Any help would be greatly appreciated

[AirOps · Accepted solution]

Hi there! Welcome to the Airtable Community! 

Thank you for your really well structured question. You should be able to solve you dilemma with a creative automation. To set this up you will need the very 1st PO number assigned manually for each cost centre, but from there you can automate the rest! Below is an image of my mock PO table,  my "Number" Field corresponds to the "Number" in your PO number formula. 

Since you are already linking a cost centre  table you can use a roll up field on that table to tell you what the auto-number on the most recent previous PO is for that particular cost centre.

You can then look this number back up on the PO table and use a formula to calculate the next number in the sequence. In this case for cost centre 1 that number is 7, and for cost centre 2 it is 4. 

Now you have everything needed to set up the automation you need. This automation assigns the new PO with the next number in the system in the "Number Field". 

You can see the results in action here: https://watch.screencastify.com/v/A1R2TV0eSLw2gRLzFEuG

I hope this helps! 

[Ar_Ad]

Wow! This is brilliant. I would never have thought of using automation for this workflow, but I have it all working now. 

I thought I was in decent shape having all PO numbers count up sequentially but now giving each Cost Centre its own sequence is an amazing addition. I don't need to reformat existing workflows outside Airtable now as the generated PO number aligns exactly with what we have been producing manually.

As long as my team remembers to manually enter the first number for a Cost Centre, we are set.

Thank you so much!