* Custom queries - Tuition needed

Questions regarding use of any Version of Family Historian. Please ensure you have set your Version of Family Historian in your Profile. If your question fits in one of these subject-specific sub-forums, please ask it there.
avatar
E Wilcock
Megastar
Posts: 1181
Joined: 11 Oct 2014 07:59
Family Historian: V7
Location: London
Contact:

Custom queries - Tuition needed

Post by E Wilcock »

I am finding it hard to use queries and filters in fh - after TMG. There is no hurry about this, but I do need some further instruction please.

I have already been taught and have learned to run a query finding the individuals for whom a single fact or attribute has a certain value.

So I can (for example) find people with British nationality.

Now if I want to select those whose spouse had some other nationality, I can do that (I think) by writing a second line using the special genderless short cut .
%INDI.~SPOU[1]>XXXX%

But the double negatives are confusing me - I have to exclude those whose partners are British.

But would it be possible instead to keep only the people with British nationality married to people with Australian nationality

and can one select in fh with an OR condition? Using Or would allow one to retain the Britons with either Australian or American partners.

And where a person has two spouses, how would this be handled?

Where please is the list of abbreviations for all the Facts and attributes. Fine if one has a query assistant field for a simple expression but not so fine if one is having to type out the queries oneself again and again, peering at the Getting the Most Book. And wondering why the results screen remains empty.

Please can someone explain or point me to an explanation of the symbols and punctuations used to write these things in fh.
I understand that the tilda is special, ~ is an expression (ie a spouse of either sex)
I think the % signs isolate the Event field you want- so there are always two of them enclosing the field where the data is to be found.
But what is the > hanging around with no arrow pointing in the opposite direction?
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Custom queries - Tuition needed

Post by tatewise »

Your last questions​ about the symbols is covered in how_to:understanding_data_references|> Understanding Data References.

Often a series of Exclude unless row filters work.
i.e.
Exclude unless ... British
Exclude unless ... Australian

You can build 'standard' AND OR Expressions using the =IsTrue(...) Function.
i.e.
=IsTrue( ... and ( ... or ... ) )

The list of tags for all facts (Events & Attributes) is in every Data Reference Assistant wherever data refs are allowed.
Remember there can be no comprehensive list except within FH because custom Facts are unique to you.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
E Wilcock
Megastar
Posts: 1181
Joined: 11 Oct 2014 07:59
Family Historian: V7
Location: London
Contact:

Re: Custom queries - Tuition needed

Post by E Wilcock »

Thanks Mike. I will absorb that info. The exclude unless is very useful.
tatewise wrote:
The list of tags for all facts (Events & Attributes) is in every Data Reference Assistant wherever data refs are allowed.
Remember there can be no comprehensive list except within FH because custom Facts are unique to you.
My problem here was that I clicked the list and could enter an Event or Attribute - but failed when it came to strings. I thought I had to type out the spouse with the tilda.
I have just tried again, this using the data reference assistant for both spouse and the attribute, and after several tries, I am still getting an empty results set.

It is clear that I am doing something wrong.

My line to get the spouses nationality is "%INDI.FAMS[1]>~SPOU[1]>NATI[1]%" (without the inverted commas).

Does that look right to you?

Now it may be not working because I dont actually have marriage data for these couples so possibly fh doesnt see them as man and wife?
User avatar
PeterR
Megastar
Posts: 1135
Joined: 10 Jul 2006 16:55
Family Historian: V7
Location: Northumberland, UK

Re: Custom queries - Tuition needed

Post by PeterR »

I used the Data Reference Assistant and obtained the following

Code: Select all

%INDI.~SPOU[1]>NATI[1]%
which worked perfectly.
The absence of MARRiage information per se would not matter.
Peter Richmond (researching Richmond, Bulman, Martin, Driscoll, Baxter, Hall, Dales, Tyrer)
avatar
E Wilcock
Megastar
Posts: 1181
Joined: 11 Oct 2014 07:59
Family Historian: V7
Location: London
Contact:

Re: Custom queries - Tuition needed

Post by E Wilcock »

Thank you Peter - There are several options for picking spouse, arent there?

So I need to avoid Family and go for Indi spouse, it seems.
But it is a case of blundering about and hoping.
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Custom queries - Tuition needed

Post by tatewise »

Evelyn, I suspect you are not using the DRA to obtain the entire data ref, but using it repeatedly​ to get different components.
Your example looks invalid​ and not composed entirely by one call upon DRA.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
E Wilcock
Megastar
Posts: 1181
Joined: 11 Oct 2014 07:59
Family Historian: V7
Location: London
Contact:

