* Creating a Custom Query

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.
Post Reply
User avatar
kfunk_ia
Diamond
Posts: 77
Joined: 03 Dec 2019 22:50
Family Historian: V7
Location: Iowa, United States

Creating a Custom Query

Post by kfunk_ia »

I am likely to be an Immigrant from RM as I think poorly of their latest effort. As such I am working on learning to do the things that I did in RM7. One of those things is a custom query or three and I am having issued because the query system here make no sense to me without AND/OR and the ability to determine if something EXISTS or not.

What I want to do is create a query that will return a list of people (Name, death date, and death place) that died after 1899 and have no Obituary (this is a custom fact that I have). My problem is that I end up with everyone in my database.

What I have done so far is:
Add if %INDI.DEAT[1].DATE% contains text". (note the ". was added somehow by the program)
Exclude if %INDI.DEAT[1].DATE% was earlier than 1900

This seems to get me the people with a death date in 1900 or later. The tricky part is going to be the Obituary bit. I only want to include people who have no Obituary. The Obituary may or may not have a date, place, note or source. There seems to be no way to exclude folks who have an Obituary fact that does not exist. Any suggestions as to how I would accomplish this would be appreciated.
User avatar
ColeValleyGirl
Megastar
Posts: 5465
Joined: 28 Dec 2005 22:02
Family Historian: V7
Location: Cirencester, Gloucestershire
Contact:

Re: Creating a Custom Query

Post by ColeValleyGirl »

It can be tricky understanding how to use the Rows tab initially.

Remembering that you can't easily filter on anything you haven't included in the Columns (it's possible but you have to enter the reference manually rather than selecting it from a list)
  • Add columns for the Individual (%INDI%), their Death Date (%INDI.DEAT.DATE%), and your obituary fact (%INDI.EVEN-OBITUARY% ) where EVEN-OBITUARY is replaced by your custom fact tag) Mark the last of these hidden (you only want to filter on it, not see it). You can add more columns here if you wish -- these are the minimum.
  • Now to the Rows tab. I don't think your first 'Add If' statement is necessary -- the 'Exclude if %INDI.DEAT[1].DATE% was earlier than 1900' should get you the starting set of individuals. Now add 'Exclude unless %INDI.EVEN-OBITUARY% is null', This tests to see if an Obituary fact exists, whether or not it has any details in it.

On the subject of AND and OR you can create compound expressions to filter on (again, manually). that include these.
Last edited by ColeValleyGirl on 28 Jul 2021 08:08, edited 1 time in total.
Reason: Clarified what you can filter on.
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Creating a Custom Query

Post by Mark1834 »

FH Queries are a little idiosyncratic and take a bit of getting used to, particularly if you are used to running queries on a conventional database.

A couple of extra points to give some background on what you posted. The dot (period) is part of what FH calls a Data Reference. It is a way of describing a piece of data starting with the most significant item and working down. So %INDI.DEAT[1].DATE% means the Individual Death Date. Secondly, the number in square brackets is significant. 1 means the first piece of data that matches. If, for example, you weren't sure of the date, and entered two possible dates, the second one would be %INDI.DEAT[2].DATE%. For dated items, I think the numbering is chronological. However, for undated events or things like sources, the order is the (possibly arbitrary) order in the database. If you impose an order, that is generally followed. Don't worry about the details at this stage of your learning curve, but just be aware of them for the future.

Check the FH Help under General Topics>Advanced Topics>Understanding Data References for all the lurid details, but as the help points out, you don't need a detailed understanding to use them - there are plenty of wizards and prompts in the program to guide you through.
Mark Draper
User avatar
tatewise
Megastar
Posts: 28342
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Creating a Custom Query

Post by tatewise »

See the FH Help for Query Window Rows Tab that explains how Add and Exclude effectively work as OR and AND operators.
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: Creating a Custom Query

Post by E Wilcock »

I think one answer is that in fh one doesnt add people with certain characteristics.
I start with everyone and then remove people "unless".

So I can go through removing people who dont have a particular birth place
And then remove further groups according to the dates of birth
User avatar
tatewise
Megastar
Posts: 28342
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Creating a Custom Query

Post by tatewise »

You can initially Add records with certain characteristics. The Help says:
If the first filter is 'Add If' or 'Add Unless' it initially makes the result set empty. If the first filter is 'Exclude If' or 'Exclude Unless' it initially puts all records in the result set.
So if the first Rows tab filter is 'Add ...' then the Result Set is initially emptied and only the records matching the filter criteria are subsequently added.

