Drawing from multiple columns to make new table or column

[Fred_Bueltmann]

Perhaps this is an introduction/need for pivot tables. I have a form collecting multiple submissions per brewery. I'm trying to create a menu that could draw from a table that shows the summary of those 4 submissions per brewery.

If I create pivot table, which I'm not experienced with, it seems to count each column as an extension, so I would need to upgrade to accomplish this. (Which I don't want to do, if I'm not certain it's going to work.)

I'm relatively familiar with Airtable, but not in advanced manipulation. I have it setup with breweries as the primary records, because they're using a form to submit multiple beers per entry. However, now I want the beers to be the identifying record, while still maintaining their brewery info (contact, distributor, etc.).

Any help will be appreciated and you will be supporting craft beer in the process!

Thanks in advance.

Cheers.

[Meredith_Scrog1]

Hi, I hope this can answer your question in some way! What I would do is set up two tables within your base - one for form submissions as you have, and then one for the beers. In the beer table, each beer type would be the identifying record (primary key) and not specific to the brewery. I would create a linked field in your other table to link it to the beer table. This way, when someone fills out the form, they are selecting from the linked field drop-down that appears to them. The drop-down references data from the beer table. When you go to your beer table, you can see the submissions attached to that beer. Cheers!

[Fred_Bueltmann]

Thanks Meredith. That is encouraging and where I was trying to head, with the exception being that their form submission is what is supposed to populate the beer table. (vs selecting from a drop-down.) If I can get more than one column per brewery to link to a new table in the same single column, that would help me summarize the 4 beers x many breweries into one beer table. Does that make sense? Maybe a lookup field can help me?

[Meredith_Scrog1]

Ahhh I see what you're saying and what your issue is. Using a linked field would be different than a drop-down, but it would require the beers to be pre-built in the other table. A workaround could be the following -

1. Still make it a linked field. Pre-populate beers you think breweries will select. Make one option in your 'Beers' table called 'Other'.

2. In your base with your form (for breweries), add a single line text field called 'Other'.

3. When setting up your form, include the linked field for Beers and your single line text field called Other. Then, you only make the 'Other' field shown when it's selected in the previous option.

4. Create an automation that when 'Other' is known, create a new record in your other table. It will automatically create that beer in your Beers table for other breweries to select!

Wonky screenshots included - sorry can only attach 1 photo!

[Fred_Bueltmann]

Well, I see that I can use Lookups to create a table for each of the 4 beers per brewery, An Fx field for the primary in this table, drawing from lookups in secondary columns. This is helpful in getting secondary columns converted to primary, but I wonder then if I can combine those four tables somehow. This isn't the worst solution (having four tables, linked) - but I'm open to suggestions.

[Fred_Bueltmann]

Whoops - attachment failed. Here's a screen shot.

[Fred_Bueltmann · Accepted solution]

Automation is the key and had been the stone unturned. I haven't worked with it before. Set up for when "Beer 1 name isn't empty" -> create record in new table. Set up an automation for each of the four priorities per brewery, and after a little learning curve - et voila.