Re: Custom queries - Tuition needed

Post by E Wilcock »

Peter - That worked. But I just pasted in what you put in your post.

I feel liberated now. I was never that skilled at searches in TMG, and I feel I can really look at my fh data now in all sort of ways.
avatar
E Wilcock
Megastar
Posts: 1181
Joined: 11 Oct 2014 07:59
Family Historian: V7
Location: London
Contact:

Re: Custom queries - Tuition needed

Post by E Wilcock »

tatewise wrote:Evelyn, I suspect you are not using the DRA to obtain the entire data ref, but using it repeatedly​ to get different components.
Your example looks invalid​ and not composed entirely by one call upon DRA.
Yes Mike - you are right. It has been a long hard afternoon with me doing it wrong. But I am now very happy. Thank you.
User avatar
LornaCraig
Megastar
Posts: 3190
Joined: 11 Jan 2005 17:36
Family Historian: V7
Location: Oxfordshire, UK

Re: Custom queries - Tuition needed

Post by LornaCraig »

Evelyn,
Yes, it takes a while to learn how to navigate round these things. I've been using FH for years but still have trouble sometimes!

In this particular example what you want is an attribute of the spouse as an individual, not an attribute of the family. In fact Family records can't have attributes, which is why you cannot expand the Attributes folder in the Family section in the Data Reference Assistant. (And you will see that if you expand the Events within the Family section they are all events which only apply to Family records, e.g.marriage, divorce, separation.)

This is why you need to go straight down to the Spouse and expand their Attributes section, where you will see all their possible individual attributes listed. Just scroll down to National or Tribal Origin and select that line. The Expression will be ready-made for you.
Lorna
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Custom queries - Tuition needed

Post by tatewise »

Yes, if you cannot find the element you are looking for then probably looking in wrong branch of DRA.
Although, there are a few rare elements missing in some branches.

BTW: To access second spouse use ~SPOU[2]
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
PeterR
Megastar
Posts: 1135
Joined: 10 Jul 2006 16:55
Family Historian: V7
Location: Northumberland, UK

Re: Custom queries - Tuition needed

Post by PeterR »

I've dug a little deeper, and it is quite easy to obtain the OP's original expression using the Data Reference Assistant:

Code: Select all

%INDI.FAMS[1]>~SPOU[1]>NATI[1]%
I used it beside my original expression and got different results.

Unless I am mistaken (I hope others will test to confirm) the above, apparently valid, expression seems to always give the HUSBand's NATIonality, not the SPOUse's.
Peter Richmond (researching Richmond, Bulman, Martin, Driscoll, Baxter, Hall, Dales, Tyrer)
User avatar
LornaCraig
Megastar
Posts: 3190
Joined: 11 Jan 2005 17:36
Family Historian: V7
Location: Oxfordshire, UK

Re: Custom queries - Tuition needed

Post by LornaCraig »

Yes, I think you are right Peter.

Expanding the Family section in the DRA and then expanding either Husband or Wife to get to their nationality attribute does give the correct result. But expanding the Spouse and selecting their nationality attribute seems to always produce the husband’s nationality.

More generally, a quick test suggests that selecting any fact (attribute or event) this way will always produce the husband’s fact in the results.

I had never tried accessing the data reference for a spouse’s facts via the family section like this, but I have now realised that, bizarrely, it is possible to keep expanding the spouse-within-the-spouse-within the spouse....

So for example you can have columns in a query like this:
Columns.JPG
Columns.JPG (35.24 KiB) Viewed 14135 times
Which produces results like this (where John Harris, British, and Susan Turner, Australian, are husband and wife).
Results.JPG
Results.JPG (31.08 KiB) Viewed 14135 times
Lorna
avatar
TimTreeby
Famous
Posts: 169
Joined: 12 Sep 2003 14:56
Family Historian: V6.2
Location: Ogwell, Devon
Contact:

Re: Custom queries - Tuition needed

Post by TimTreeby »

%INDI.FAMS[1]>~SPOU[1]>NATI[1]%

Will always return the Husbands Nationality because that is what you are asking for.

Because if you read it backwards you are asking for

The 1st Nationality
of the 1st Spouse
of the 1st Family Record
of the Individual.

Family Records contain 2 Spouses HUSB and WIFE
And unless much mistaken the 1st Spouse is always the Male, So to get the Wife's Nationality you need the Expression

%INDI.FAMS[1]>~SPOU[2]>NATI[1]%
avatar
Gowermick
Megastar
Posts: 1702
Joined: 13 Oct 2015 07:22
Family Historian: V7
Location: Swansea

