Maybe there’s some deeper reason that’s implied in this explanation, but I can’t picture a scenario where I would care about what the client might or might not guess about how products are set up behind the scenes. If you don’t want the client to know how your products are set up, omit the product numbers from any document the client is going to view. In most stock scenarios, stock/product numbers are for internal use only. Even if the end customer/client sees them, they typically don’t care about the product numbering scheme. They care about the product.
Anyway, I’m not trying to dissuade you from going the random number route. I’m just having a hard time understanding the details of your specific situation. No need to explain further, though. If you were able to reach a solution that gave you what you wanted, that’s all that matters.
The most problem common on issue is going to be like this
A product
1001
1002
1003
B product
1004
1005
1006
But after a while if I want to add one piece on A product it will be
A product
1001
1002
1003
1007
B product
1004
1005
1006
This 1007 is weird, doesn’t it? But if all the numbers are randomly, there is no such a odd situation
The most problem common on issue is going to be like this
A product
1001
1002
1003
B product
1004
1005
1006
But after a while if I want to add one piece on A product it will be
A product
1001
1002
1003
1007
B product
1004
1005
1006
This 1007 is weird, doesn’t it? But if all the numbers are randomly, there is no such a odd situation
One way to solve that is to use completely different number groupings for A and B products. If A starts with 1001, perhaps B starts with 2001. If the A product line will have more than 1000 items in it, B could start higher, like 3001, or 5001, or whatever works for your situation.
Generating these numbers would be fairly easy as long as you track the A and B products in different tables. Their numbering formulas would be set based on their respective numbering schemes. In the A table, it could be Autonumber+1000, and for the B table, it would be Autonumber+2000 or whatever you decide.
If you’ve got more than just A and B, spread out the number groupings as you need for your products. If necessary, maybe go to five digits instead of four, or six digits, or whatever makes sense.
One way to solve that is to use completely different number groupings for A and B products. If A starts with 1001, perhaps B starts with 2001. If the A product line will have more than 1000 items in it, B could start higher, like 3001, or 5001, or whatever works for your situation.
Generating these numbers would be fairly easy as long as you track the A and B products in different tables. Their numbering formulas would be set based on their respective numbering schemes. In the A table, it could be Autonumber+1000, and for the B table, it would be Autonumber+2000 or whatever you decide.
If you’ve got more than just A and B, spread out the number groupings as you need for your products. If necessary, maybe go to five digits instead of four, or six digits, or whatever makes sense.
But we have more than 100 products.
Best regards,
But we have more than 100 products.
Best regards,
In that case, consider a segmented product code, something like 101-001, where the first part is the base product type, and the second part represents the variation of that product. With three digits in each part, you could have a thousand products (or 900 if you don’t want leading zeroes in your product codes), each with a thousand variations. If that’s not enough options, add more digits to accommodate more products/variations. Obviously this would be cumbersome to track with each product in a new table, but with the help of Zapier or Integromat, the numbering scheme might still be something you could semi-automate. Here’s a quick mockup of what that might look like:
The Product Categories table is the master list of products by category, with an autonumber setup like I described above to give each category its own category code. The Products table is where all of the actual products live. When adding a record for a new product, you would choose its category in the Category field, which then auto-fills the Cat. Code field. Zapier/Integromat would then recognize the new record, count how many products currently exist in that category, and fill the Product Code field in that record with the next variation number in the sequence.
The building of the full code in the Product Code field may also be doable with formulas, though I can’t yet wrap my head around how that would work. Most likely it would require another table and some combination of lookups/rollups/etc. However, the issue I see there is that if you ever re-order your records in the Products table, the formula would likewise re-number the product codes with different variation numbers than they had before. By using an integration service, the Product Code field data is filled once by the service, and would stay unchanged if the record order ever shifts.
In that case, consider a segmented product code, something like 101-001, where the first part is the base product type, and the second part represents the variation of that product. With three digits in each part, you could have a thousand products (or 900 if you don’t want leading zeroes in your product codes), each with a thousand variations. If that’s not enough options, add more digits to accommodate more products/variations. Obviously this would be cumbersome to track with each product in a new table, but with the help of Zapier or Integromat, the numbering scheme might still be something you could semi-automate. Here’s a quick mockup of what that might look like:
The Product Categories table is the master list of products by category, with an autonumber setup like I described above to give each category its own category code. The Products table is where all of the actual products live. When adding a record for a new product, you would choose its category in the Category field, which then auto-fills the Cat. Code field. Zapier/Integromat would then recognize the new record, count how many products currently exist in that category, and fill the Product Code field in that record with the next variation number in the sequence.
The building of the full code in the Product Code field may also be doable with formulas, though I can’t yet wrap my head around how that would work. Most likely it would require another table and some combination of lookups/rollups/etc. However, the issue I see there is that if you ever re-order your records in the Products table, the formula would likewise re-number the product codes with different variation numbers than they had before. By using an integration service, the Product Code field data is filled once by the service, and would stay unchanged if the record order ever shifts.
I like what you came up with for product codes with category codes. This can be really helpful to gain information about a product from just glancing at the product number. Incredibly helpful if you are pulling stock off of shelves and you don’t want to look it up in the database. Often set length abbreviations are used rather than numbers. So if the length was four the categories might be:
- POST => poster
- MSPD => mousepad
- BOOK => book
The issue with a human readable SKU (Stock-keeping Unit) system is that it takes more work to come up with. It might be useful to have that information. Sometimes it’s easier to just use a simple part number. And I agree with @Xing about random being better. What’s important is that the number is unique. Letting the numbers run sequentially implies that they follow an order. These rules will often break. So even though its extra work removing the ability to make this assumption can be incredibly helpful.
Side note: if anyone wants to generate random numbers within Airtable:
- Create a formula field with
RecordID()
- Sort by previous field
- Create ‘Autonumber’ field
- Delete ‘RecordID’ field
The Autonumber will now be shuffled. This means that your range will be only as big as your number of records. (You can also leave the ‘RecordID’ field and turn on auto sort with will shuffle these records in which could be helpful if you wanted a “Random Sort”)
@Xing -
A little (well, a lot) late, but the solution is here. You’ll need to tweak it a bit to make sure the values are all > 999, though.
