* Custom Saint Query - Not Working
-
- Superstar
- Posts: 420
- Joined: 18 Nov 2012 01:50
- Family Historian: V6.2
- Location: Mesa, Arizona, USA
Custom Saint Query - Not Working
I tried to create a Custom Query to list everyone on my wife's side of the family who had a title of "Saint". A screen shot of the Row filters is attached. I have it set up to go back 60 generations for my wife's ancestors. I initially exclude everyone in the database who does not have a "Title" assigned to their profile, i.e., "Title is null".
Some of her ancestors have as many as 2 or 3 different "Titles", of which "Saint" may be the 1st, 2nd or 3rd Title. So I told the Query to check for Titles 1, 2, ands 3 for each person, and, to only add the person in the Result Set if the query found "Saint" in the first, second, or third "Title".
Unfortunately, the Query is listing everyone who has a "Title" entry, regardless of whether it says "Saint" or not. I have tried numerous combinations of filters and I cannot get it to do what I want.
Is there an obvious error in my filters?
Some of her ancestors have as many as 2 or 3 different "Titles", of which "Saint" may be the 1st, 2nd or 3rd Title. So I told the Query to check for Titles 1, 2, ands 3 for each person, and, to only add the person in the Result Set if the query found "Saint" in the first, second, or third "Title".
Unfortunately, the Query is listing everyone who has a "Title" entry, regardless of whether it says "Saint" or not. I have tried numerous combinations of filters and I cannot get it to do what I want.
Is there an obvious error in my filters?
-
- Platinum
- Posts: 46
- Joined: 06 Jan 2014 18:04
- Family Historian: V5
Re: Custom Saint Query - Not Working
Yes, the 3rd line should be Exclude Unless instead of Add if - which probably makes the 2nd line unnecessary
-
- Platinum
- Posts: 46
- Joined: 06 Jan 2014 18:04
- Family Historian: V5
Re: Custom Saint Query - Not Working
Thinking about it, that would add anybody with a Title2 or Title3 of 'Saint'
So move the relation criteria to the bottom and change it to Exclude Unless
So move the relation criteria to the bottom and change it to Exclude Unless
Re: Custom Saint Query - Not Working
Bob,
Steve got it partly right, but got it mangled in translation.
What you should do is find everyone with saint in their title and add them to the empty list.
1: addif title contains saint
2: addif titl2 contains saint
3: addif titl3 contains saint
Now go through the resulting list, and remove those who are not within 60 generations using an exclude if generation 'greater than 60' statement.
4: exclude if generation > 60
(You'll need to check syntax for this statement)
On that point, 30 generations will take you back to around 1000 AD, heavens knows where 60 generations will take you, back to Adam & Eve I suspect
Mike
Steve got it partly right, but got it mangled in translation.
What you should do is find everyone with saint in their title and add them to the empty list.
1: addif title contains saint
2: addif titl2 contains saint
3: addif titl3 contains saint
Now go through the resulting list, and remove those who are not within 60 generations using an exclude if generation 'greater than 60' statement.
4: exclude if generation > 60
(You'll need to check syntax for this statement)
On that point, 30 generations will take you back to around 1000 AD, heavens knows where 60 generations will take you, back to Adam & Eve I suspect
Mike
Mike Loney
Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
- tatewise
- Megastar
- Posts: 28341
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Re: Custom Saint Query - Not Working
Bob, let's first explain where your original Rows filter is going wrong.
Add if an ancestor adds all your required Individuals.
Exclude if %INDI.TITL% is null excludes all with no Title, but keeps anyone with any Title(s).
Add if %INDI.TITL[?]% contains does nothing, because all Title facts are kept by above filter.
As Stevie says, the Add if filters would add everyone with a Title of Saint regardless of ancestry.
Mick's solution is correct with a final filter of:
Exclude unless an ancestor ...
It is easier using a Fact Query with these filters, where their order is irrelevant, and catches all instances of Title:
Exclude unless =IsAncestorOf(GetRecord(%FACT%),["Person"]) is true
Exclude unless =FactLabel(%FACT%) matches 'Title'.
Exclude unless =FactValue(%FACT%) matches 'Saint'.
Add if an ancestor adds all your required Individuals.
Exclude if %INDI.TITL% is null excludes all with no Title, but keeps anyone with any Title(s).
Add if %INDI.TITL[?]% contains does nothing, because all Title facts are kept by above filter.
As Stevie says, the Add if filters would add everyone with a Title of Saint regardless of ancestry.
Mick's solution is correct with a final filter of:
Exclude unless an ancestor ...
It is easier using a Fact Query with these filters, where their order is irrelevant, and catches all instances of Title:
Exclude unless =IsAncestorOf(GetRecord(%FACT%),["Person"]) is true
Exclude unless =FactLabel(%FACT%) matches 'Title'.
Exclude unless =FactValue(%FACT%) matches 'Saint'.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
Re: Custom Saint Query - Not Working
Mike,
Whilst you are correct in what you say regarding fact filters, for someone not familiar with logic, I think it is harder for them to understand and appreciate what is going on.
With the original query, one can step through each filter in turn and actually visualise what is happening to the resultset as one goes, moving individuals into and out of the list as each filter is applied. One could even check that each filter is doing what we expect by examing the resultset, before adding the next filter. For the novice, this is perhaps the best way to proceed.
Whilst you are correct in what you say regarding fact filters, for someone not familiar with logic, I think it is harder for them to understand and appreciate what is going on.
With the original query, one can step through each filter in turn and actually visualise what is happening to the resultset as one goes, moving individuals into and out of the list as each filter is applied. One could even check that each filter is doing what we expect by examing the resultset, before adding the next filter. For the novice, this is perhaps the best way to proceed.
Mike Loney
Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
-
- Superstar
- Posts: 420
- Joined: 18 Nov 2012 01:50
- Family Historian: V6.2
- Location: Mesa, Arizona, USA
Re: Custom Saint Query - Not Working
Thanks everyone for your suggestions, I now have it working correctly. I have attached a screenshot of the sequence of filters (suggested by Mike L.) that did the trick.
I am admittedly a novice at doing queries, this is only the second one that I have ever attempted, quite a useful tool. I now understand what I was doing wrong in the initial attempt.
BTW, my wife's lineage is currently back 50 generations, which took us back to a Roman senator born in circa 390 AD. I just plugged in 60 generations to make sure that I caught everyone, I have lowered to 55 in this revision.
I am admittedly a novice at doing queries, this is only the second one that I have ever attempted, quite a useful tool. I now understand what I was doing wrong in the initial attempt.
BTW, my wife's lineage is currently back 50 generations, which took us back to a Roman senator born in circa 390 AD. I just plugged in 60 generations to make sure that I caught everyone, I have lowered to 55 in this revision.
- Attachments
-
- Custom Saint Query, Rev 1.JPG (33.41 KiB) Viewed 6168 times
Last edited by BobWard on 09 Sep 2016 16:30, edited 1 time in total.
- tatewise
- Megastar
- Posts: 28341
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Re: Custom Saint Query - Not Working
One small piece of advice.
The Operator: contains will match anywhere in the Title, and if you don't tick Match case, will match saint or SAINT or besaints or SAINTLIKE, i.e. it is not necessarily a standalone word.
It would probably be better to use the Operator: begins with and put a space after 'Saint ' so it only matches the word Saint at the beginning of the Title.
Whether you should tick Match case depends on how certain you are that you have spelled Saint with a capital.
The Operator: contains will match anywhere in the Title, and if you don't tick Match case, will match saint or SAINT or besaints or SAINTLIKE, i.e. it is not necessarily a standalone word.
It would probably be better to use the Operator: begins with and put a space after 'Saint ' so it only matches the word Saint at the beginning of the Title.
Whether you should tick Match case depends on how certain you are that you have spelled Saint with a capital.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
-
- Superstar
- Posts: 420
- Joined: 18 Nov 2012 01:50
- Family Historian: V6.2
- Location: Mesa, Arizona, USA
Re: Custom Saint Query - Not Working
Good point, Mike, thanks.