One-to-many queries

jamesingram · Post by **jamesingram** » 27 Dec 2010 17:56

I've used FH very happily for a couple of years now but only just started writing queries. I find I naturally want a query language to let me ask a question such as 'Go get all notes linked to individuals and, once you've done that, filter the results so that you return to me only those notes which have a source whose short title is 'IGI''

So far with FH queries I've not been able to refer e.g. to the source of an individual's notes in what I think is a generic way. Here's part of one query:

'Include if =Exists[%FACT.SOUR[1]>%) contains 'IGI',
Include if =Exists[%FACT.SOUR[2]>%) contains 'IGI',
Include if =Exists[%FACT.SOUR[3]>%) contains 'IGI' '

I'm repeating FACT.SOUR for every source that I think may exists for a note, but what I want to say is 'just go and look at all the sources for the notes (and YOU work out how many sources there may be), and tell me how many contain the string 'IGI'.

So how would I write a query that would capture any existing FACT.SOUR value without me having to specify which precise FACT.SOUR it is?

Thanks

James Ingram

ID:4911

Bilko · Post by **Bilko** » 27 Dec 2010 20:49

Hi James,

I tried something similar to this some time ago, but work commitments slowed me down. I've just tried a solution and it's just found some errors for me (Birth entries recorded against Death Registers!).

The line I used is :- add if %FACT.SOUR[1+]>% contains [''].

When this query is run, you are asked what you want to be found (ie enter IGI) and are then presented with the result in the columns previously entered.

The [1+] tells the query to check all entries for each type (ie it will check each marriage).

Hope this helps, but I'd not be surprised if there is a better way.

[wink]

Bilko

jamesingram · Post by **jamesingram** » 27 Dec 2010 22:45

Bilko

Thanks very much for the quick reply - that [1+] thing looks just like what I need, I'll experiment with it.

Regards

James Ingram

PeterR · Post by **PeterR** » 28 Dec 2010 15:02

I find that, although the 1st line of code below is accepted as valid syntax, its effect is the same as the 2nd line of code, and so of no real help:

Code: Select all

add if %FACT.SOUR[1+]>% contains ['']
add if %FACT.SOUR[1]>% contains ['']

jamesingram · Post by **jamesingram** » 28 Dec 2010 17:41

Peter

I've found the same thing, I think FH ignores the [1+] and defaults to the first item it finds. I've dug around in the online help (Help-->Family Historian Help) and have now discovered this '[1+]' type of syntax (look for 'looping' in the Search).

It does the job I want but only for text templates, not for queries. Unless there's another way to do this for queries, or unless I've got things muddled, maybe there's a wishlist entry here...? [smile]

James

Bilko · Post by **Bilko** » 30 Dec 2010 22:54

Hi Peter & James,

I've just double checked everything I wrote before and it does correctly identify every instance of a source used. For example - my aunt has been married 4 times and the query correctly shows 4 marriage entries against the source 'A-Mar 1916-2005' (Ancestry Marriage index 1916-2005) - when I search for 'A-Mar'.

It also works when I ask for the string 'IGI' when it returns all birth, Christening, Baptism, Marriage and burial entries that I have found and recorded against 'IGI' as a source.

The inclusion of the '+' after the [1] requires the query to check the first source of type X for each person/family, followed by the 2nd, then 3rd until there are no further entries of that source type for that person/family.

So using marriage & IGI for an example, the following results will be obtained from 'add if %FACT.SOUR[1+]>% contains ['']' {assuming positive results for a person who married 4 times}

a. Marr[1] source = IGI,
b. Marr[2] source = IGI,
c. Marr[3] source = IGI,
d. Marr[4] source = IGI.

Whereas the result obtained from 'add if %FACT.SOUR[1]>% contains ['']' {assuming positive results} will be {for the same person}

a. Marr[1] source = IGI.

There is no requirement to check any further because the query has been instructed to ONLY check the first instance!

I must point out that the example given above is improbable because, in the days of the IGI a second marriage would (almost certainly) not be permitted so a forth would impossible. This would explain why you seem to 'think' that the [1+] part of the instruction does not work when, in reality, it does.

Strangely enough, I have found repeated entries using this query for Birth registrations but none for deaths !

Happy New Year to all

Bilko

jamesingram · Post by **jamesingram** » 31 Dec 2010 10:25

Bilko

I've tried out what you've said and I now see it working, so thanks for the persistence.

I've made the [1+] syntax work with a simple query of type FACT, but also I'd been attempting to extend the logic within queries of type Individual e.g.

%INDI.NAME[1].SOUR[1+]>% contains ['']

and it's a query such as this that brings identical results to

%INDI.NAME[1].SOUR[1]>% contains ['']

So this is why I didn't believe you at first![oops]

It'd be nice if queries could let you do this. The syntax may get a little difficult but the results would reflect simple, everyday questions.

James

PeterR · Post by **PeterR** » 31 Dec 2010 12:24

Sorry to have to disagree with both Bilko and James, but I still believe that use of a looping index e.g. [1+] in contexts other than a diagram text scheme has no effect. The relevant FH Help page includes 'Looping indices only work within text templates in text schemes.'.

In a Fact Query with filter add if %FACT.SOUR>% contains [''] and with entered value 'marriage' I get 367 Records in Result Set.

In a Fact Query with filter add if %FACT.SOUR[1+]>% contains [''] and with entered value 'marriage' I get the same 367 Records in Result Set.

But in a Fact Query with the following filters I get 404 Records in Result Set because 37 of my Marriage Facts have a 1st Source which does not contain 'marriage' and a 2nd Source which does contain 'marriage'.

Code: Select all

add if %FACT.SOUR>% contains ['']
add if %FACT.SOUR[2]>% contains ['']

The people with multiple marriages were all included in the 367 results from the first Query because for all their Marriage Facts the 1st Source contains 'marriage'. The use of the [1+] looping index in the second Query made absolutely no difference. The additional 37 records found in the third query were other people (some with multiple marriages) where only the 2nd Source for their Marriage Fact (i.e. %FAM.MARR.SOUR[2]>%) contains 'marriage'.

Bilko · Post by **Bilko** » 01 Jan 2011 16:15

A WORKING version of the query I discussed above has been added to the Downloads section of FHUG !

Happy New Year to all

Bilko [grin]

PeterR · Post by **PeterR** » 01 Jan 2011 19:09

Downloaded Fact_Sour_(1 ).fhq is not a valid FH Query File. Perhaps something went wrong with Bilko's upload?

PeterR · Post by **PeterR** » 01 Jan 2011 23:09

Downloaded Fact_Sources_(1 )_v2.fhq is also invalid. I wonder if the plus-sign in the original name is a problem, since it appears to have been replaced by a space at some stage.

YouTube · Post by **Jane** » 02 Jan 2011 09:53

I have fixed the Query now, it was a problem with the + in the file name as you guessed Peter.

PeterR · Post by **PeterR** » 02 Jan 2011 15:29

Thanks, Jane, for fixing the Query!

However, having now downloaded, imported, and used it, I can definitely confirm that it does not work as claimed.

If I enter 'marriage' when prompted for Source, the result set contains the same 367 records as the first Query mentioned in my post on 31/12/10, above:

In a Fact Query with filter add if %FACT.SOUR>% contains [''] and with entered value 'marriage' I get 367 Records in Result Set.

Bilko's Query fails to find any of my 37 records where the 1st Source does not contain 'marriage' but the 2nd Source does. For example the Individual whose data are shown below clearly has 2 marriages each with a Source containing 'marriage', but neither marriage is found by Bilko's Query.

As I have said before, in the context of a Query %FACT.SOUR[1+]>% has exactly the same effect as %FACT.SOUR>% and finds only records where the 1st Source contains 'marriage'. The only way to get the above 2 marriages to appear in a Fact Query that prompts for Source is to have an additional filter add if %FACT.SOUR[2]>% contains [''].

By the way if anyone else needs to delete the invalid Query (not possible within FH) delete the file Fact_Sour_(1 ).fhq (and possibly Fact_Sources_(1 )_v2.fhq - note the space after the one) from the folder C:Documents and SettingsAll UsersApplication DataCalico PieFamily HistorianQueriesCustom (if using XP, or from the equivalent location if using Vista or W7).

Family Historian User Group