Relationship between 2 tables - how to?
Hi
I'm a musical director with a choir. I need to track which songs are going to be sung at each rehearsal.
So I have created a table with all the songs: Satser and a table with rehearsal dates for my choir: Herlev Seniorkor. (Danish!)
So this ex.: the 4th of September I want to rehearse 4 songs. In Herlev Seniorkor table I have a record for every rehearsal day - a calendar field - and I select the day: 4th of September and then I have created a "Relationship from" and selected Satser. So now the 2 tables have a 1:N. relationship. Now I can assign the songs we have been rehearsing that day. But the week after - the 11th of September - I can't assign those songs again - and we need to practise them again for sure ;-) -, because of the type of relationship I assume.
I want to be able to assign songs from Satser to more than one rehearsal day.
How can I accomplish that?
My finishing goal is to be able to create a plan for every rehearsal day/concert with all the titles and duration and eventually being able to decide which songs to rehearse AND send the scores with e-mail!
Any help much appreciated!
14 replies
-
HI can you please add your db, its easier for me to help
-
You need a many to many (N:N) table between Saster and Herlev Seniorkor. Maybe it can be called Songs Rehearsed or Rehearsal Songs?
Anyways, create a new table then add the relationship field to Saster and Herley Seniorkor. Now a song can be related to many rehearsal days. Now you can also track specific information for that song that was rehearsed on a specific day.
-
Hi
I understand that I need a many to many relation, but I'm uncertain how to create it in Ninox.
Isn't there a way to create the Herlev Seniorkor table in a way that I can have - let's say 20 - song placeholders for each date in the table, and then I could just populate those with the song titles I need?
In that way I could also have my concert program incorporated in the table.
Really appreciate your time!
-
sc said:
I understand that I need a many to many relation, but I'm uncertain how to create it in Ninox.did you seeKruna fine example of a N:N table? The table is called chose songs and it is currently a child of Herlev Seniorkor.
Look back at your previous post about importing. provided you with another example of a N:N table.
sc said:
Isn't there a way to create the Herlev Seniorkor table in a way that I can have - let's say 20 - song placeholders for each date in the table, and then I could just populate those with the song titles I need?A N:N table is the way to allow the Herlev Seniorkor table to have an "unlimited" number of songs linked to a record.
You might be thinking of creating 20 fields, with names like song1, song2, song3, etc. Then if you want to track information about the song that was played during that rehearsal you will have to create 20 more fields for each bit of data you want to track.
You could but you will find out in the future that you will have a very difficult time getting information about a song was that played.
-
To echo above - I would definitely stay away from creating multiple fields of the same data on the same table - you will heading for trouble - Ninox is a relational database and makes it easy(ish) to compile related information. Whereas, if you do it all within one table-form you will end up with a flat file. If that is your preferred method then you don't need Ninox, you could do this in say Google numbers /Excel spreadsheet!.
All the above comments are sound advice. By relating it all now, the benefits will be huge. Once in place (structure wise) then we can help you put a button on to walk through a session and mail it out to the choir contact list etc. Also being related you will be able to look back to do things like as see what tune was sung/rehearsed at what event. Tune frequency etc, Copy elements to new session etc
One of the joys of Ninox is that you relate the table as a whole and not have the build links of key fields like other software.
Have a look as this basic (in house short Ninox videos) from Maria Luisa Pasini
one to many. https://ninox.com/en-tutorials/database-architecture-1-n
many to many. https://ninox.com/en-tutorials/database-architecture-m-n
-
Okay, I understand. From the video I get, that she uses basically 2 tables: Invoices & Products. In my case will it be meaningful to use Herlev Seniorkor (all the dates for rehearsals and concerts) as my "Invoices" and Satser (all the scores) for Products?
Thanks a lot for your time and interest! -
If it were me i would go for 3 tables as a min
main form (header dates etc. then a table for songs and table for singers
and have songs and singers a sub table on main form
I assume that you keep the arrangements PDF's off line
I kind of visualise it something along the line of this :-
-
That would be possible too. But I don't need the singers, but I have several choirs, so I will build a Herlev Seniorkor 2 as well. The layout you have made, is actually what I'm after. So for each rehearsal/concert date I can print and mail the program to the choir - potentially with the vocal scores appended!!! Finally I need to be able to calculate the total playing time, but that could be done with a formula I suppose?
Thanks again for your help!! -
Okay Makes snese
and yes assuming you have out recorded the paying time for each peice in th record then a formula will indeed will sum up the total time. or if they on a sub talable y0u can sum up the records for that field in the view.
-
Hi again!
I have now created a data-base with a choir, with the dates for rehearsals and concerts. I have established a link between the dates and the table with all the scores. I can select several scores for a rehearsal OR concert, and see the total duration! All good. But I also need to give each score for a particular date a number so I can sort the scores in a running order. How can I do that?
Thanks again for your help!!
-
Content aside
- 1 yr agoLast active
- 14Replies
- 209Views
-
4
Following