Auto-populate Music Library Fields

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.

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.

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

followingan_wessel '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

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.

Mel Charles 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.

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

an_wessel 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

an_wesselif "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.

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

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.