Create different rows based on 50 different conditions

[Ventrice_Lam]

Hi! I have a table called “Initiatives” - each “Initiative” has a multi-select for 50 states. I would like to generate a new row in a table called “Tasks” for each state selected for an Initiative.

Example:
INITIATIVES TABLE
Row 1: Initiative A | NY, NJ, PA

TASKS TABLE
Row 1: Initiative A - NY
Row 2: Initiative B - NJ
Row 3: Initiative C - PA

Is this possible? I’ve tried using Automations but unfortunately I need to be able to do this for up to 50 states, and the limit of 25 Automations prevents that. Thanks in advance!

[ScottWorld]

Welcome to the community, @Ventrice_Lam!

Instead of using a multi-select for your states, your best bet would be to create a many-to-many relationship with 3 different tables, and your tasks table would become your “join” table.

These would be your 3 tables:

1. Initiatives table
2. States table
3. Tasks table

The tasks table would be your “join” table, meaning that it would represent the intersection between your initiatives table & your states table. in other words, each record in the tasks table would contain one linked initiative + one linked state.

You could do the data entry from the tasks table itself, or you could do data entry from either of the other tables. (As long as everything is linked together properly, you can do your data entry from any of the 3 tables.)

You can learn more about many-to-many relationships here:

Airtable - Support

Airtable's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

[Ventrice_Lam]

Thanks @ScottWorld! Is there a way to make the data entry part a bit easier? Manually selecting 50 linked states in the Airtable UI is a touch slow - ideally would love to “select all” to create a task for each.

[ScottWorld]

If you wanted to assign 50 linked states to a record (or even 50 multi-select options to a record), you can do that with an automation. Just put commas in between all of your different values when you update the field.

[Ventrice_Lam]

@ScottWorld Sorry, I’m not sure I understand. I’m trying to create one Initiative record, select let’s say 30 States, and result in one Task for each Initiative:State (30 tasks in total).

The way I have it set up now, I’d have to create an Initiative record, click “Link to a Record from Tasks”, click “Add a New Record”, then click “Link to a Record from States” to select a state, and repeat 30 times. Is there a way I can set up some logic that says, when I create an Initiative record, for each State I link, create a Task"? If not, is there a way to make the process simpler?

[ScottWorld · Accepted solution]

Yes, you can do this with Airtable’s automations. When you create a new record, you can trigger an automation to create the new records for you.

However, you can only have up to 25 actions per automation, so you would likely need to split this up into 2 or more automations.

For less restrictions (i.e. almost no restrictions), you can write your own custom JavaScript to do this for you, or use a no-code/low-code automation platform like Integromat. Integromat is my favorite way to do this, because I don’t need to write any JavaScript code.

[Ventrice_Lam]

Thanks @ScottWorld! I figured it out using your suggestion of automations in combination with the suggestions from this post here: Creating multiple records based on a range of numbers

Thanks so much

[ScottWorld]

You’re welcome! Glad you were able to figure it out! :slightly_smiling_face: