Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Formula for Sublist with Auto-number or similar counter

62 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello Airtable community!

I’ve created a table to keep track of a list of background extras on a film. The way the columns are currently set up is as follows:

[Character Number] [First Name] [Last Name] [Character] [Scene] [Shoot Day] and so on.

Each person is given a character number, and right now I have that column’s field as Auto-number. We already have records input for all characters that will be in the film, and just need to fill in names. The problem is that some people will have multiple characters assigned to them and we don’t know which people or which characters yet. If possible, I’d like to find a formula to assign a subletter to a record’s [Character Number] when a [First Name] and [Last Name] are input into a record that match an already existing one.

For example:

This is how the list would look before a new person is input

001-----MIKE--------WALLACE-----REPORTER
002-----SONYA-----PARKS---------PROTESTOR
003-----ADAM-------PHILLIPS-----PROTESTOR
004---------------------------------------JANITOR
005---------------------------------------JANITOR

And if we input one of the same people into a new character it would become this

001-----MIKE--------WALLACE-----REPORTER
002-----SONYA-----PARKS---------PROTESTOR
003-----ADAM-------PHILLIPS-----PROTESTOR
002B—SONYA-----PARKS--------JANITOR
004-------------------------------------JANITOR

Notice that after the new name is input, what used to be 004 changed to 002B and what used to be 005 changed to 004.

This will save us a lot of time and effort if we don’t have to manually index the hundreds of people we have coming, so any help at all is greatly appreciated.

Thank you!

1 Reply 1

My post got flagged for some reason and in the time it took to get reviewed I kept digging around and found a way to do it with automation. Now the big problem is the automation cap for the free tier. Is there a work around to allow more than 100 automation actions/month? I don’t think I’d need 5000 and the boss wouldn’t be willing to upgrade anyway.