* Query to exclude non relatives

[uktony]

I have written a query to summarise those individuals for whom a 1939 Census/War Record entry should be recorded, but I haven't marked it as such.

The query runs perfectly but includes individuals who have no relationship to root.

I want to add a condition to exclude this group but can't find the right field to use in the select criteria. Is there one?

I've searched through the historic topics and the help file but am none the wiser.

Hoping those more knowledgeable than me can point me in the right direction.

[tatewise]

This is one of those cases where it can't be resolved with a Data Reference.
Therefore, the Data Reference Assistant will NOT help you.

You need to use one or other of the FH Functions. See FHUG Knowledge Base Understanding Functions.

However, which function you need depends on what you mean by "individuals who have no relationship to root".

There is the =IsRelativeOf( FileRoot(), %INDI% ) which is true for all direct relatives of the File Root and direct relatives of partners of those direct relatives, but is not true for other relatives via partnerships that puts those Individuals in the same Relation Pool as the File Root.

So on the Rows filter tab use:
Exclude unless...
=IsRelativeOf( FileRoot(), %INDI% ) or alternatively =IsTrue( RelationPool( FileRoot() ) = RelationPool( %INDI% ) )
is true

Try those two alternatives and the former will probably list fewer people than the latter.

[uktony]

Thanks Mike

I will give it a go tomorrow

[uktony]

Well....that worked. Just the job

Thanks again Mike

[tatewise]

Which alternative expression worked best for you?
Or did they both give the same results?

[uktony]

I used

Exclude unless =IsRelativeOf( FileRoot(), %INDI% ) is true.

It gave the answer I wanted; when I have a spare 5, I might try the second option and see what happens

[Little.auk]

Mike
I have tested both of these on my FH project

The Exclude unless... =IsRelativeOf( FileRoot(), %INDI% ) returns 786 records

The Exclude unless... =IsTrue( RelationPool( FileRoot() ) = RelationPool( %INDI% ) ) is true returns 1041 of the 1126 people in my tree.

I will compare the two result sets and let you know what the difference between the two queries is.

I use the two filters below which returns the same result as =IsRelativeOf +1 (787) - because it includes the Root person.

Add if an ancestor (max.99 generations) of ["Root"]. Include original individual. Include relatives spouses.
plus
Add if a descendant (max.99 generations) of ["Root"]. anyone in the current results set. Include relatives spouses.


The ancestor - descendent filter gives the flexibility of limiting the number of generations up and/or down - I use 4 generations for ancestors and 1 generation for descendants quite a lot.

[tatewise]

Yes, those Relations tab options are effectively the same as the =IsRelativeOf( FileRoot(), %INDI% ) function.
They are the File Root's 786 direct blood-line relatives.

The additional 255 people who are in the same Relation Pool are further ancestors, descendants, and spouse families of those 786 direct relatives. i.e. They are various forms of 'cousins'.

[Little.auk]

Mike

I have edited one of my ancestor queries and added the Relation column which is the =IsRelativeOf(FileRoot(),%INDI%) expression and the Relation 2 column which is the =IsTrue( RelationPool( FileRoot() ) = RelationPool( %INDI% ) ) expression.

The extract below shows all the Deveraux it returns
Basil Newman Devereux, the husband of my great aunt Ivy Maud HEATH, and Jean, their daughter are the only ones who show as =IsRelativeOf(FileRoot(),%INDI%).

All those with an ID beginning IND-148 are Basil's siblings, IND-147 are his father and his siblings, and IND-336 is his grandfather.

So, with me set as "FileRoot", "RelationPool" is picking up everyone in my extended tree, e.g. my great aunt's husband's brother.

I suppose it shows the difference between a Relation and a Relative.

Hope that is of use

Devereux.jpg (98.13 KiB) Viewed 1424 times

[LornaCraig]

Yes, those Relations tab options are effectively the same as the =IsRelativeOf( FileRoot(), %INDI% ) function.
They are the File Root's 786 direct blood-line relatives.

The additional 255 people who are in the same Relation Pool are further ancestors, descendants, and spouse families of those 786 direct relatives. i.e. They are various forms of 'cousins'.

No, the additional 255 people do not include "various forms of cousins", or "further ancestors, descendants". Those people are always blood-line relatives (ignoring the exception of adoptions), so are included among the 786 direct relatives. The extra people in the Pool are only linked indirectly via spouses. They include blood-line relatives of the spouses, along with other more distant in-laws-of in-laws etc.

[tatewise]

That is what I meant, although I accept worded badly, and why I put 'cousins' in quotes.

It is really quite confusing for newcomers to the concepts of relationships.
Consider the Family Historian Sample Project where Ian Stephen MUNRO is the File Root.

His dad Anthony Edward MUNRO and mum Susan Isabel DOWLING are ancestors, so =IsRelativeOf(FileRoot(),%INDI%) is true.

But his dad's first wife Julia Amanda FISH is also considered a direct relative and =IsRelativeOf(FileRoot(),%INDI%) is true even though she is not a blood relative!
However, her parents and siblings are not considered direct relatives but are in the same Pool.

