0

Auto-populate Music Library Fields

I am working on databasing all of my sheet music and would like to auto-populate a field on one table with information from two other tables if possible.

Table 1 = "Organ Library" - this is the main table that contains all of my sheet music records.

Tables 2 & 3 contain records of hymns from two different hymnals. 

The common identifier between all three tables would be "Tune Name". However, one tune name can return multiple Hymn Titles from tables 2 and 3. I would like to have a field on Table 1 that auto-populates all of the Hymn Titles found in tables 2 and 3 based on Tune Name. 

Any help is much appreciated! 

14 replies

null
    • Fred
    • 9 mths ago
    • Reported - view

    You may want to consider condensing your two Hymnal tables into one. Since they store the same data except for the denomination. So I would recommend merging the two tables then creating a choice field that lists the denominations so you can search on that.

    Once you do that you can delete the reference fields in Organ Library to the two tables.

    In Ninox, you create the 1:N (one to many) link from the many side. So in the new Hymnal table you will create a reference field to Organ Library. That way when you look in Organ Library you will see a table of all linked records in Hymnal.

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    I have put you the two tables together to show you as Fred's suggestion

    Have a look. If that works for you then we can tackle or talk you through the second part of your question ie the link and auto populate (this is not that hard you could a table ref lookup or indeed a filtered view)

      • Fred
      • 9 mths ago
      • Reported - view

      Maybe my understanding of the data is wrong but shouldn't the link go the other way between Organ Library and Service Book?

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    Fred - I haven't touched the links

    I have only merged the the two tables as you suggested and deleted the other table which in turn has  deleted the table ref in Organ Library.

    Reading throughan_wessel thread above Organ Library is the main table. The other 2 (now 1) tables  were not child tables but merely contain other music.

    an_wessel 's aim is to use the main table but when on a particular tune the tune name would provide a data lookup to similar titles music scores in table 2 that have the same or similar name in ie "All Saints" so providing a view to those records. (not actually draw data from any particular child into the main form). Thus as I see it there is no real relationship but an ad hoc one at the time of finding a "like' Tune

    However The reference as is does in fact show all records on table b ( so i left this alone) but as it stands I would argue you don't need this ref link at all.

    At first I though well one could use a view of the 2nd table within Organ Library

    and indeed setting the view filter  works as per the reference file )shows all records)

    if 'Tune Name' > " " then select 'Service Book' 

    as some tune have no tune name

    However something like 

    if 'Tune Name' > " " then
        let mySearch := 'Tune Name';
        select 'Service Book' where 'Hymn Title' like mySearch
    end

    does not work

    I think that this is because Tune name in Organ Library is a string of words and equally Hymm Titles in Service Book (table b) also has a string of names in that field. Thus Ninox does not or cannct find a like match because at the moment it does not know what to latch onto .

    I think we need some way of sifting a key word out off the tune name - trouble is that key word could be in any position within the field string.

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    Or maybe have a key field in both tables that only has certain key names ie Saints, Eventide etc.

    following 's logic

    If were going to sit at the organ and play Eventide (Yes I do actually have one -  a 3 manual Viscount Cantorum plus various synths and a piano - so you can see why this thread interests me)

    In Organ Library there are 3 Eventides (set in different key signatures)

    If i select any one of these I will also want to see a reference (lookup reference only) to the other 2 Eventides as shown in table b (Service book). None aare actually related other than have the same name in this case

    "All Saints" would throw up no less that 5 similar named tunes in service book - but this only if the search was based on "Saints" and not "All"

    By they way - some great title sin the tables

    I'm a Handel Organ Concerto man myself - ie https://www.youtube.com/watch?v=LIcQJ_nyqeY

    which is a delightful 4 movement - Andante

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    So Fred somehow we need to take a string of words and use some of that to look at another string of words and pick out certain keywords - but who is to say what keyword is important.

    maybe we need an instring value to grab but as to how one decides which keyword to grab in a long title tune i am not sure about.

    I can see another way but that would rely on adding further ref field in both tables that holds selected keys words - ie Saints in all Saints, Gone with the wind Gone/wind etc but even this could fall over...

    if adding direct links then a lot of tunes would have a lot of references to other tunes in table b - thus I think this could be messy and building up lots of references for the sake of it.

    • Fred
    • 9 mths ago
    • Reported - view
     said:
    I think that this is because Tune name in Organ Library is a string of words and equally Hymm Titles in Service Book (table b) also has a string of names in that field. Thus Ninox does not or cannct find a like match because at the moment it does not know what to latch onto .
    I think we need some way of sifting a key word out off the tune name - trouble is that key word could be in any position within the field string.

     Looking at the DB, I came up with this code for a view element:

    let t := this;
    select 'Service Book' where lower(Tune) = t.lower('Tune Name')
    

    It looks like Tune Name and Tune have the same names, so I'm going with this and assume that this will always be the case. I use the lower() command to make sure we don't have to worry about case throwing off the equal.

    Let me know if you think this will work for a performer.

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    @fred That certainly brings up other music references, but not sure it will search for all.

     specifically asked for a partial match to "Hymm Title" field not to "Tune" in the service book. But i guess this might achieve to some degree what he is asking for.

    I added in your script in this version V3

    if "Tune" in Organ Library is blank no attempt to find a match will take place.

    As prev stated I don't think you probably need the relationship hard wired here. As it works perfectly without it ( i have left it in in V3 but removed locally to test) as you are not physically trying to attach the tunes in Service Book to the main table as child records.

    Also I name your previous table to service Book as 2 tables were becoming one. But change the name to suit you as you see fit.

    Leave it will you to see if it will do what you need.

    P.s I might continue to play with this and see what can be done etc.

    • Fred
    • 9 mths ago
    • Reported - view
     said:
    I would like to have a field on Table 1 that auto-populates all of the Hymn Titles found in tables 2 and 3 based on Tune Name.

     So this is what he asked for. Let me see if I can understand it.

    There is a field in Organ Library called 'Tune Name'. In Service Book there is a field called 'Tune' and 'Hymn Title'.

    If there is no relationship, then what makes sense to me is to search on exact match of 'Tune Name' and 'Tune'. Since a cursory look at the data makes me think that this is how they are to be matched. So we find all records in Service Book that match Tune to Tune Name and then we show the Hymn Title field.

    I've created a formula that tries to recreate the Hymn Titles field in Organ Library.

    I've also added a view element that does the same thing but allows for more information to be displayed.

    I've noticed there are many records in Organ Library with the same Tune Name, for example Azmon. So I'm not sure how differentiate them to records in Service Book.

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    Fred  - thats the joy of music

    Yes you could conceivably have 2 tunes of the same name in Organ Library.

    they could be an easy to play or a mores skilled version

    or in different key signatures - or in different musical arrangements. 😊

    • an_wessel
    • 9 mths ago
    • Reported - view

    Gentlemen, I appreciate your attention to this! My apologies on my lack of response along the way. I hope to dig back in this evening or early this week. For now, just dropping in to say thank you for your work on this to help me find a solution! 

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    V5 is the same as V4 but with the table relationship removed. As there is no real link between the tables and it seems unlikely the records in Service Book will be added as child records, then realistically you don't need it.

      • an_wessel
      • 9 mths ago
      • Reported - view

       v5 is spot on! Thank you, thank you! 

      • Mel_Charles
      • 9 mths ago
      • Reported - view

       If you are happy - please can you mark the post as answered

Content aside

  • Status Answered
  • 9 mths agoLast active
  • 14Replies
  • 124Views
  • 3 Following