I have a number for every record in this format P01384. It starts with P00000. There are several records missing and I would like to see if there is a way to write a formula that would give me the numbers of the missing records in the sequence. Is this possible in Airtable?. I have found a way to do it exporting a .csv of the table and then, using a macro I found for Excel, find the missing numbers. A very convoluted way. Would love to just have this info in Airtable. Seems like it should be a simple thing but guess it’s not. Any guidance would be greatly appreciated.
Finding Missing Numbers
Hey there! There are definitely a few ways you could do this, but before I suggest a technical method, like using the scripting block, would you mind sharing a sample of the data and any linked tables?
@andywingrave Thank you so much for answering back. Here is the way the table is setup. No linked tables.
Example of a missing record. The scripting block sounds like a good idea. I have no scripting experience but can start looking to learn what I need to get this done.
The record number in the screen capture is a formula that is a reference to a single line text field. It is a reference as a deterrent for users to change this by mistake.
@andywingrave Thank you so much for answering back. Here is the way the table is setup. No linked tables.
Example of a missing record. The scripting block sounds like a good idea. I have no scripting experience but can start looking to learn what I need to get this done.
The record number in the screen capture is a formula that is a reference to a single line text field. It is a reference as a deterrent for users to change this by mistake.
Yeah - For a technical (long-term) solution - I think the scripting block is a good shout here - You could create some logic that says if Record A != Record B - 1, create Record C called “Record A +1” - Or something like that…
BUT
Very simply, if you’re only going to do this once…Create a new table of all the possible record values P1-P10000000, sort them alphabetically - Create a link to your original table and see which ones are missing. Once you have Change the “link-to” back to text, and you’ll have all your missing values. A bit more manual - But no scripting required.
Yeah - For a technical (long-term) solution - I think the scripting block is a good shout here - You could create some logic that says if Record A != Record B - 1, create Record C called “Record A +1” - Or something like that…
BUT
Very simply, if you’re only going to do this once…Create a new table of all the possible record values P1-P10000000, sort them alphabetically - Create a link to your original table and see which ones are missing. Once you have Change the “link-to” back to text, and you’ll have all your missing values. A bit more manual - But no scripting required.
Thank you so much. Not sure I understand how to proceed with the second, simpler solution. Will try to investigate more and see if I can figure it out. Incredibly grateful for your help.
Login to the community
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.