A similar scenario applies to his uncle's wife Helen Mary ADDISON who is a direct relative but her parents are not.

[LornaCraig]

As far as I recall, the early versions of the "Getting the Most from FH" book used to contain a clear definiton of a "relative". It was something like "anyone who is an ancestor, a descendant, or a descendant of an ancestor, plus a spouse of anyone in that set." (Part of this definiton is actually redundant because your own descendants are included among the descendants of your ancestors.) In other words, anyone you can share DNA with, plus their spouses. Anyone else who is linked only via a spouse is in the same Pool but is only an indirect relative. If you stick to the "DNA plus spouses" rule you can't go wrong. (Barring the usual exception of adoptive relationships). The definition makes perfect sense and I have never found it confusing.

Unfortunately recent versions of FH have obscured this definition. The glossary in the current Help pages says "Your relatives are your ancestors, your descendants, and your ancestors' descendants. Spouses of any of the above are usually counted as your relatives too. Your spouse's relatives (your in-laws) may also count". This looks like a definition of the woolly way the word "relative" is used in everyday language. It completely glosses over the fact that for FH purposes there is a clear and important distinction between a direct relative and an indirect relative. And the glossary doesn't even include a defintion of a Pool. There is a defintion of Pool elsewhere, under RelationPool in Relationship Functions, but it refers to "a group of people who are directly or indirectly related to one another" without explaining the difference!

[tatewise]

No wonder then that it is confusing for newcomers to FH

[Little.auk]

The Oxford English Dictionary doesn't help -
It defines Relative (noun) as - "a person connected by blood or marriage".
and Relation (noun) as - "a person who is connected by blood or marriage; a relative".

The Ancestor and Descendant filters that I use are in the Row / Relations tab of the FH Query screen. They give the option of including or excluding spouses, (and also the "Root" person).

The only practical use I have found for the RelationPool() expression is in identifying "Orphan" families or individuals - of which I have a few that I have added over time, because I thought they might be relations, but have not yet been able to find a connection to!

[LornaCraig]

Peter,

The first two lines of your query are potentially capturing group of people who include more than just direct Relatives but fewer than an entire Pool. Perhaps that’s why you had to add the 3rd line, using =IsRelativeOf . That prunes out the extra people, but you could have used a single line query: Add if =IsRelativeOf … is true.

Disregarding the third row, your first two row filters are similar to those used in the standard All Relatives query. BUT you are including spouses of Ancestors in the very first row. This means that if an ancestor had a second (non-ancestral) spouse and that spouse had any descendants from another partner those other descendants would be included by the second row. But those descendants are not ‘direct’ Relatives. You don’t share any DNA with them.

On the other hand those two rows are not capturing the entire Pool, because they won’t capture any ancestors of the non-ancestral spouse, or any in-laws they have from their other partner.

As you will see, the standard All Relatives query doesn’t add spouses until the very last stage, so it captures only direct blood-line relatives plus their spouses. If it’s any comfort, the FH standard query used to make the same mistake (adding the spouses at the wrong stage) until I pointed it out to Calico Pie!

I don’t think there is anything to be gained by dwelling on the difference, if there is one, between ‘Relative’ and ‘Relation’ in everyday language. The important definitions for FH purposes are ‘direct Relative’ (sometimes just referred to as ‘Relative’) and ‘Pool’, as explained in my previous reply.

The FH definition of a Relative determines who is included in an All Relatives diagram. You will see that spouses are included in the diagram but there is no room for the spouses’ other relatives to fit. The difference can be seen by comparing an All Relatives diagram with an All Relatives +Indirect Relatives diagram. Having worked with diagrams from the outset I have always found the distinction intuitive and unambiguous, but if you don’t work with diagrams I can see that there is great scope for confusion and sadly the current documentation doesn’t make the definitions clear.

[Little.auk]

Lorna,

Sorry, perhaps I should have deleted the third filter line before adding the image!

If you look closely, you will see that the "IsRelativeOf" filter line is dimmed out, because it is disabled - achieved by right clicking on the filter and clicking on the dropdown.

This is a very useful facility for testing different filter options, as in this case where I was testing whether the first "Ancestor/Descendant" combination gave the same output as "IsRelativeOf".

Contrary to your post, the answer is that it does, the only difference is that the "Ancestor/Descendant" filter gives the option of including or excluding the "Root" person.

I also use this facility to make some of my queries multi-use by creating several filters which I disable and enable as needed.

[LornaCraig]

Sorry, perhaps I should have deleted the third filter line before adding the image!

Yes, I realised that it was disabled in the screenshot.

This is a very useful facility for testing different filter options, as in this case where I was testing whether the first "Ancestor/Descendant" combination gave the same output as "IsRelativeOf".
Contrary to your post, the answer is that it does, ....

It will give the same results unless you happen to have any instances where a ancestor had another (non-ancestral) spouse and you have recorded some descendants of that other spouse via their other (unrelated) partner. That's why it's difficult to spot the error.
Here is an example:

Test group.JPG (23.89 KiB) Viewed 1189 times

