* Query to find family branch members

[quarlton]

I would like to create a query that would identify which branch of my family people belong to.

e.g. Select one of my 4 grandparents and return a list of all people (not just direct ancestors) in that branch.
(As far as I know, there are no cross branch connections above my grandparents)

Had a quick search but couldn't find a suitable hint.



Many thanks

[Gowermick]

Dave,
I’m working from memory, ( not on PC), so following is the rough idea of what Row expressions you need

1. Add if File root to the Resultset
2. Add if DirectAncestor of anyone currently in the Resultset. ( Add all direct ancestors of File root)
3. Add if Descendant of anyone in Resultset ( now add all their descendants. Here you can apply restrictions to number of generations you want to include (1= Include Children, 2 = Include Granchildren etc etc

The terms you need to search for are ‘IsDirectAncestorof’ and ‘IsDescendantof’.

When I’m back on PC, I’ll post my actual query, which does exactly what you need (unless someone else beats me to it )
Hope this helps

[Gowermick]

Dave,
I’m working from memory, ( not on PC), so following is the rough idea of what Row expressions you need

1. Add if File root to the Resultset
2. Add if Ancestor of anyone currently in the Resultset. ( Add all direct ancestors of File root)
3. Add if Descendant of anyone in Resultset ( now add all their descendants. Here you can apply restrictions to number of generations you want to include (1= Include Children, 2 = Include Granchildren etc etc

The terms you need to search for are ‘IsAncestor’’ and ‘IsDescendant’

When I’m back on PC, I’ll post my actual query, which does exactly what you need (unless someone else beats me to it )
Hope this helps

[quarlton]

Thanks Mike, I'll give it a try this morning.
I've done something similar before but it was a while ago and the memory isn't what it was

Dave

[jelv]

Using ‘IsDirectAncestorof’ and ‘IsDescendantof’ isn't going to pick up spouses of people in the branch and their relations.

[Gowermick]

As I was saying, I nearly got it right , just missed the Spouses option (Thanks Jelv)

on General Tab:
1.) Add if = IsSameItem(,FileRoot()) is true

On Relations Tab
2.) Add if Relationship Ancestor Max generation Leave Blank to capture all
Select option Anyone in the current result set

3.) Add if Relationship Descendant Max generation set as required
Select option Anyone in the current result set and Tick Inc Spouses of relatives if Required

[tatewise]

As John says, using the method proposed by Mike will NOT include everyone in a grandparent branch.
IsAncestorOf(...) function will only return direct blood line ancestors.
IsDescendantOf(...) function will only return direct blood line descendants.
However, I think Mike is referring to the Query, Rows tab, Relations tab filters.
That does have options to Inc. spouse of relatives but still suffers the same as the functions named above.
Add if Ancestor will add only direct blood line ancestors and optionally their spouses.
Add if Descendant of Anyone if the current result set will only add direct line descendants and optionally spouses.
That will also include your parents, you, and your children, etc...
But it does NOT include ancestors of any of those optionally included spouses.
If you then Add if Ancestor of Anyone if the current result set that will include ancestors of your parents, you, and your children, etc, so will include far more than required.

However, I think the following filters as per screenshot may be a solution but they need some intensive testing.
Add if Ancestor of Grandparent and Inc. spouse of relatives
Add if Descendant of Anyone if the current result set and Inc. spouse of relatives
Exclude if Descendant of Grandparent Inc. original individual and Inc. spouse of relatives
( that removes your grandparents, your parents, you, and your children, etc. )
Add if Ancestor of Anyone if the current result set and Inc. spouse of relatives
Add if Descendant of Anyone if the current result set and Inc. spouse of relatives
Exclude if Descendant of Grandparent Inc. original individual and Inc. spouse of relatives
( repeat the last three filters enough times to capture the entire branch, except the last filter must NOT use the Inc. original individual option )

image.png (41.11 KiB) Viewed 1363 times

[arthurk]

Or you could just use the inbuilt All Relatives query for each grandparent in turn.

The slight drawback to this is that each grandparent appears in their spouse's list as well as their own, but if you can live with that you might find it simpler.

[LornaCraig]

Or you could just use the inbuilt All Relatives query for each grandparent in turn.

I think the All Relatives query only achieves what the first two lines of Mike (Tate's) query does. It won't include any relatives-of-spouses and it won't excude the grandparent's descendants (although I'm not sure whether the OP needs them to be excluded)

[tatewise]

I ran the All Relatives query and my proposed grandparent branch query on the Family Historian Sample Project using Michael Pleasance MUNRO as the root grandparent.

All Relatives returned a Result Set of 52 people which as Lorna says includes dozens of descendants.
It also includes the wife of the grandparent who is the root of a different grandparent branch.
As Lorna says, I think it does not include all spouse branches. i.e. those without a Generation number.

My query returns a Result Set of 9 people and excludes the descendants and the root's wife.
On another Project it includes descendants of side branches with Generation numbers greater than 0.
i.e. cousin's once-removed.
It would be difficult to include those side branches and filter out descendant generations of the root in a Query based on the All Relatives query.

[LornaCraig]

Here’s another suggestion. TEMPORARILY unlink the grandparent from their spouse (or spouses, if more than one). This will also automatically unlink them from their descendants.

Now create an All Relatives and Indirect Relatives diagram for the grandparent. This is, in effect, a diagram of everyone left in the same Pool as the grandparent.

Select all the boxes by dragging the mouse to draw a rectangle round the entire diagram.
Use Edit > Add to Named List.

The list can be viewed in the Query window if required, using a List Row filter Add if … Is In List.

Now RE-LINK the grandparent to their spouse(s).

Unfortunately any new additions would need to be added to the list manually, or the process repeated.

[jelv]

I'm wondering about asking for a new function that would help solve problems like this.

When you use the relationship and relationcode functions there will be many individuals where there is no result returned is they are more distant. However, when you use the How Related tool it says there is no direct relationship but also gives an indirect relationship for almost all of them. FH must be able to determine the path between the two individuals to say what the indirect relationship is.

What I'm wondering is about a function that returns all the individual id's in that path as a CSV string? The one wrinkle I've spotted is where the path includes a couple - perhaps that could appear as the two id's separated by a colon.

For the OP's request it would make the query much simpler and more robust. You'd simply add anyone where the first person in the string was one of the selected grandparents parents.

I think it would also make it possible when doing diagrams to highlight all the individuals linked to one of your DNA matches without setting flags on all the linking individuals which is the solution often used.

[arthurk]

I ran the All Relatives query and my proposed grandparent branch query on the Family Historian Sample Project using Michael Pleasance MUNRO as the root grandparent.

All Relatives returned a Result Set of 52 people which as Lorna says includes dozens of descendants.
It also includes the wife of the grandparent who is the root of a different grandparent branch.
As Lorna says, I think it does not include all spouse branches. i.e. those without a Generation number.

That last point might be important, but for the rest, it might help if the OP could clarify what is wanted:

I would like to create a query that would identify which branch of my family people belong to.

e.g. Select one of my 4 grandparents and return a list of all people (not just direct ancestors) in that branch.
(As far as I know, there are no cross branch connections above my grandparents)

I would take that to include descendants of ancestors, rather than just siblings, which I think is what your (Mike's) suggestion does.

[LornaCraig]

....I would take that to include descendants of ancestors, rather than just siblings, which I think is what your (Mike's) suggestion does.

Yes, the OP wants descendants of ancestors. But descendants of ancestors also include descendants of the selected grandparent himself/herself (because the grandparent is a descendant of their own ancestors). Mike has assumed that those latter people are to be excluded, although it’s not clear whether the OP wants them to be excluded.

If the descendants of the grandparent are to be excluded while all other descendants of ancestors are to be included, along with their spouses and relatives-of-spouses, then the unlinking method I suggested in my second reply will work.

[tatewise]

The one overriding caveat to all these proposals is that there must be no marriage/partnership between people in different grandparent branches.
The OP says there are no such cross branch connections so for him that is OK.

However, in general, any such cross branch connections will merge the members of the branches.
I think the only solution is the All Pool Relatives Except Partner Ancestors plugin.

[LornaCraig]

I'm wondering about asking for a new function that would help solve problems like this.
...
What I'm wondering is about a function that returns all the individual id's in that path as a CSV string? The one wrinkle I've spotted is where the path includes a couple - perhaps that could appear as the two id's separated by a colon.

For the OP's request it would make the query much simpler and more robust. You'd simply add anyone where the first person in the string was one of the selected grandparents parents.

How would that differ from simply selecting everyone with the same Pool number as the starting person? In either case it would include the spouse's relatives, which the OP doesn't want in this case, and descendants of the starting person, which the OP may or may not want.

[jelv]

All of my grandparents (and my wife's) are in the same pool. I'd have to do the unlinks to get it to work.

[tatewise]

I'd have to do the unlinks to get it to work.

Which 'it' are you referring to?
You don't have to do anything before running my proposed Query using the Rows tab and Relations tab.

[jelv]

I was replying to Lorna's suggestion of using the pool numbers which is a complete non-starter.

My interpretation of the original request was anyone in any way related to the selected grandparent's parents.

I'm currently testing your suggestion. Should the penultimate line have "Include relatives spouses"?

So far it's looking good.

[LornaCraig]

I was replying to Lorna's suggestion of using the pool numbers which is a complete non-starter.

I was asking how your idea of adding "anyone where the first person in the string was one of the selected grandparents parents" would be any different. As far as I can see (but I may have misunderstood what you mean) it would produce the same results as selecting the whole pool, so wouldn't help.

All of my grandparents (and my wife's) are in the same pool. I'd have to do the unlinks to get it to work.

Yes, that's why in the suggestion in one of my earlier posts I started by saying you need to temporarily unlink the grandparent from their spouse and re-link after you have added the selected people to a named list. It may not be elegant but it's not a complete non-starter. I tried it and it took less than a minute.

[jelv]

I wasn't clear about whether the CSV string would include the endpoints - perhaps it should in which case it would be the second number. Simple example from the sample project showing relationship of Paula Charlotte Munro to Elizabeth Alice Munro and what the function would return:

Relationshippath.png (18.69 KiB) Viewed 1171 times

The string would be 27,26,6:25,50.

If the person is somehow connected to the grandparent, the first id would be the grandparent, the next id would be the grandparent's father or mother. Then, if the person was related to one of the grandparents siblings the next number would be the id of the sibling, otherwise it will be the id of the great grandparent.

If they are in the same pool but not related through the selected grandparent, the first id is the grandparent and the second would be either one of the grandparents children, or the grandparents spouse neither of which are required and nor are any of the individuals where the route starts that way.

[jelv]

Yes, that's why in the suggestion in one of my earlier posts I started by saying you need to temporarily unlink the grandparent from their spouse and re-link after you have added the selected people to a named list.

In my opinion, anything that requires modifying the tree to get the desired result from a query is an incredibly bad idea. Far too easy to forget to reverse the modifications!

[jelv]

I've generalised Mike's query to be all branch relatives of a selected individual (not grandparent) - no logic change to a change to the prompt and added a couple more sets to make it go wider if needed.

It selects everyone related to an individual root person EXCLUDING:

• their spouse and anyone related via the spouse
• their children and anyone related via the children.

I've a bit more testing to do when I get back in later tonight but I think it's looking good - thanks Mike.

(Original attachment deleted. See later in this thread for the latest version)

[LornaCraig]

I wasn't clear about whether the CSV string would include the endpoints - perhaps it should...

I can see how the string works if you can specify endpoints. But in the case discussed in this topic there are no fixed endpoints. From what you have said I think the function would need a parameter or parameters indicating the ‘directions’ in which you want the string to set off, i.e. to the father and the mother but NOT to the spouse or the children. However I’m no expert on functions so I won’t try to comment further on this idea!

In my opinion, anything that requires modifying the tree to get the desired result from a query is an incredibly bad idea. Far too easy to forget to reverse the modifications!

Understood. It depends on your priorities. For me it would take longer to construct a query (if Mike and you hadn’t provided one) or think about setting parameters in Mike’s plugin. Time is of the essence.

[quarlton]

Hi Everyone

Apologies for not coming back sooner but I got called away on a family emrgency

Many thanks for everyone's input - I seem to have opened a can of worms - never though that it would be as big a problem as it appears to be.

I will have a look at all your comments and suggestions tomorrow.
Again, many thanks

Dave