Re: Custom queries - Tuition needed

Post by Gowermick »

TimTreeby wrote: And unless much mistaken the 1st Spouse is always the Male
Whilst you may be correct, in a diagram, when you opt to show marriage information, it is only shown against the Female half of the marriage! (See my new post)
Mike Loney

Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
avatar
E Wilcock
Megastar
Posts: 1181
Joined: 11 Oct 2014 07:59
Family Historian: V7
Location: London
Contact:

Re: Custom queries - Tuition needed

Post by E Wilcock »

I dont want to get muddled about this. My final list looked fine - I had a results list containing people of both genders. Coming to fh from TMG I am not used to Family facts.

So I understood Peter's first suggestion. %INDI.~SPOU[1]>NATI[1]%

But I would like to clarify the numbers. If someone married twice and their second spouse was not a UK national, they would not be selected if the filter (above) looks only at the first partner.

But writing a further line wont bring an individual who had already been discarded back into the results set?

I deliberately postponed learning about numbered events (Census). Mike warned me I would eventually need to and it seems the time has come.
avatar
E Wilcock
Megastar
Posts: 1181
Joined: 11 Oct 2014 07:59
Family Historian: V7
Location: London
Contact:

Re: Custom queries - Tuition needed

Post by E Wilcock »

I just had a thought. If one omitted the numeral, would that cover all instances of SPOU and thus all spouses?

%INDI.~SPOU>NATI[1]%
User avatar
LornaCraig
Megastar
Posts: 3190
Joined: 11 Jan 2005 17:36
Family Historian: V7
Location: Oxfordshire, UK

Re: Custom queries - Tuition needed

Post by LornaCraig »

No, omitting the numeral is equivalent to using numeral 1, so %INDI.~SPOU>NATI[1]% is equivalent to %INDI.~SPOU[1]>NATI[1]%.

You will need additional columns and row conditions in the query for each spouse, and if you want to cater for cases where a spouse has more than one nationality you will need extra columns and row conditons for those too.

Taking one step at a time, let's assume for the moment that nobody has more than one nationality.
Let's also assume that you want to find every individual who has British nationality themselves, but has at least one spouse who is not British.

You are right that you need to make sure you don't exclude anyone too early in the process, but also you don't want to add too many people too soon. I think you need to leave the test for the nationality of the individual themselves until last, don't do it first.