If the first Rows tab filter is 'Exclude ...' then the Result Set is initially filled and only the records matching the filter criteria are subsequently removed.

It sometimes takes a little ingenuity to get the filters in the most effective order.
Otherwise, the AND and OR operators may need to be employed.
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: Creating a Custom Query

Post by E Wilcock »

But Mike I have never understood that sentence

"If the first filter is 'Add If' or 'Add Unless' it initially makes the result set empty."
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Creating a Custom Query

Post by Mark1834 »

It starts with an empty record set. “Add if” adds only records matching that condition. “Add unless” adds all records that don’t match the condition. It’s not particularly intuitive, but consistent with the way FH Queries are built up.
Mark Draper
User avatar
ColeValleyGirl
Megastar
Posts: 5465
Joined: 28 Dec 2005 22:02
Family Historian: V7
Location: Cirencester, Gloucestershire
Contact:

Re: Creating a Custom Query

Post by ColeValleyGirl »

If the first filter is 'Add If' or 'Add Unless', you start with an empty 'bottle' to which you have added the records selected by that filter.

If the first filter starts with 'Exclude', you start with a full 'bottle' from which you have removed the records selected by that filter.

In both case you can then go on to add and/or remove other records in the bottle.
User avatar
kfunk_ia
Diamond
Posts: 77
Joined: 03 Dec 2019 22:50
Family Historian: V7
Location: Iowa, United States

Re: Creating a Custom Query

Post by kfunk_ia »

For reference, I have read through the items in Help as well as the KB articles. I also have the 'Getting the Most From' book where I scanned Chapter 17 and read Chapter 18. I am a computer programmer by training although it has been a few years since I last worked in the field and I am finding customer queries to really be harder than the need to be. I do get the concept of the internal list and the actual output. I don't recall specifically what the book called them.

In my world, the internal list should start empty and Add If and Add Unless, should initially populate that internal list based on the criteria from the Adds. My world apparently clashes with FH's world in this regard.

WHere I got to last night after posting was the following:
Add if =Exists(%INDI.DEAT.DATE%) is true
Exclude if %INDI.DEAT.DATE% was earlier than ["DeathDate"] (yes, I am trying to use a param here to delete records from before my specified date)

This seems to do what I want it to do. I get about 11,000 record out of the 40,000+ in the database. All appear to have a death date later than what I specify for the param. what I can't seem to make happen is to exclude the records that already have a Obit fact.

If I add (what seems reasonable to me) 'Exclude if =Exists(%INDI.EVEN-OBIT%)', this seems to do nothing. Yes, my custom fact is Obit, not Obituary. So I am not certain how to make that happen. I also can't seem to make other expressions go away one I add them. I have tried many things to see if they work, and when they didn't I wanted to delete them. The best I can seem to do is disable them.

I have also actually tried using compound statements in the Expression line but I can't seem to add that expression without an Operator and possible a Value.
User avatar
tatewise
Megastar
Posts: 28342
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Creating a Custom Query

Post by tatewise »

You say "In my world, the internal list should start empty and Add If and Add Unless, should initially populate that internal list based on the criteria from the Adds. My world apparently clashes with FH's world in this regard."
The FH world and your world are identical in this regard when the first field is an Add. Why do think they clash?

To delete a Row filter, select it in the top pane, and click the X Delete button in lower left corner.

BTW: Filters Add unless %INDI.DEAT.DATE% is null and Exclude if %INDI.DEAT.DATE% is null will both work just as well as Add if =Exists(%INDI.DEAT.DATE%) is true.

Helen explained how to exclude those who have an Obituary by using Exclude unless %INDI.EVEN-OBIT% is null.
However, Exclude if =Exists(%INDI.EVEN-OBIT%) is true should work just the same.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
kfunk_ia
Diamond
Posts: 77
Joined: 03 Dec 2019 22:50
Family Historian: V7
Location: Iowa, United States

Re: Creating a Custom Query

Post by kfunk_ia »

Mike, I appreciate your help, however I am still getting persons with the Obit fact in my set. I have the following:

Add unless %INDI.DEAT.DATE% is null
Exclude if %INDI.DEAT.DATE% was earlier than ["DeathDate"]
Exclude unless %INDI.EVEN-OBIT% is null

