* Tutorial on Queries

[E Wilcock]

Just to let you know Mike (if the computer wizardry doesnt) that I have read this and much appreciate the work you have put in and the explanations.
May be a few days before I can do this and report back. And dont want you to think I am ungrateful.

[E Wilcock]

=GetField( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE%) . "]" )

=GetField( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE%) . "].ADDR" )

I am almost frightened to ask this and it may be listed in the help somewhere but I have wanted to ask ever since the original thread.
viewtopic.php?f=32&t=15673&p=83337&hili ... nts#p83337

I feel that I and may be other newish or less computer minded users could be less daunted if we understood the codes and symbols we are looking at.
So I wanted to ask why these were used and when.

It may be wise if you choose another simpler and better example for your explanation as I have just quoted the above from the latest post.
The questions that came to me in the original thread when I was at first floundering, were:

Why are there round brackets in some places and square brackets in others?
What are the % signs for?
When do you use inverted commas?
What is the tilda for (that ~ isnt on my bi lingual Russian English keyboard, I have to use the character map)
and the arrows < and >
Why is there sometimes a full stop and sometimes a comma?

Thanks to you Mike, and your building it up step by step in this tutorial, I do now understand both these sentences. In fact every step you have explained.

But I dont understand why it is written as it is. And when other users post within the thread they post lines with symbols, so obviously they understand and I am in the dark,often not understanding whatI read and so not bothering to read it properly.

Again there is no hurry on this.

[tatewise]

Many of your questions are answered in how_to:understanding_expressions|> Understanding Expressions and its links to the FH Help and to Data References and Functions.

Why are there round brackets in some places and square brackets in others?
Round brackets enclose parameters of a Function such as GetRecord(%FACT%) or Year(%FACT.DATE%).
Even if a Function has no parameters it must still have round brackets such as FileRoot().
Square brackets identify an instance of a Data Reference such as INDI.NAME[2] or INDI.CENS[year=1900].
If the square brackets are omitted the Data Reference refers to 1st instance, so INDI.OCCU is INDI.OCCU[1].

What are the % signs for?
% signs enclose a Data Reference such as %INDI% or %FACT.DATE% or %SOUR.AUTH%.

When do you use inverted commas?
Most literal text strings are enclosed in double quotes such as "This is literal text".
Whereas the Field() and FieldText() functions need data reference constants enclosed by inverted commas.
e.g. Field( %INDI%, 'INDI.DEAT.DATE' )

What is the tilda for?
Tilde ~ identifies a relative or shortcut Data Reference such as INDI.~FATH = INDI.FAMC>HUSB.
Often they use non-GEDCOM tags such as ~FATH, ~MOTH, ~SPOU, etc.

What are arrows < and > for?
The meaning of < and > characters depend on their context :-

In Data References the > identifies a link pointing to another record such as INDI.SOUR>.
Whereas a dot . identifies a sub-field within current record such as INDI.NAME.NICK.

In comparisons they mean less than or greater than and = means equal to, etc.
See the FH Help > Using Family Historian > Advanced Topics > Understanding Functions > Operators.

In Sentence Templates they make their contents conditional on a Data Reference existence.
e.g. < at %FACT.ADDR%> displays nothing if there is no Address, otherwise it adds the at prefix.

Why is there sometimes a full stop and sometimes a comma?
Full stop or dot . was explained above in the context of a Data Reference such as INDI.NAME.
When a Function has multiple parameters they are separated by a comma such as GetField( %INDI%, "INDI.NAME" )

Why are functions sometimes prefixed with equals = and not others?
Any outermost Function in an expression must have an equals = prefix such as =Field( %INDI%, 'INDI.NAME' )
But any Function as a parameter inside ( ) does not such as =Field( GetRecord(%FACT%), 'INDI.NAME' )

[E Wilcock]

This Query is getting a bit more complex, mainly because of multiple Census Events.

The Columns tab needs Expressions to list both parents, probably as you had before.
Father is =Field(GetRecord(%FACT%),'INDI.~FATH>')
Mother is =Field(GetRecord(%FACT%),'INDI.~MOTH>')

The Rows tab filters get quite complex.
First they must add the parents if they are recorded in the same Census as the child.
Now previously you used an expression to obtain the father's EMIGration event Note:
=FieldText( GetRecord(%FACT%), 'INDI.FAMC>HUSB>EMIG.NOTE2' )
and in this thread we learnt that INDI.FAMC>HUSB> equals INDI.~FATH>.