You will need the following columns to display the results (adding more if anyone has more than three spouses!)
Columns2.JPG
Columns2.JPG (23.05 KiB) Viewed 14067 times
And the following rows (again adding more if anyone has more than three spouses)
Rows2.JPG
Rows2.JPG (22.89 KiB) Viewed 14067 times
This will start by adding anyone whose first, second or third spouse in not British, so unfortunately it will include cases where no nationality has been recorded for a spouse. Finally it will exclude cases where the individual himself/herself is not British.
WARNING: this will exclude individuals who have no nationality recorded. (I haven't found a way round that yet, perhaps someone else can).

I have tested this briefly but not exhaustively. Apart from the problem of handling cases where no nationality is recorded, I think it works. Someone else may come up with a better solution.
Lorna
avatar
E Wilcock
Megastar
Posts: 1181
Joined: 11 Oct 2014 07:59
Family Historian: V7
Location: London
Contact:

Re: Custom queries - Tuition needed

Post by E Wilcock »

Lorna and everyone - I really appreciate your help and the discussion.
Looking at Lorna's final suggestions late last night, I realised that I have(I think) totally misunderstood queries in fh.

I find the prose and the terminology used in the Getting the Most book (and the software) very confusing and somehow picked up the idea that once you had selected the first list of people, i.e. the first row, subsequent lines could only subtract from it. Laura suggesting a different line order suggests one can add people too?.

I could have confused queries and filters.

But I think it is also due to the examples given in Getting the Most? Exclude if, plus the use of negatives.

Again and again I feel users need a good non technical primer as a guide to fh. The long list of queries that come with fh seem mystifying.
The sort of search I want to do with any genealogy software is

Find all individuals who have a particular (selected) event either in a certain year or during a period between two years.

Find all individuals who appear on a particular census (selected by date) in a particular town. Or whose death occurred in a particular place.
Or who were interned in a particular camp - is it that one uses search in fh rather than a query?

Custom queries and writing them come near the end of the book, whereas I would expect to be using custom queries constantly to search for data.

Other users may have other suggestions but I would love to see a little list for new users . Someone kindly and early in my use of fh gave me a way of finding all people born in a certain country during a certain time period. But receiving a custom query written by an expert, delayed my understanding how queriess work. It shouldnt be so terribly difficult.

The pages of Getting the most which particularly confused me are 204 and 205 - I still dont understand them. It does look indeed as if the rows are called filters and one has constantly to narrow down the search.

I am away from computers today so there will be a delay in my trying things out and responding.
Last edited by E Wilcock on 12 Apr 2017 13:59, edited 1 time in total.
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Custom queries - Tuition needed

Post by tatewise »

I suspect the part that is confusing you is:
When Family Historian runs, it starts by putting all items of the query type (all Individual records if it's an Individual query, or all facts, if it's a fact query) into the Input Set. The Result Set list is empty at this point.

As stated earlier, Family Historian then applies each filter in turn.

For each filter, if the filter has an Add condition (Add if… or Add Unless…), Family Historian will look through the Input Set to find items that match (if it is an Add If…) or don't match (if it is an Add Unless...) the condition specified in the filter. Any items found will be moved from the Input Set to the Result Set.

If the filter has an Exclude condition (Exclude if... or Exclude Unless…), Family Historian will look through the Result Set to find items that match (if it is an Exclude If…) or don't match (if it is an Exclude Unless…) the condition specified in the filter. Any items found will be moved back from the Result Set to the Input Set.
The key concept is that Add rules will add to the Result Set, and Exclude rules will remove from the Result Set. So the Result Set is not always decreasing.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
Gowermick
Megastar
Posts: 1702
Joined: 13 Oct 2015 07:22
Family Historian: V7
Location: Swansea

Re: Custom queries - Tuition needed

Post by Gowermick »

It is worth bearing in mind, that the rules are applied in sequence, which can greatly affect the resultset.
For example (with simplified rules for explanation purposes

1. Add if surname is Smith
2. Add if surname is Jones
3. Exclude if spouse surname is Green
This will result in a list of all Smith and Jones, then remove all those who married someone called Green

BUT, if the order of the rules were changed to this

1. Add if surname is Smith
2. Exclude if spouse surname is Green
3. Add if surname Jones

This will result in a list of all Smith and Jones, but only excludes Smiths who married someone called Green.
No Jones will be excluded, because the exclude Green rule was applied before any Jones were added to the resultset
Mike Loney

Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Custom queries - Tuition needed

Post by tatewise »

Yes, that illustrates perfectly that the Result Set can both expand (with Add rules) and contract (with Exclude rules).

If the rules are changed again to the following it is even more subtle:
  1. Exclude if spouse surname is Green
  2. Add if surname is Smith
  3. Add if surname is Jones
The Exclude rule will put everyone NOT partnered with a Green into Result Set.
The Add rules will add everyone named Smith or Jones even if they are partnered with a Green.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
Gowermick
Megastar
Posts: 1702
Joined: 13 Oct 2015 07:22
Family Historian: V7
Location: Swansea

Re: Custom queries - Tuition needed

Post by Gowermick »

Mike,
Have you got that right?
Before applying first rule (exclude Spouse is Green), the result set is empty, so there is nothing to exclude. Are you missing an Include All?

Edit:
Answered my own question. As I understand it, If there are NO rules or if first rule is an exclude statement, All the records are copied across to the result set. This makes some sort of sense, because the rules are Filters, and with no rules, there is no filter so everything gets copied into the resultset. However, with nothing in the resultset, if the first rule is an exclude rule, there will be nothing to exclude, so everything makes it to the resultset

Howver, is the exclude rule then applied? Personally I think not - Mike, over to you
Mike Loney

Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Custom queries - Tuition needed

Post by tatewise »

The Query Window: Rows Tab Help page (as usual) explains all.
With Query Window open hit F1 key and follow link to The Rows Tab:
If the first filter is one of the 'Add' ones, it initially makes the Result Set empty.
If the first filter is an 'Exclude' one it initially puts all records (of the type used by the query) in the Result Set.
Then it applies each filter sequentially to the Result Set.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
Gowermick
Megastar
Posts: 1702
Joined: 13 Oct 2015 07:22
Family Historian: V7
Location: Swansea

Re: Custom queries - Tuition needed

Post by Gowermick »

Mike,
With respect, you didn't answer the question. The book explains that if there is no filter statement, or the first statement is an exclude statement, all the input file is copied to the resultset/output file. But it doesn't explain whether the rule of this exclude statement is applied or not. I was just asking you to confirm whether it is applied or not? Yes or No? :D
Mike Loney

Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Custom queries - Tuition needed

Post by tatewise »

The answer is Yes, as stated in the Help I quoted:
Then it applies each filter sequentially to the Result Set.
which by definition includes the first rule whether Add or Exclude.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
Post Reply