I made the change that Helen mentioned on the Obit line, however persons with Obit are still showing in the results. The first two lines seem to work just fine. I have even tried moving the Obit line to the second position. I have to check for the Obit fact because some Obit facts do not have either dates or places, So drilling down and checking them will still miss records.
User avatar
LornaCraig
Megastar
Posts: 3190
Joined: 11 Jan 2005 17:36
Family Historian: V7
Location: Oxfordshire, UK

Re: Creating a Custom Query

Post by LornaCraig »

When you say " I am still getting persons with the Obit fact in my set" do you mean everyone (with an appropriate death date) who has the Obit fact is listed in the Results set? Or just some people? If some, but not all, are being excluded is it possible that you have inadvertently created two slightly different Obit facts?
Lorna
User avatar
kfunk_ia
Diamond
Posts: 77
Joined: 03 Dec 2019 22:50
Family Historian: V7
Location: Iowa, United States

Re: Creating a Custom Query

Post by kfunk_ia »

Lorna -

I am getting persons with an Obit fact mixed in with the persons that I would expect the query to produce, those born 1900 and later that do not have Obits. I did double check a number of the persons that show with Obits, and no, there is only 1 copy. I even checked to see if those persons may have had two Death facts, but none did.
User avatar
tatewise
Megastar
Posts: 28342
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Creating a Custom Query

Post by tatewise »

Yes, perhaps you have an Obituary Attribute too or some other variant of Obituary.
To get %INDI.EVEN-OBIT% it would have to have been originally entered as just Obit and then labelled Obituary.
Is this a Project imported from another product (RM) that needs its facts reviewed?
What is listed in Tools > Fact Types with Show Hidden ticked for any facts starting with Obit?

BTW: The order of the two Exclude filters is not important. They effectively AND together.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
LornaCraig
Megastar
Posts: 3190
Joined: 11 Jan 2005 17:36
Family Historian: V7
Location: Oxfordshire, UK

Re: Creating a Custom Query

Post by LornaCraig »

I did double check a number of the persons that show with Obits, and no, there is only 1 copy
I didn't mean that a person might have two obituary facts, but that you might have created two slightly different obituary fact types, and the query is only testing for one of them.

See Mike's reply where he explains using Tools > Fact Types with Show Hidden ticked to reveal all facts starting with 'obit'
Lorna
User avatar
kfunk_ia
Diamond
Posts: 77
Joined: 03 Dec 2019 22:50
Family Historian: V7
Location: Iowa, United States

Re: Creating a Custom Query

Post by kfunk_ia »

EDIT: Good news, I finally figured it out. I just excluded the Obit attribute also. Now, the Attribute/Event situation is another question that I will post later.


Lorna and Mike -

I apparently have an Obit event and an Obit Attribute.

My Obit came along from RM7 where it was created. There was only one Event called Obit in RM 7. SO I am assuming that I need to somehow merge or delete the Event and Attribute?

It appears that all of my custom Events from RM7 also have a matching Attribute.

BTW, this database is for practice and is disposable so if there is something I need to look at in regards to RM7 to make this change, I can do that also.
Last edited by kfunk_ia on 28 Jul 2021 18:40, edited 1 time in total.
User avatar
tatewise
Megastar
Posts: 28342
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Creating a Custom Query

Post by tatewise »

That is a common scenario.
When a fact such as Obituary is imported into FH it is dealt with depending on whether it has a descriptive value or not.
If it has a value then it becomes an Attribute, but if not then it becomes an Event.

I assume you will want to convert the Obituary Events to become Obituary Attributes.
It is best to use Tools > Fact Types to add a definition for the Obituary Attribute and leave the Event undefined.

There is the Change Any Fact Tag plugin that can convert the Events to Attributes.
Then in the plugin, it is easy to select the <Custom Event> Obituary to be changed into the Custom Obituary Attribute.

They will then have the Dat Ref %INDI._ATTR-OBIT%
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
kfunk_ia
Diamond
Posts: 77
Joined: 03 Dec 2019 22:50
Family Historian: V7
Location: Iowa, United States

Re: Creating a Custom Query

Post by kfunk_ia »

Mike - So my custom facts all use the date field, the place field and the description field in RM7. If I had not used the description field, then I would not have gotten Attributes with the same name, correct?
User avatar
tatewise
Megastar
Posts: 28342
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Creating a Custom Query

Post by tatewise »

The date and the place fields are irrelevant as far as deciding between Events and Attributes.
Yes, any custom Obituary facts without a description value will import as a custom Event in FH.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
Post Reply