So to obtain a CENSus event we could use:
=Field( GetRecord(%FACT%), 'INDI.~FATH>CENS' )
but that would get the 1st CENSus from the father's list of facts, and might not be the one that matches with the child.

So we need to use the special [year=yyyy] index to select the correct CENSus instance.
That needs the =GetField(...) function that allows its Data Reference to be composed from various parts.
The year index part involves literal text [year= and the year itself Year(%FACT.DATE%) and the end of index ]
=GetField( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE%) . "]" )
Notice how the Year from the %FACT.DATE% is used to select the Father's CENSus.

Thus the Father's CENSus ADDRess is extracted by using:
=GetField( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE%) . "].ADDR" )

So the Rows filter to add the Father's matching Census is:
Add if
=IsTrue( %FACT.ADDR% = GetFieldText( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE) . "].ADDR" ) )
is true
and similarly for the Mother:
Add if
=IsTrue( %FACT.ADDR% = GetFieldText( GetRecord(%FACT%), "INDI.~MOTH>CENS[year=" . Year(%FACT.DATE%) . "].ADDR" ) )
is true

Then exclude all but Census facts:
Exclude unless
=FactLabel(%FACT%)
matches
Census

Finally, exclude young children living with parents:
Exclude if
=AgeAt( GetRecord(%FACT%), %FACT.DATE% )
is less than
15

Mike, I am sorry to tell you that I have failed on this. First because you have lost me, and my not understanding means I dont know why I am failing. And just possibly because there is a typo in the filter you devised.

I have copied your post here, so as to have it on the same Forum page. Can we recap please?
First that this is a Fact query based on the one we went through for emigration?

Secondly that the lines in this query consist of just four lines - 1. Father, 2. Mother, 3. selecting the fact 4.excluding children under 15.

Third, I dont understand where in the parents' sentences I need to insert the chosen census year, or whether it is handled in the parameter box?.
Add if
=IsTrue( %FACT.ADDR% = GetFieldText( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE) . "].ADDR" ) )
is true

If this is the case, then the fault may be in the syntax of the filter which I know is very complex. When I paste it i,n the choices below are greyed out and it cannot be added to the box above because that button is also greyed out.

Thinking of a possibly typo, there appears to be an unpaired bracket )
But more important, I dont know what to do with the final Is true as the choices are greyed out.
Removing one of the two brackets does not sort it.
Neither does ticking the parameter box for the date as when one tries to run the query, the options offered do not include Date.

I am really sorry to be such a slow pupil. But I am out of my depth.

[DavidNewton]

Just a bit of proof reading, no more,

Add if
=IsTrue( %FACT.ADDR% = GetFieldText( GetRecord(%FACT%), "INDI.~FATH>CENS[year=" . Year(%FACT.DATE) . "].ADDR" ) )
is true

There is a missing % .....Year(%FACT.DATE%)...

David

[tatewise]

Yes, David is correct, my typo, sorry

In my defence, m'lord, the Year(%FACT.DATE%) function is shown correctly everywhere else four times, even on the "and similarly for the Mother:" filter below.

Evelyn, by now you should be able to spot such syntax errors.
Every Data Reference that starts with % must end with %.
e.g. %FACT.ADDR%, %FACT%, %FACT.DATE%

Finally, there are no unmatched ( parentheses ).
Count them. There are four ( and four ) altogether.

[E Wilcock]

Well in my defence, I tried the mother line. On its own. Will have another shot now.

[E Wilcock]

That works - tho I limited it to the mothers living with their adult children.
The problem is in the results set.
I guess this needs me to try and convert it to an individual enquiry, so it lists each old lady only once. Which I need because this is a numerical count.

As it is, the name of each mother appears multiple times along side each of her multiple children.

If I select the names and try and put them in a named list I seem to get every name.
And when I changed the column content, to leave only the names of grandmothers, I must have done something wrong because there is no person there at all. I will have another go at this at some point.

it looks to me as if fh is capable of finding out some very interesting things abut family units which one cant find (or someone with my limited capability cannot find ) simply by filtering database data. But it is no good reprimanding me because I have noticed something wrong or failed to notice something else.

This is not my area of expertise - and I had been frank with you that I did not know what I was doing.

[DavidNewton]

