Creating many to many relationship between two tables

Hello. Have a look here: there is a model created by Ninox support to show how you can create a N:M-Relationship:

https://www.dropbox.com/s/5j1m157jior0qtf/19_Many%20to%20Many%20Table%20Relation.ninox?dl=0

or here in the manual:

https://ninoxdb.de/en/manual/tables/table-references-and-relations

Fabio

You're welcome!

The 19_Many MN Relationship table shows data in the fields. How do you get it to automatically populate the data? When I do this, my fields are empty and I have to manually populate the fields. But I want it to automatically and dynamically update / add data to show in the fields as I change the referenced data. What step am I missing?

You may also find my blog posts about relationships useful (Relating to Relational Databases parts 1 and 2):

software.kirknessassociates.com/blog.html

To answer the question about automatically populating data when you select a linked record (I assume you mean something like picking up the price of a product from a related table?) then there are two ways:

1. you can use a formula field (formula something like Product.Price)

2. you have a normal number field and an after update trigger on the reference field ('Inserted Price' := Product.Price)

Which to choose?

The first option will alwaus display the current price of the product (in my example) which may or may not be what you want - for example with an invoice line you would probably want the price at the time (in which case use option 2).

The second option has the advantage that it captures the value at a point in time - and also the value can be edited if you want. In the incoice line example you could choose to override the price for a particular sale for example.

Hope this helps!

Julian, thanks so much for your answer and blog post! I’m new to Ninox so it will take me a little while to process the info to see if it works for my situation, but I’m not entirely sure if I explained my situation clearly:

I have table 1 (books) and table 2 (genres). Each book can have multiple genres and each genre can have multiple books associated with it.

I create table 3 (for N:M relationship) and in table 3, I link to table 1 and 2. After that, when I look at table 3, there are two BLANK fields and nothing else—one for Books and one for Genres.

If I click on the Book field, I can choose from the book titles from table 1. If I click on the Genre field, I can choose from the list of genres from table 2.

I know I can manually select/link each book title from table 1 to table 3. But I have a long list of book titles and it will grow over time. Same with genres. How can I get table 3 to automatically populate with all the book titles and all the genres?

The 19_Many to Many sample table linked to by Fabio Ferrara shows the MN table as being fully populated, but I can’t find out how it got there.

Julian, I’m hoping that your solution will solve my issue, but I suspect that maybe you’re talking about a situation where I already have the book titles and want to pull in additional data like price?

Thanks again for your help on this!!

Ah, I see that I’m asking the wrong question. I’ve figured out that I have to go to Books and add each book & genre from the Books table, not the NM table.

Julian, you say in your blog post to hide junction tables to see the data of the other tables directly. How do you hide the junction table? Viewing through the junction table is seriously awkward (at least the way I’m doing it :).

Thank you SO much for your help!!

I would usually hide junction tables (using the Hiden tickbox in the table definition).

Thanks so much! It works now! Much appreciated.

I would like to look at the https://www.dropbox.com/s/5j1m157jior0qtf/19_Many%20to%20Many%20Table%20Relation.ninox?dl=0 example, but dropbox says the file is no longer available to download?

Thank you

Try now.

Thank you very much, Fabio.

I have to say that the example could be a lot better. While it describes the basic database structure, it doesn't address how you would set up the fields or views in order to make working with this type of structure easy. For instance, if you are in the students table/view, and you want to add a course, you should be presented with a list of courses that you can pick from. When you choose the course you get a new entry in the N:M table. And same thing the other direction, when you are in the courses table you should be able to add a student by picking from a list of students. So how do you do that?

In addition, you shouldn't be able to duplicate entries (a course shoudn't have the same student in it multiple times). I'm new to Ninox but in other databases you would ensure this by setting a unique primary key comprised of a combination of the keys from both students and courses. How would you do this in Ninox?

And, in the N:M table, the relationship fields aren't marked as required, shouldn't they be? You wouldn't want an entry that doesn't have both because it would be invalid. Was this just an oversight because the example was put together very quickly, or is there a reason these weren't marked as required fields?

And lastly, how would you represent composition in such a table? If a student is deleted, you would want all the entries in the MN table related to that student deleted, and likewise same in the other direction.

Could be there's a better example or one of the templates provides more detail, but I'm trying to figure out how to implement this (I have lots of many-many relationships).

Has you finded any solution Mike N.? Its exactly what im looking for :S