Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

add +100 on each new row

Topic Labels: Base design Data Formulas
918 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Tecky_x
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey,

Something super basic to do in any excel, python script etc. but how to get in in Airtable?

I want to create some kind of ID for each customer. First customer has number 900100, and each new one (this is a child table) should get the number +100, so 900100, 900200 etc.

Is there any way to do so? (ps autonumber is not an option as you can edit it, if something goes wrong and you add a row by accident it ruins the sequence)

4 Replies 4
Chris_Parker
6 - Interface Innovator
6 - Interface Innovator

Sounds like you'll need to make an automation using the Script action to do that. The script would be triggered upon creation of a new record. It would do a look up of all records, identify the highest value, and write the next highest value into the record.

ScottWorld
18 - Pluto
18 - Pluto

As @Chris_Parker mentioned above, you would need to do this with an automation.

You can do this without any scripting at all by creating a field in a new table that keeps track of the previous number used, along with a formula that calculates the next number to be used.

Then, you would use that formula in your automation to set the number on your next record. 

Here is one method of doing this with an automation without scripting that uses two helper fields:

- {Is Latest Number} would be a checkbox field that is selected for only the record with the latest number.

- {Next Number} is a formula field that adds 100 to the {ID Number} field if {Is Latest Number} is checked.

The trigger for the automation would be when record is created.

The first action would be a "find records" action that looks for the record where {Is Latest Record} is selected.

The second action would be an "update record" action that sets the {ID Number} of the triggering record to the {Next Number} of the found record and also sets the {Is Latest Number} checkbox.

The final action would be another "update record" action that clears the {Is Latest Number} checkbox from the found record.

Here are some advantages of a non-scripting system:

- You do not need to know scripting.

- You do not need to hardcode table or field names, which might change.

- The native "show dependencies" feature of Airtable will know all the fields involved, making it harder for people to accidentally delete the fields involved.

The main drawback to this non-scripting method versus a scripting method is that it requires two additional helper fields.

You can also retain the advantages of the non-scripting method by moving the addition to a scripting action, instead of a formula field. A script that adds two numbers together is very simple to write, but I also include one in my Automation Helper Scripts.

Note that any system based on automations is subject to problems when new records are created faster than automations run. When multiple records are created within a few seconds of each other, they can end up with the same number. This is an issue whether or not you use scripting.

However, I also suggest you revisit why it is important to you to have sequential numbering in this way. While having unique IDs is important, sequential numbering usually makes people feel better but is rarely actually necessary in a functioning database system.

Hi.
I just want to clarify, like in Excel, when you click and drag down the little square

Alexey_Gusev_0-1711699407729.png

It will automatically continue the sequence. It can be done in any place of the column and with any step

Alexey_Gusev_1-1711700196705.png