hi Julian, before we come here, we tried everything we can, including Zapier, the PRO supportive team from Zapier confirmed that neither “Storage” nor “Formatter” can perfectly support this function. and I contacted the Airtable, Airtable also confirmed that currently there is no solution, but I can post a thread here to see if some folks can have idea, but again, it seems it’s impossible.
hi @bdelanghe , can you kindly share this base to me, why i can not sucessfully create it which i followed your instructions
hi, @bdelanghe , this sounds like a detour solution, which I really appreciated. but I found a inconvenient, for example, now we are creating Book 5, which after fill out the Primary Key field, we have to manually lookup the “rand_aotonumber” match “5” in order to trigger the “ID”, can this be automatically?
While @bdelanghe’s solution can’t be automated (except through such a 3rd-party integration app as Zapier or Integromat, where time delay may be a factor), the mapping of
rand_autonumber can be performed less obtrusively than with a full manual lookup. For instance, if the two fields are consecutive, the value of
book_autonumber can be dragged (using the fill handle into the adjacent
rand_autonumber field. Alternatively, the value can be transferred using a quick, three-keystroke sequence (
Ctrl-V). If you don’t need the random value looked up concurrent with the creation of the record, you can wait until the day’s (hour’s, week’s, whatever quantum of time is appropriate for your app) record creation is done and then copy the entire
book_autonumber column to the
rand_autonumber column by selecting the
book_autonumber header, pressing
Ctrl-C, selecting the
rand_autonumber header, and pressing
Ctrl-V. Again, not an optimal solution, but at least a slightly more streamlined UI than manually looking up the applicable
rand_autonumber for each record.
@Xing you are correct after you enter the title you will have to copy book_autonumber field to the rand_autonumber field. @W_Vann_Hall offers quite a few quick ways to this. I recommend his last suggestion:
copy the entire
book_autonumbercolumn to the
rand_autonumbercolumn by selecting the
Ctrl-C, selecting the
rand_autonumberheader, and pressing
I published this base on Airtable Universe for you to check out.
If we’re talking overkill, I thought about mentioning the Airtable->Slack->Zapier->Airtable integration method as likely being the only way to achieve an acceptable response time. It’s not real-time, but it’s as near real-time as you’re likely to get currently. Compared with the other methods of streamlining linked-record creation, though, it’s an overly complex solution for the wrong problem…
AFAIK, Integromat still offers polling cycles of less than 15 minutes (5 minutes? 1 minute?). Unfortunately, if real-time is truly what’s needed, 5 minutes is essentially the same as 15…
hi @W_Vann_Hall, I don’t really care about the minute’s count real-time, if so, can you tell us what’s the solution for automating on the lookup with Integromat or Zapier instead of ctrl-C AND ctrl-v on every time editing?
Hi @Xing. I’d suggest starting a free Zapier account and getting a familiar with it before integrating into your daily workflow. They have a good guide here. If you are familiar with IFTTT it’s similar. In this case, it’s if ‘New Book’ (in view ‘NeedsID’) then add ‘book_autonumber’ to ‘rand_autonumber’ field. This is likely what you are looking for. And I’ve created a ‘trigger’ for my demo base that does that:
The template for the trigger is:
And the template for the action is:
Feel free to recreate this trigger for yourself. I’d also like to note that the free plan would only allow for you to update 80 books per month. If you need more you’ll have to switch to a paid account.
ps. best of luck.
I have a question on the action on Zapier,
can you specific what’s the value I shall select from the drop down menu? and why we need to select the custom value for record ID at here? thank you
What we are doing is updating the record that was just created. So we need to give it the ‘Record ID’ from ‘Step 1’. (This is the row ID from the new book that was created).
Zapier calls this a ‘custom value’ because you are passing in a value from another step. I suggest reading the help doc to get a better understanding.
HI @bdelanghe , thanks for your patient, you can imagine that I’m not as PRO as you, I think i’m in the last step of the victory. This “customer value” is really hard to understand.
There are many fields that when I click the dropdown menu are a long mix numeric and digits…do I just need to choose anyone of them? Could you please tell me more about this concept? or if you don’t mind could you please screen shot some images that this is so hard to understand for ppl like me who doens’t understahnd the coding.
Because this was (apparently) taken to private messages, I’m not sure if a solution was ever found to this problem. Whether or not it was, one question I have—which I couldn’t see answered in any of the other comments—is: why does the number have to be random? As long as it matches the other criteria—unique, four digits, and does not begin with 0—does it matter if it’s random vs sequential? If sequential is okay, there’s an easy way to get that. (Just throwing this out there as an alternate solution in case it helps anyone else.)
Make the second field an Autonumber field. Add a third field (ID) that’s a formula:
For the first record, its value will be 1000, and each record’s ID will increase from there. Now you can hide the Autonumber field, as you don’t need to see it.
hi Justin, thanks for your useful message, I’m sure there are some ppl would want to know the reason why I needed the random number.
Here is my situation I’m working on a project which is the inventory and I want all the stock number randomly.
For instance, I have Product A, and 100 pieces of product A, and Product B which is 50 pieces.
I want all the number randomly which the clients can not guess which piece is from A and which piece is from B.
For example, if I have A, 100-199, if the number is randomly, the clients can not know the “internal fact”.
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
But after a while if I want to add one piece on A product it will be
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.
But we have more than 100 products.
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.