I have been following this thread with interest and I have finally tried the query on my file and there were way too many results and the reason is that large numbers of my census returns were taken in small villages where the individual houses did not record any form of address other than possibly 'Private House'. On many census returns the address given is simply the name of the village. Even adding in a check on the place as well as the address will still bring up numerous families living in the same village but not in the same households.

A possible alternative method, assuming that all the census records have a method 1 source, would be to use the source record instead of an address to identify people in the same household.

David

[DavidNewton]

Sorry Mike, I see the alternative of using the source was in fact your first thought.

David

[tatewise]

@David ~ Yes, if you have reliable Method 1 Source Citations for each Census household then use:

=IsSameItem(%FACT.SOUR>%,GetField(GetRecord(%FACT%),"INDI.~FATH>CENS[year=" . Year(%FACT.DATE%) . "].SOUR>"))

This was why I asked Evelyn about Source Citations, but she was not certain they were reliable, so we used ADDR.

@Evelyn ~ If you carefully select just the Mother's Names and use cog Menu > Add Selected Cell Records to Named List then each mother will only be added once, and gives the count you want. To make that selection easier, click on column heading to sort Mothers together.

If you change every INDI.~FATH> to INDI.~FATH>~MOTH and every INDI.~MOTH> to INDI.~MOTH>~MOTH in both Columns and Rows tabs then that should cater for both grandmothers.

[E Wilcock]

Thank you Mike - I hadnt worked that out. I tried to select just that column. It didnt work and will have another go now. But I will have to rewrie the query column first, as I forgot that all projects share the same query files and I managed to delete them.

One needs to do what I do with word documents and always save queries with a new number before editing.

I really like what David is thinking about too.

As far as my sources go. I arrived from TMG with my most recent census sources lumped. i.e. 1911 Census General Source and I put the file and page reference into the citation. By "citation" I mean what I am adding for a particular household record. I have done the same for the 1939 Register.

However where I am studying emigration using fh Projects what ties a household together is the Place field (I dont use Address) and that Place record is copied and pasted into the Text from source box in the Citation for that household.
I began to paste the address as text from source when I read somewhere, perhaps in the fh knowledge base, that the way to find and view again households (e.g. hotels and nursing homes) with lots of different unrelated people was to do a text search for the street address.

In addition to having Text from source, I may also put a page or film image number into the Citation Where in source field. But some households in censuses spread onto the next page I think, so that is unreliable when it comes to having a uniform entry for the whole household.

[tatewise]

Whooooa!
Evelyn, you just said "what ties a household together is the Place field (I dont use Address)".
Whereas, before you said it was Address that tied them together.
So those Rows tab filter involving ADDR are WRONG and should use PLAC instead.

BTW:
For Census Households over two pages, I always mention both Pages where appropriate for every member.
However, since I use Method 1 Source Citations that usually appears once in the Source record Publication Details, and has both page images attached. That seems to be yet another advantage of Method 1.

[E Wilcock]

Sorry - Problem with terminology. They share a street and house number (I think of this as address). Everyone in that project is in the same town, because it is a place study.
I have not rewritten anything for fh yet - Sorry. But today busy writing up something else.

[E Wilcock]

I deleted all previous versions of this Query, and rewrote it by saving and editing the Query that allowed me to compare emmigraton dates of parents and their children.
The intention of this query is to identify grandparents listed in a census in the household of an adult child. I changed the stipulated age of the child to 21 which was the age of majority when the census was taken.
I selected and added the grandmothers to a named list using the cogwheel, as you explained Mike. (and not the edit menu)

This looks fine at first sight. It found that in a total of more than 300 families, 29 Grandmothers were listed in the household. I had a look checking them and it looked good.
But I dont trust these figures as there is a problem with the Fathers. Too many of them I think and the list included some men whose first names are not known, in other words they cant be census entries. As census entries have the forenames. I enter husbands with unknown forenames only to put in the women's married names.

I believe it is showing all the fathers of the children shown. Have I written the row expressions wrong?

I copied the query, and saved it as a new one limited to grandmothers, deleted the row line about fathers and ran it again. But the number of women came down to 25. And the surplus fathers remained.
My lines at the moment are
Add if =IsTrue(%FACT.PLAC% = GetFieldText(GetRecord(%FACT%),"INDI.~FATH>CENS[year=" . Year(%FACT.DATE%) . "].PLAC")) is true
Add if =IsTrue(%FACT.PLAC% = GetFieldText(GetRecord(%FACT%),"INDI.~MOTH>CENS[year=" . Year(%FACT.DATE%) . "].PLAC")) is true
Exclude unless =FactLabel(%FACT%) matches Census
Exclude if =AgeAt(GetRecord(%FACT%),%FACT.DATE%) is less than 21