If you construct this group and run your query, taking Child SMITH as the root person, you will find that the first two Rows return the same list of individuals as the =IsRelativeOf function. But if you now give ‘second wife GREEN’ an additional spouse and then create some children for her with her additional spouse you will find that the result set from your first two Rows includes those extra children, who are not relatives of Child A. Enabling and disabling your third Row demonstrates this.

This is why the Spouses should only be added at the last stage, as in the updated standard All Relatives query.

[Little.auk]

This is why the Spouses should only be added at the last stage, as in the updated standard All Relatives query.

I have tried running the query with "Include Relatives Spouses" turned OFF in the Ancestors filter and found it has a minor glitch somewhere as shown in the Excel table below which shows an extract of the results for the three filters.

William BLACK was the first husband of Harriet CLAXTON, and she was the mother of the six listed children. She later married Dominick DOHERTY and their first child is the bloodline link. Turning OFF the Spouses for the Ancestors filter loses William (the #N/A entry) - yet the IsRelativeOf query shows him.

Perhaps the "Descendant Spouses Only" output is strictly correct as William BLACK is not bloodline - but the children are.

Stranger still, William FARMER at the bottom of the list does show! He was the second husband of Rosina BOWLES - they had no children, the ancestor line goes through her first husband Harry HEATH!

I have tried your group construct and see what you mean - it didn't show up in my query because I have no occurrences in my project.

Now that I know about that possibility I have added a third filter Exclude If =Relationship(["Root"],,TEXT,1) is Null, which will allow the spouse but exclude any children.

Ancestors Filters Comparison.jpg (95.83 KiB) Viewed 1147 times

[LornaCraig]

William BLACK was the first husband of Harriet CLAXTON, and she was the mother of the six listed children. She later married Dominick DOHERTY and their first child is the bloodline link. Turning OFF the Spouses for the Ancestors filter loses William (the #N/A entry) - yet the IsRelativeOf query shows him.

The IsRelativeOf results are correct. It is your Row filters which are not correct. I deduce from your Excel table that although you are no longer including spouses in the first Row you are still including them in the second Row. You should not be including spouses in either of the first two Rows. You should have a separate third Row which adds spouses of anyone in the current set. Please look again at the standard All Relatives query I referred to previously. It has three straightforward Row filters.

William BLACK is not a bloodline relative, but he is a spouse of a bloodline relative so he gets added in the (new) third row. The point is that he should not be added in the first Row, because if he happened to have any other children (other than the six with Harriet Claxton) they would be incorrectly captured by the second Row.

Stranger still, William FARMER at the bottom of the list does show! He was the second husband of Rosina BOWLES - they had no children, the ancestor line goes through her first husband Harry HEATH!

Again, that is absolutely correct. William Farmer is a spouse of a bloodline relative. Remember the shorthand definition of a Relative is “DNA plus spouses”; that is, anyone in your bloodline plus a spouse of anyone in your bloodline. William Farmer is included because he is the spouse of a bloodline relative. For the same reason, a spouse of an uncle or aunt or cousin would be included.

If you create an All Relatives diagram of the bloodline of the child of Harrier CLAXTON and Dominick DOHERTY you should find that both William BLACK and William FARMER are included in the diagram because they are spouses of bloodline relatives.

I fear this discussion makes it seem as if the topic it much more complicated than it is. I can only refer again to the standard All Relatives query which is easy to understand and does the job without any need to create a custom query.

[Little.auk]

I fear this discussion makes it seem as if the topic it much more complicated than it is. I can only refer again to the standard All Relatives query which is easy to understand and does the job without any need to create a custom query.

Sorry, senior moment, I have been misinterpreting "All Relatives query" for "IsRelativeOf" expression - silly mistake!

When I built my custom query, I started with the standard "Ancestors Query" and built from that - when I should have used "All Relatives".

[LornaCraig]

Don't worry, I have senior moments too.

[Little.auk]

Not that it really matters - but I still don't understand why, when spouses were turned on in the Descendant filter, William Black didn't display but William Farmer did - In the queries that show them both, they are both listed as great (2x) grandmother's husband, so their relationship to the "Root" is the same, yet that query treats them differently.

William Black was given as one example there are other missing spouses. Comparing the full result sets, the correct three-line query returns 172 spouses of bloodline relatives - whereas the descendants and spouses filter returns 168 - missing out William Black and three others!

[LornaCraig]

When you had spouses added only at the second row (descendants) filter William Black was omitted because he is not a spouse of a descendant. That’s why you need a completely separate third row to add spouses of everyone in the result set (spouses of ancestors and spouses of their descendants).

The odd case is William Farmer, because as you say he has the same relationship to the root as William Black. However, there is a possible explanation. Looking at the great (x2) grandmother to whom he was married, do you have any ancestors for her? If so, the great (x2) grandmother is herself a descendant of an ancestor, so your second row is picking him up as the spouse of a descendant!

[Little.auk]

Thanks Lorna,

You are spot on ! All four of the ancestors whose spouses are missing from the report are "end of their line" - I haven't found parents for any of them yet, so they don't yet qualify as descendants.

I tested by giving Harriet Claxton an unnamed father and William Black then showed on the report.