Help: Formula for transferring data to table when 2 items are selected in another table

[Malena_Spar]

Hello! I am looking for help creating a formula for data entry automation.

Screenshot 1: Table: Activities

Category column: Multiple select
Grants column: Links to another record
Workplan project column: Links to another record

Screenshot 2: Table: Grant 1

Deliverable: Long text
Activities: not sure what field type to use, formula?
Workplan projects: not sure what field type to use, formula?

I will enter data in the “Activities” table, including 1 or more categories for each activity, 1 or more grants for each activity, and 1 or more workplan projects for each activity. In the example, I have titled the activity “test webinar” categorized it as “webinars” made it apply to “grant 1"” and “WP project 1.” Since it applies to Grant 1 and the “webinars” category, I would like it to then automatically enter the Activity Name under “Activities” in the “Grant 1” table in the “conduct at least 5 webinars” deliverable column. I would also like it to automatically transfer the accompanying workplan project(s) from table: Activities, column: workplan project to table: Grant 1, column: workplan projects.

I plan to build this out with 5-6 grants that would pull information from the Activities table. There may be multiple grants and categories for each activity.

Thank you!

[Kamille_Parks]

Malena_Spar:

Activities: not sure what field type to use, formula?

A formula field in Airtable cannot “search through another table for data that matches the name of this table”. That’s spreadsheet methodology and Airtable is a relational database.

You already have a Link to Record field called {Grants} in your Activities table; I’m going to assume its linked to your {Grants Summary} table.

Instead of having 1 table per “Grant”, you need one junction table for “Deliverables” and “Grant 1” and “Grant 2” would be Views inside that singular table. To make it work, you would have:

• {Activities} => Link to Record field connecting to the Activities table
• {Grant} => Link to Record field connecting to the Grants Summary table
• {Workplan Project} => Lookup field of the linked {Activities} records’ {Workplan project} field

Each view in your “Deliverables” table would use a filter to show one grant at a time.

Airtable Support

Combining multiple tables into one table with multiple views

  In general, you can think of a table as a list containing all the people, ideas, or items of a specific type. For example, a table could be a list of blog posts or recording artists on your recor...