Conditional Autonumber? Help!?

[H_F]

Hi, thanks for any help anyone can give, I feel like my head is going to explode.

Essentially what I’m attempting to do is have an autonumber generate based on the project it is linked to. I am tracking forms that come in from different projects within the same table.

I need it to work so that If a record is associated with ‘Project 1’ it will generate the next number associated with ‘Project 1’ and if I then create a record associated with Project 2 it won’t carry on from the number generated from the ‘Project 1’ recorded created.

I feel like im explaining this so badly, so if anyone needs further clarification I’m happy to give it. I’m an intermediate user concerning Airtable but this is just baffling me completely!

[Rupert_Hoffsch1]

Hi there, if I understand correctly, you need an automation / script which generates an autonumber for different projects in the same table. First record for project 1 → #1. First record for project 2 → #1. Next record for project 1 → #2 etc.

Correct?

[H_F]

Yes, that is exactly what I need, is it possible?

[Rupert_Hoffsch1]

Of course. Do you have any experience with script writing?

[H_F]

As in writing formulas in Airtable? I would say only just beyond basic. Scriptwriting like coding, no I don’t have any.

[Rupert_Hoffsch1]

Ok. Do you have a Pro account?

[H_F]

I do have a pro account

[TheTimeSavingCo]

I’ve managed to get something similar to work by using the “Find records” automation to look for all the related records, for example “Project 1”, and then updating the field with that value

Consider:
Your table already has two records associated with “Project 1”
You create a record associated with “Project 1”
The automation looks for all records associated with “Project 1” and finds 3 records
The automation updates the record that triggered the automation run with the number 3, which you can get via the length value of the find result

You then create a record associated with “Project 2”
The automation searches for records associated with “Project 2”, and finds 1 record, i.e. the one that was just created
The automation updates the record that triggered the automation with the number 1

Let me know if you end up looking into this and have questions

[Kamille_Parks]

This issue was covered in the following three posts

Conditional Autonumber Ask the community

@Kamille_Parks discusses how to do this at length in this BuiltOnAir podcast: [[S09-E07] Full Podcast for 10-26-2021]

Update counter when action is triggered Automations

Set up your tables to have fields like these: [Clients] table: {Max Order #}: a rollup field pointing to the [Client orders] table with the MAX(values) aggregation formula to find the last order number associated with that client. {Next Order #}: a formula field that just adds 1 like this {Max Order #} + 1 [Client orders] table: {Client Order #}: a number field {Client Next Order #}: a lookup field pointing at the [Clients] table’s {Next Order #} field. Then set up your Automation to…

Update counter when action is triggered - problem when duplicating Base design

If your base is in a Pro workspace you can set the fake Autonumber field to have its permissions so no one can edit except Automations: [image] This way when you duplicate the record, the new record comes in with no field value. You could do that with a formula field: IF({Field A} = {Field B}, "yes", "no") Then have your automation filter for conditions where the formula outputs “yes”.

[Hamlin_Krewson2]

A conditional autonumber is very achievable with two related tables and zero automation (no need for Pro). You can do this by rolling up the recordIDs (or a UNIX timestamp) of related records, and then searching for the position of that number within that related record. I have more details in the following post.

Make a Tip of the Day to automatically post in Slack