I am sorry I have still not got this right but I did get up early and give it my full attention.

[tatewise]

Those Rows tab filters are working on ~FATHer and ~MOTHer of the child, as you requested at the very beginning.

As I said yesterday, you must change every INDI.~FATH> to INDI.~FATH>~MOTH and every INDI.~MOTH> to INDI.~MOTH>~MOTH in both Columns and Rows tabs then that should cater for both paternal and maternal grandmothers.

If you seeing men where you expect women in the Results Set then check those Individual records have their Sex field set correctly, which is what determines whether FH thinks they are male or female, regardless of their names.

[E Wilcock]

I have done that to both columns and rows.
My problem was that I was still using the other formulation for the Parents, as that was what we started with.

It looks as tho it works but I will need to do a bit of checking through some other projects as the result isnt what I expected. Clearly I also need to study these alterations to sort out what they are doing.

[E Wilcock]

Mike I was about to post here desperate for your help.
As I wanted to add a woman's spouse or married surname in a column to the fact queries you kindly helped me write
But passenger and emigration and detention lists only give married names - so I really needed the women listed with their married names, or their husband's names .
That meant editing the columns. And I hadnt been able to do this till today.
But while writing this I went back to the previous thread which I keep safely on my computer and will paste in the line needed - just in case anyone else is looking for it.

=Field(GetRecord(%FACT%),'INDI.FAMS>HUSB>')

I think you may possibly have told me this already, but cant find the thread. I am so sorry.

I am good at adapting your queries as far as filter lines are concerned. i.e I have adapted one to search for a fact by day and month as well as year.When adding a more specific date to a query I used the concise option and am a but mystified why it lets me write in e.g. 5 Jan 1943 and changes it at once on screen to 5 January. But wont let me write in 5 January myself.

I had the greatest difficulty (still) retrieving this thread as my search terms - custom fact query etc were rejected as too frequent until I put in the word tutorial. I believe i also marked this thread as one I personally wanted to be able to find again, but that too I failed to find.

[tatewise]

Let me answer those points in reverse order.

Assuming you Bookmarked this topic, then click on your E Wilcock username top right and choose View Bookmarks.

Have you tried the new Home page Search top right?

I don't understand you Query filter Date problem, so please give more explicit details.
By "the concise option" do you mean the :COMPACT qualifier? e.g. %FACT.DATE:COMPACT%
It is not clear where you are entering the date.
Is it directly into the Value field at the bottom of the Rows tab, or in a Parameter prompt when running the Query?
I am not able to reproduce your symptoms.

The expression to obtain the 1st husband's record is as you state:
=Field(GetRecord(%FACT%),'INDI.FAMS>HUSB>')
To adapt that to retrieve his surname you need:
=Field(GetRecord(%FACT%),'INDI.FAMS>HUSB>NAME:SURNAME')
But what if she was married more than once?
In that case you will also need an entry for each possible husband:
=Field(GetRecord(%FACT%),'INDI.FAMS[2]>HUSB>NAME:SURNAME')
=Field(GetRecord(%FACT%),'INDI.FAMS[3]>HUSB>NAME:SURNAME')
and so on until sufficient multiple partnerships are included.

But which Surname applies at the time of the passenger/emigration/detention list is much more difficult to determine, especially if there is no Family Marriage Event with a Date, so I have ignored that for the moment.

[E Wilcock]

Let me answer those points in reverse order.

Assuming you Bookmarked this topic, then click on your E Wilcock username top right and choose View Bookmarks.

I thought I had but it turns out I used the cogwheel instead of the spanner. Icons are not always best for forgetful old people like me.

Have you tried the new Home page Search top right?

Again I got it wrong and searched in the wrong place.

For the rest of your answer thank you. Very helpful. I may get back to the simple date and date choices later.

But I am once again seriously stuck. And again it is these multiple occurrence of some events.

I can now produce a query result for everyone who emigrated on a particular date (Fact and specific date query) and it matches the passenger list of people who went to a place e.g. Canada on that particular day.
But I then want to exclude the people who remained in Canada and select only those for whom a second occurrence of immigration took them to USA.

