Create a table based on other tables that automatically populates

[Simon_Holmes]

Hi, I have two tables in my database:

• Food Items (contains a number of food items with various attributes such as price, food type, etc.)
• Shops (contains a list of shops with attributes such as address, post code)

These would have a many to many relationship, in that each food item could be in many shops and each shops could contain many food items.

I would like to make a third table which automatically contains all possible combinations of the two tables, and which automatically updates whenever a new record is added to either of the first two tables. Then I intend to add some other attributes and set up a filter so that I could query it.

I can not work out how to do this. I have found a way to create a junction table by following this tutorial ( https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relation... ) but it seems to require me selecting each line manually. I must be doing something wrong but can not work out what it is.

Edit to further clarify: I think in SQL it would be a query along the lines of SELECT * FROM food_items CROSS JOIN shops.

Many thanks

Simon

[msiddiq]

Could you please let me know the details about that solution. Thanks

[Tuur]

I’ve sent you a message. :slightly_smiling_face:

[Reflash]

I am still wondering if it’s possible, it will require too much of manual work in my case

I’ve solved it using a python script and then manually adding two columns, but this doesn’t seem like a nice approach:

gist.github.com

https://gist.github.com/reflash/085b572b39131648c1a4db2020ad0686

airtable-joint-table.py

clm_1 = """...""" # column of your first table
clm_2 = """...""" # column of your second table

lines = zip(clm_1.split('\n'), clm_2.split('\n'))

new_lines = list()
for line in lines:
    for item in line[1].split(','):
        new_lines.add((line[0], item.strip()))
        

This file has been truncated. show original

[Trevor_Goss]

Hey Arthur, this was an old message you had on this thread, but I’ve got a similar use case for a project. Are there are any updates to to this question? I’d love to learn what your recommendation was on using the API to accomplish this?

Thanks in advance!

Trevor

[Tuur]

The API solution involved custom software. A script or Zapier task is also the same concept…

It’s a ‘manual’ thing.

[Ismail_Simsek]

Looking for same solution. automatically updated cross join table

Ahmed_Lacevic:

Hi Tuur, any updates on this. I also need a many-to-many join that automatically populates like an SQL query table would.