* writing queries

[E Wilcock]

I really must learn how to write queries. But as yet, even tho I have the book. I havent.

I want to compare Emigration dates of parents and their children.
The destination Place is not a true Place field - it is part of a Note to the Event, so I reckon the first step is to use the fh text search button to find anyone who emigrated to New York and add them to a named list.

Or can a query search for text in the note for an event field?

I then want to find any children of that individual who emigrated anywhere at all. But when Jane very kindly wrote me a query to find people with grandchildren each successive child had to be listed by number in the query.

That suggests a better way of doing it would be to start with the children. Look for any person who emigrated anywhere and then exclude those who did not have a parent who emigrated to New York.
If the results list had columns for parents that would give the useful list. But only if the word New York can be found.

[tatewise]

Evelyn, you haven't chosen the easiest of Queries to start with, but let use it as a tutorial.
Also, don't overlook how_to:creating_a_query|> Creating a Query and its sub-sections.

You ask "Can a query search for text in the note for an event field?"
Yes it can ~ that is what the Rows tab filter is all about.
Although in some cases, having a Named List can also be useful filter.

Query Type
The first step is to choose the Type of Query best suited to the task.
Most people instinctively start with an Individual Query that focusses on Individual Records.
But sometimes another Type is better, such as a Family Query, a Source Query, or a Media Query.
However, in this case a Fact Query is probably best, since this task focusses on Events.
Most types of Query focus on records of the same type, but a Fact Query is an anomaly in focussing on the Fact subsidiary components of both Individual and Family records.

When you use the cog Query Menu > New Custom Query you must choose a Query Name and the Query Type.

General Tab
This is often overlooked, but offers a Description of the task and Print details settings.

Columns Tab
This determines what Columns (not Rows) are displayed in the final Result Set.
In this case we probably want the Child's Individual Record, their Rec Id, and the Date of the Migration Event.
We will also want similar details for both the Father and Mother.

So the Columns settings for Heading and Expression could be as follows:

Fact =FactLabel(%FACT%) i.e. the name label of the Fact e.g. Emigration

Then for the child:
Child =GetRecord(%FACT%) i.e. the Individual record that owns the fact
Rec Id =RecordId(GetRecord(%FACT%)) i.e the Record Id of that record
Date %FACT.DATE% i.e. the Date of the fact

Then for the father:
Father =Field(GetRecord(%FACT%),'INDI.FAMC>HUSB>') i.e. the child's family as child ~ husband's Individual record
Rec Id =RecordId(Field(GetRecord(%FACT%),'INDI.FAMC>HUSB>')) i.e. his Record Id
Date =FieldText(GetRecord(%FACT%),'INDI.FAMC>HUSB>EMIG.DATE') i.e. his Emigration Date

Then for the mother:
Mother =Field(GetRecord(%FACT%),'INDI.FAMC>WIFE>') i.e. the child's family as child ~ wife's Individual record
Rec Id =RecordId(Field(GetRecord(%FACT%),'INDI.FAMC>WIFE>')) i.e. her Record Id
Date =FieldText(GetRecord(%FACT%),'INDI.FAMC>WIFE>EMIG.DATE') i.e. her Emigration Date

Rows Tab
This filters the data so only the Rows of interest are displayed in the final Result Set.
In this case we only want Emigration facts, and only if the Father or Mother has a suitable Emigration fact too.
After a little experimentation it turns out to be best to filter the Father and Mother details first.
So the Rows settings for Condition, Expression, Operator and Value are as follows:

Add if =FieldText(GetRecord(%FACT%),'INDI.FAMC>HUSB>EMIG.NOTE2') contains New York
i.e. similar to the Father's Emigration Date column above, but for the Note field instead.
It says Add the Child details to Result Set if the Father's Emigration Note contains New York.

Add if =FieldText(GetRecord(%FACT%),'INDI.FAMC>WIFE>EMIG.NOTE2') contains New York
i.e. as above for the Mother

Exclude unless =FactLabel(%FACT%) matches Emigration
i.e. Exclude all Child details except for Emigration facts.

Further refinement is possible to allow you to choose any destination rather than just New York.
If you have used a labelled Note to hold the destination, there is a more reliable filter expression.
The above expressions assume each parent only has one Emigration event, but that can also be relaxed.

[davidm_uk]

Wasn't sure whether to ask this here or in a new post - happy if you want to move it.

I have a Fact query that shows where I have facts with no sources (Unsourced Facts query downloaded from KB):

Fact Query.JPG (51.9 KiB) Viewed 7955 times

I want to add a column which shows the relationship of the individual (or individuals for a family fact if this is possible, if not just the first named individual in the family fact) to the Root. I've added the RecId=RecordId(GetRecord(%FACT%)) entry but can't work out how to then use the Relationship function.

[DavidNewton]

I used the FactOwner shortcut function to extract the individual or pair of individuals and then the relationship function so getting four columns

TAG="Owner","=FactOwner(%FACT%,1,MALES_FIRST)"
TAG="Owner relation","=Relationship(FactOwner(%FACT%,1,MALES_FIRST),FileRoot(),TEXT,1)"
TAG="CoOwner","=FactOwner(%FACT%,1,FEMALES_FIRST)"
TAG="CoOwner relation","=Relationship(FactOwner(%FACT%,1,FEMALES_FIRST),FileRoot(),TEXT,1)"

