0

How to turn 5x reference columns in a CSV into a single reference column containing all 5?

I'm trying to import from .CSVs where the fields I intend to link look like this, with added notes to show where I want the links to go.

 

Genres

GenreID (PK)

GenreName

 

Movies

MovieID (PK)

MovieName

Genre1 (---> Genres.GenreID)

Genre2 (---> Genres.GenreID)

Genre3 (---> Genres.GenreID)

Genre4 (---> Genres.GenreID)

Genre5 (---> Genres.GenreID)

 

Actors

ActorID (PK)

ActorName

MovieID (---> Movies.MovieID)

Genre1 (---> Genres.GenreID)

Genre2 (---> Genres.GenreID)

Genre3 (---> Genres.GenreID)

Genre4 (---> Genres.GenreID)

Genre5 (---> Genres.GenreID)

 

I already know how to import records with links like this:

 

Screenshot 2022-02-16 at 23.15.30

 

What I really want is to import from CSV and have it look like this:

Screenshot 2022-02-16 at 23.15.48

I can't even get that kind of table to show up on the Import CSV bit where you allocate CSV columns to Ninox colums so I'm guessing that isn't a route. I was wondering if I actually upload the CSV into the other side of the reference with the GenreIDs stored like an array with semi-colons but I can't get that to do anything either.

 

The next route I see to take is to make a button or something that will take whatever's in the 5 individual 1:N Genre reference columns and combine them into the single N:1 reference but I've always found Ninox's scripting to be pretty opaque as a non-programmer.

 

Any ideas?

1 reply

null
    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    Each genre can caracterize many movies, and each movie can be caracterized by several genres - up to 5 in your implementation. Which means that there must be a M:N relation between the "Genres" table and the "Movies" table. Being faithful to the theory of relational databases, Ninox does not affer a "direct" way to build such relations. The two tables at hand are describing objects in the real world, so they are "Entities" tables.
    To buils a M:N relation, you need to create another table, for the sole purpose of representing the relation. It will be a "Relation" table. Such a table does not need to contain any data fields, it just needs to contain two N:1 reference fields to the two "Entities" tables. There will be one record for each elementary relation between the tables.
    While it does not need to include data fields, a "Relation" table is often the right place to put down some information. Suppose, for example, that there is a "degree" in the appartenance of each movie to each genre, like 100% adventure, but with 30% romance and 5% musical. Creating a "Degree" field in the "Relation" table is exactly the right place to document the fact.
    It would be rather difficult to fill this structure by direct importation from CSV files. So, importing the contents of the "Entities' tables, and adding a button to any table to trigger a script building the records in the "Relations" table is probably the way the go.
    So lets create a table named "Movie-to-Genre", and, in this table, create two table references to "Movies" and to "Genres" - in Ninox, relations are always created from the N side. As a result, there will be two N:1 reference fields in the "Movie-to-Genre" table, called, by Ninox's default, "Movies" and "Genres". If you look in the "Movies" and "Genres" tables, you will find the other side of the relations, shown as a 1:N reference field named, by default, "Movie-to-Genre".

    for m in select Movies do
    let g := m.Genre1;
    let gi := first((select Genres)[GenreID = g]);
    if gi then
    let r := (create 'Movie-to-Genre');
    r.(Movies := m);
    r.(Genres : = gi)
    end;
    let g := m.Genre2;
    etc etc
    end

    The "Genrex" fields have now become redundant, and should be deleted, since redundant data in a relational database are evil.