For the second instance of emigration the place where they started was Canada, so I wrote a line to exclude people unless the place was Canada. I have no trouble in finding those.

But if I add the Canada line to the first query, I get an empty result. It must only be looking at the first occurrence of the Fact?

I would like to use such a query for several other Facts e.g. to find people who were in two different detention centres.

I tried putting 1 and 2 in the Fact Label but that doesnt work as the Fact label doesnt contain the numeral.

Can you help me with this please? If it isnt possible , that is fine.

[tatewise]

Evelyn, you did not get the search wrong.
The new Home page Search was added by Jane recently as an experiment.
See New search for the Forums (15238) where you can provide feedback comments.
If it continues to be better than the earlier Search features, then I suspect it will be made more prominent.

I believe you are using a Fact type Query, so as we have discussed before, each Result Set row is associated with one Fact and it is not currently possible to determine which instance number applies to each Fact.

So you cannot be sure that the Emigration facts found by your Fact & Date Query are 1st instance Emigration facts.

Remember the Rows tab filter is looking at one Fact at a time.
So I guess you have a filter that excludes Emigration facts unless the To Place matches Canada.
You added another filter that excludes Emigration facts unless the From Place matches Canada.
So the only facts allowed through are those whose From Place and To Place matches Canada, which will be none.

It is those more complex combinational filter conditions that are more suited to a Plugin because it does not have the constraints imposed by Query rows.

However, in your specific case you could could get close to your required list with the following Rows tab filters.
Exclude Emigration facts unless the From Place matches Canada.
Exclude Emigration facts unless the To Place matches USA.
Exclude Emigration facts unless the Date was later than the migration Date of your earlier Query.
So that will list everyone who emigrated from Canada to USA after that specific Date.

To test two instances of Emigration facts in a Fact type Query is tricky.
Let us assume the Emigration to Canada facts you are already finding are the 1st instance.
So the filter needs to look at the Emigration 2nd instance Place field which is accessed using:
=FieldText(GetRecord(%FACT%),'INDI.EMIG[2].PLAC')
Thus exclude unless =FieldText(GetRecord(%FACT%),'INDI.EMIG[2].PLAC') matches Canada.
Also exclude unless =FieldText(GetRecord(%FACT%),'INDI.EMIG[2]._PLAC') matches USA.
That should list the people who emigrated to Canada on the chosen Date and later emigrated to USA but only where those are their first two Emigration facts.

[E Wilcock]

Sorry Mike I should have said that I was using emigration as an example only.
I realised the second place field, place to might be a problem but the Fact I am actually using is derived from emigration and therefore (as we discovered ) only has one place, the place from.
The place to is entered in the note.
I think what you have given me above may enable me to do as I want.
I will fiddle around a bit.

[E Wilcock]

The bog standard queries you made for me were all Fact queries.
My original successful Fact query gets the people by the date of the Fact.

But since the result set is of Facts, there wont be any events without that date.
I am getting empty results.

So I will try with an individual query. Dont post anything more till I have experimented a bit. As you have already provided the info I need.

[tatewise]

OK, the same concepts should work for any facts and fields.
Instead of Emigration and EMIG substitute whatever fact Label and Tag is required.
Instead of PLAC and _PLAC fields substitute whatever Tag is required, e.g. NOTE2 for the Note field.

My first suggestion for filters are INSTEAD of your current filters not ADDITIONAL.
If you add them to your current Query filters then I am not surprised the Result Set is empty.

The alternative of an Individual Query was going to be another suggestion if the Fact Query method did not work.
But even that is not straightforward.

[E Wilcock]

Mike I have done it. Using an Individual query to select those who went on a particular date (lots of them) and then excluding anyone who didnt have a second instance of the event. That picked out the three or four people I was looking for.

Thanks only to your showing me how to look for people with a second instance of an event. It worked even with a custom event.

I have saved the query but it is really a one off. If one is using a database to filter, one is used to having to re enter the search terms, and I think I have been too cowed by the idea that in fh one needs to write a new query every time.

Or because your basic examples for me were fact queries, I have neglected individual queries which I guess most people using fh use all the time. I think in the last two weeks I have at last understood the data references, and how to get the information out of the data I entered.

(Sorry - this crossed with your further advice)