Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 03, 2023 06:35 AM
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:
Any help would be greatly appreciated
Solved! Go to Solution.
Mar 03, 2023 02:51 PM
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!
Mar 03, 2023 02:51 PM
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!
Mar 04, 2023 06:56 AM
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!