Create number sequences with various prefixes



I’m trying to create a table that generates an individual item code based on the items supplier, department & category ID’s. I’ve explored a few solutions already with no result so hoping someone else might know a good way to achieve this…

Currently my ITEM table has a column called Prefix that concatenates the rolled up Supplier Code | Department Code | Category Code. This looks something like: 27511010

I want new items created in this table to be assigned the next code in their specific sequence and add the next one. So the first item would be 275110101001 and the next with the same prefix 275110101002. However, if the items had a different or new prefix (say 12511414) then it would start at 1 again, 12514141001

What is the best way to achieve this in Airtable?
Any help much appreciated! Thanks.