the text is taken directly from the appropriate .fhq file. If it is an individual fact then the last two columns copy the first two.

David
Added in Edit. The relationships here are describing the file root's relationship to the individuals (e.g, grandson) to get the relationship the other way round then move FileRoot() to be the first parameter (e.g grandmother)

[E Wilcock]

I will reply just to Mike.
To thank you. So very much.
First, I have not chosen a hard query to start with. Thanks to you and Jane writing queries for me in the past, I read what you sent me and have managed simple queries of my own.

I asked about this as it was too hard. And I need to go slowly thro the tutorial you have posted which I have saved as a Word document.

You are right, I was looking for a list of people so it had not occurred to me to use a fact query - So I learned something at once.
I did study both the book and the existing query downloads including the list of ones for facts. I apologise but none of the titles suggested the result might be some individual records.

The lines instructions - reading them on the page, I dont really understand but if I go off and do it , may be I will.

I am very happy indeed that fh Queries can pick out Words from the Note fields.

I am a great collector of data for which fh is the easiest data entry I have ever done. I am less good at extracting the information, so I need to learn this. Thank you.

[E Wilcock]

Sorry Mike, I am already stuck
You wrote to establish the columns
"Fact =FactLabel(%FACT%) i.e. the name label of the Fact e.g. Emigration"

Do I need to edit this to put in the label EMIG instead of FACT? As the software doesnt seem to want me to do that. Or will the choice of the Fact in the lines for the query, determine that label in the report?

[E Wilcock]

Mike - I think I am getting the hang of arranging the columns but when I want to use a custom tag copied from Emigration - the program is refusing the abbreviation DEPO (Deportation.)
e.g. in this line.
Date =FieldText(GetRecord(%FACT%),'INDI.FAMC>HUSB>EMIG.DATE') is accepted

Date =FieldText(GetRecord(%FACT%),'INDI.FAMC>HUSB>DEPO.DATE') is not. I am not sure why. It is possible that I created deportation n TMG but I am working on a Project started from scratch in fh.

[Jane]

DEPO is not a valid tag, find your custom deportation fact in the list when working on the column tag for an individual query and add it as a column so you can see what the tag should be.

[E Wilcock]

Jane - Thank you. Very neat.
And thank you Mike - I have run the query and have a result - tho the result set is only two people. I created a false record to test it. And it was right. I got a further entry.
Perfect. Because this is something I want to look at - and it makes sense now to enter more dates and facts.
And I do understand queries a lot better. Tho I fear that you really did the work for me again Mike. I can tweak this to cover other Events.

There are details of the syntax I dont understand

=FieldText(GetRecord(%FACT%),'INDI.FAMC>HUSB>EVEN-DEPORTATION.NOTE2')

Why is it NOTE2?

[davidm_uk]

@DavidNewton

Many thanks for that, got it working now. Another bucket load of work to do to find why no sources for those facts, a problem I didn't know was that extensive....grrrrrrrr

[tatewise]

Why NOTE2?
There are two forms of NOTE, i.e. Local Note and link to Note record.

In GEDCOM they both use the NOTE tag and are differentiated only by their value being a text string or a link in the format @N123@. Also, to avoid any possible misinterpretation, any @ character in the text string should be represented by @@.

But in FH Data References the two forms are denoted by the tags NOTE2 and NOTE respectively, to allow better syntax checks. There are similar tags for SOUR2 and SOUR and for OBJE2 and OBJE.

BTW: Sorry Evelyn, I assumed this was your first attempt at writing a Query, because you said:

I really must learn how to write queries. But as yet, even tho I have the book. I havent.

The book has several chapters on the subject, so please try its tutorials.

[E Wilcock]

Mike - As you know I studied the two chapters in the book. I will make time tomorrow to work through the tutorials.

But there are two points to bear in mind -That although the example of finding a person with parents with certain names would have found for me the children of people who emigrated, it didnt (I dont think) show one how to search for text in the records and the successful method you eventually chose for me was a Fact query.

The other thing is I that I will forget what I learn. The reason I use computers is that I forget. On another thread you rebuke me for forgetting an abreviation used in fh - I probably never used it (I have never had time to properly tidy any of my TMG imports). Or failed to understand it at the time.

I have as yet not exactly understood the sentences with symbols % and brackets you gave me for either columns and lines. But I am writing to a deadline. And will get back to you when I have done the tutorials.

[tatewise]

Evelyn, as you have said before, you need to keep notes about what you learn that are meaningful to you.

In the Book in Chapter 14/18. Writing Custom Queries it summarises all the Query Types on the first page.

There are nine/ten types of query – one for each record type ('Individual', 'Family', 'Source', etc) and one labelled 'Fact (event or attribute)'. ...etc... A Fact query produces a result set consisting of fields within records – the fields which store event or attribute data. So Fact queries are a little different from all other queries in that they alone do not return records.

In the FH V5 Book Chapter 14 under The Born After 1900 Sample Query on Page 162 it explains how a Rows filter can test the INDI.BIRT.DATE field, and by analogy any other field within a fact. I am not sure if that is also in the FH V6 Book Chapter 18.

(BTW: I am sorry you felt rebuked.)