Autonumber based on another field?

[Tom_Miller]

Hi folks,

Im building a database for my business and have gotten stumped trying to make an inventory number that would increase numerically - but only relative to other records with a common field.

Here’s an example:

Currently we manually generate an inventory id that looks something like this: client abbreviation, two digit year, product name abbreviation, and then a quantity.

That looks like this: DD18DO01 which means Dunkin Donuts, 2018, Donuts, and then 01. If we had 3 donuts in our inventory, each donut would have it’s own numerical id at he end (01,02,03,etc…).

I understand how to do this part in airtable, where i’m struggling is in situations where I’d have say: DD18BG01 (BG for bagel), or DD18BC01 (Black coffee) and have each of this different ids increase incrementally in the same table. So if I added a 2 new bagels, I would generate DD18BG02, DD18BG03, etc…

What I seem to be running into is a formula to generate that ID automatically BY product with an increasing number.

Has anyone faced a similar issue?

[Jeremy_Oglesby]

If that is possible at all in within vanilla Airtable (which is doubtful), it will not be easy to achieve by any means.

The only straightforward way I can see to achieve this is to have each different product (Bagels, Donuts, Black Coffee, etc) in its own table, so that it is running on its own Autonumber generator. That, of course, is undesirable for other reasons.

You could try creating a table for each product that solely produces the Autonumber (ie, the primary field is an Autonumber field, and the only other field is a link to your “Products” table). Then in your Products table, instead of having a single-select or whatever type field you are using to mark a Product as a particular type, you use a link to the particular product table. However, this requires having a linked record field for each product type you have within your Products table; and you’d have to create a lookup for each product type to grab its Autonumber field value. Then you could use a formula field to find the appropriate Lookup field Autonumber value to use in the Product ID.

Again – no good solutions in Airtable, since records are not aware of the values of other records within the same table.

[Tom_Miller]

Ok that is what I thought. Seems like manual entry is the way to go for at least that portion of the inventory code.

[bdelanghe]

I use to manage inventory, so I definitely understand the need for sequential SKUs. I did manage to create a Frankenstein version of what you are asking for:

It uses a table where the primary field is the first four digits of the SKU:

What it’s essentially doing is finding the lowest auto number to find the first record. And then essentially removing that number. And then repeating. After all that the formula ‘ss’ creates a two-digit string. That is then joined with the first four digits in the ‘f_name’ formula.

I couldn’t make Item be a formula because it causes a circular error. Here is a link to the base:

Sequential SKUs

If your SKUs normally don’t go to a high number this might be the way to go. Otherwise, I’d consider doing something like this:

With a similar root table:

This would help a lot with manual entry and doesn’t require such a complicated structure.

[edit: uploaded table to Airtable Universe]