Help

Create a table based on other tables that automatically populates

13852 15
cancel
Showing results for 
Search instead for 
Did you mean: 
Simon_Holmes
5 - Automation Enthusiast
5 - Automation Enthusiast

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

15 Replies 15
Tuur
10 - Mercury
10 - Mercury

Hi Simon,

Unfortunately there’s no option for that right now, unless you’re willing to write something using the API.

If you’re really serious about this please drop me a line. :slightly_smiling_face:

Best, Arthur.

grayfo
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Arthur

My Base also has a many-to-many relationship supported by a Junction Table, but I think my requirement is slightly different.

Starting from Table A, I need to view a list of Table B records and quickly tick/select each one that relates, in order to automatically create a Junction record in Table C to support that relationship.

Can you help me with this, please - and could you point me towards an example Base that supports this functionality?

Many thanks

Graham

grayfo
5 - Automation Enthusiast
5 - Automation Enthusiast

UPDATE: I had a good reply from the online chat facility, as follows…

_" I recommend checking out our guide to creating many-to-many relationships in Airtable: https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relation... _

_Unfortunately we don’t currently support the exact behavior you have described. In a linked field you can manually select a record you’d like to link, but after you do so the dialog closes and you must reopen it to select another record. We are considering changing this behavior, though! In the meantime our fill handle may be of use if you are linking multiple records to one record at one time. _

Our Product Catalog and Orders template is a great example of many-to-many relationships"

Great support service!

I agree on the service! :slightly_smiling_face:

Sorry I didn’t respond sooner, but don’t hesitate to ask me something else…

Greetings from Holland,

Arthur.

Tanuja_Prasad
4 - Data Explorer
4 - Data Explorer

I have a need similar to @Simon_Holmes. Here is my scenario.

My team of 5 people is working on a project to analyze 10 software packages, against a list of features that we need.

Table1: list of software packages
Table 2: list of features. There is an additional problem here: there are about 12 meta-features and each has about 5-to-10 features. How to represent that ?
Table 3: list of team members

When a team member is working on his/her analysis, a useful view would be:
– one software package
— list of features in the left column (the detailed list of features)
— two additional columns “analysis comments” and “analysis rating”

But, a software package can be analyzed by multiple team members.

Sounds like a junction table is needed. But I’m not sure how.

Your help is appreciated.

Jessica_R
4 - Data Explorer
4 - Data Explorer

Just wondering there’s any further update to this?

I’m effectively wanting to emulate a LEFT/RIGHT JOIN so that I can have a query which lists all permutations for two different tables. Can this be done?

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

Hi Ahmed! I only have a custom (paid for / Zapier like) solution…

Thank you, i’ll use MS Access for this smallish project, but I fully support what you are doing. We need an access alternative on the web that’s easy to use and modern.

best,

ahmed

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

I’ve sent you a message. :slightly_smiling_face:

Reflash
4 - Data Explorer
4 - Data Explorer

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:

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

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

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

It’s a ‘manual’ thing.

Ismail_Simsek
4 - Data Explorer
4 - Data Explorer

Looking for same solution. automatically updated cross join table