LOOKUP, ROLLUP, or find a FORMULA to pull content through different records in same table

[Patricia_Martin]

I have a base to keep track of the FESTIVAL PARTICIPATION of each of our FILMS.
In table 1, I record the participation of each FILM on different festivals (which I pull from a FESTIVAL DB in table 2). This means that on table 1, on the first field {FILMS} film1, film2, film3 are recurrent values, appearing in as many records, as festivals where they get invited.

In table 1, I include a text field named {SOCIAL MEDIA} where I include the film’s social media related profiles and hashtags. I do that on the first occurrence of each film on a record.

MY PROBLEM:
I can’t figure out a way to either LOOKUP, ROLLUP, or find a FORMULA to reproduce the info I entered in {SOCIAL MEDIA} for a particular FILM X in a record to another record in the same table where FILM X appears.

In my mind is like making an intra-table “related field” but I cannot figure out how to do it.!

I made the image below to better explain what I need, in case I did not expressed myself clearly.

airtable-1|700x268

[Kamille_Parks · Accepted solution]

You need to have a table for Films. In your Films table, every film will be listed once. That’s where the {Social Media} field should go.

Your Participation table should be linking to the Films table.

• If your primary field in Participation (Table 1?) is the name of the film, duplicate that field and convert the duplicate into a “link to another record” field pointing to your Films table.
  • Replace the primary field with something that produces a unique name for each record, such as a formula field with a formula like: {Film} & ": " & {Festival}
• If the {Film} field is not the primary field you can convert it as above without the need to duplicate it first.

The {Social Media} field in the Participation table would be “lookup”-type field that finds the linked Film’s {Social Media} filed from the Films table.

[Patricia_Martin]

Thanks Kamille! I tested your layout and it works!

I created a table for FILMS in addition to the two I already have: one for PARTICIPATION, and another for FESTIVALS.

I guess there’s no way around rather than creating a specific table for FILMS, even if it is only to pull the info of the {Social Media} field into the PARTICIPATION table.

Actually, in the PARTICIPATION table I am putting a formula field where I am gathering both the social media profiles and tags of a specific film at each specific festival:

{Social Media (from FILM)} & " " & {Social media (from FESTIVAL)}

That comes handy when doing social media promotion.