Skip to main content

Create a table based on other tables that automatically populates

  • October 23, 2016
  • 15 replies
  • 120 views
S

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-relationships#ex3 ) 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

    T
    Forum|alt.badge.img+19
    • Tuur
    • Inspiring
    • 366 replies
    • October 24, 2016

    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.

    G
    • grayfo
    • Participating Frequently
    • 7 replies
    • February 21, 2017

    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.


    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

    G
    • grayfo
    • Participating Frequently
    • 7 replies
    • February 22, 2017

    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


    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-relationships _

    _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!

    T
    Forum|alt.badge.img+19
    • Tuur
    • Inspiring
    • 366 replies
    • February 22, 2017

    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-relationships _

    _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.

    T
    • Tanuja_Prasad
    • New Participant
    • 1 reply
    • September 27, 2017

    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.

    J
    • Jessica_R
    • New Participant
    • 1 reply
    • March 27, 2018

    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?

    A
    • Ahmed_Lacevic
    • New Participant
    • 2 replies
    • April 1, 2018

    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.


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

    T
    Forum|alt.badge.img+19
    • Tuur
    • Inspiring
    • 366 replies
    • April 1, 2018

    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…

    A
    • Ahmed_Lacevic
    • New Participant
    • 2 replies
    • April 1, 2018

    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

    M
    • msiddiq
    • New Participant
    • 1 reply
    • April 14, 2018

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


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

    T
    Forum|alt.badge.img+19
    • Tuur
    • Inspiring
    • 366 replies
    • April 16, 2018

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


    I’ve sent you a message. :slightly_smiling_face:

    R
    • Reflash
    • New Participant
    • 1 reply
    • September 28, 2018

    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:

    T
    • Trevor_Goss
    • New Participant
    • 2 replies
    • December 14, 2019

    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.


    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

    T
    Forum|alt.badge.img+19
    • Tuur
    • Inspiring
    • 366 replies
    • December 15, 2019

    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.

    I
    • Ismail_Simsek
    • New Participant
    • 1 reply
    • March 31, 2022

    Looking for same solution. automatically updated cross join table


    Terms & ConditionsAccessibility statement