Skip to main content

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

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



Best, Arthur.

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



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

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!

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



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



Greetings from Holland,



Arthur.

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.

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



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.

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…

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

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


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

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


I’ve sent you a message. 🙂

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:




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



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

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.

Looking for same solution. automatically updated cross join table



Reply


Terms & Conditions