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

[Tuur]

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]

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]

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!

[Tuur]

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]

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]

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?

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

[Tuur]

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

[Ahmed_Lacevic]

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