An Overview of Queries

Introduction

According to the Family Historian Help file “A query is a reusable set of instructions for extracting information from a Family Historian file. The information returned by a query is called a Result Set, and it is laid out in tabular form, in a grid of rows and columns. Each row in the Result Set corresponds to a record. If the query is an Individual query, each row returned will correspond to an Individual record. If it is a Source query, each row will correspond to a Source record.”

There are more types of queries, of course: you can create a query based on any Record Type that ƒh support, and also on Facts.

Queries are a very powerful feature of ƒh because they allow you to analyse your data in an almost any way you can think if. What did my direct ancestors die from? How large were the families of the people in my tree? What facts have I entered without citing a source? All these and many more questions can be answered using Queries.

Standard and Custom Queries

Queries fall into two categories: Standard Queries, which are distributed with ƒh and cannot be edited (except to change the behaviour when printed) , and Custom Queries which are created by users (either for personal use or to share via the Queries section of our Downloads page).

Queries are accessed via the Query Window (View > More Workspace Windows > Query Window) or the Query Window icon in the main toolbar. If you want to navigate straight to a Standard query, use View> Standard Queries > Query name; and if you want to navigate straight to a Custom Query, use View> Custom Queries > Query name

See The Query Window for instructions on how to use the Query Window and all its tabs. You are advised to read the Help file before moving on to the Hints and Tips below.

Working with Query Results

Any entry that is a Record can be double-clicked to open its Property Box to review or edit fields.

Any entry that is a Data Field can be right-clicked to choose Locate in Property Box or Locate in All Tab.

Any entry that is a Data Field can have its contents deleted by pressing the DELETE key. Also, by selecting multiple entries such as an entire column, then bulk deletes can be applied.

Printing the Results Set from a Query

It is possible to Print the Results Set from a Query, and there are Print Detail options to configure the printed output on the General tab of the Query Window.

Most of the Print Details options are self-explanatory, but the Title and Subtitle Custom options have some undocumented features to incorporate any Value entered via a Rows Tab prompt for a Parameter. Any such Value is represented by [“Prompt”], where Prompt is the name of the parameter Label, and to include it in the Title or Subtitle it must be enclosed in a Type Specifier Function of a matching type: Text([“Prompt”]), Number([“Prompt”]), Individual([“Prompt”]), Date([“Prompt”]), etc.
e.g. Date =Number(["Day"]) of =Text(["Month"]) for =Individual(["Person"])

Saving Results to a File

Since ƒh V6 the Save Results to File menu offers two Save As Comma-Separated CSV File options for UNICODE UTF-8 and ANSI encoding, but some spreadsheet programs do not handle UNICODE UTF-8 well, so try both options, or even the Save As Text File options with either Tab character or Comma as the Column separators. See also How to Open UTF-8 in Excel 2013-2016 and How to Import CSV file that uses UTF-8 in Excel 2007.

You should note that Excel (as shipped) does not support Dates earlier than 1 January 1900.  There are two approaches to dealing with this:

  1. Use numerical columns for Day Month and Year in your queries if you intend to export them to Excel.
  2. If you’re very comfortable with Excel, explore the techniques in How to Work with Dates Before 1900 in Excel

Installing a Custom Query

If you have downloaded a Custom Query (for example from the Queries section of our Downloads page or the FHUG forums you must install it before you can use it. The simplest way to do so is to double-click on the file to open it; ƒh will automatically install it.

Creating a Custom Query

Sooner or later, most users of ƒh have a question about their data that can be answered using a Custom Query. You may find the prospect quite daunting, but the process of creating a simple custom query is really quite simple.

Chapters 14 and 15 of Getting the Most From Family Historian 5 or Chapters 17 and 18 of the equivalent book for Version 6 are a good introduction, (The contents of the Version 5 book still apply to Version 6 in the area).

Another good starting point is this “Creating an Married Women Query” animated tutorial for Version 4 and above:

There is another animated tutorial for version 3 which still contain relevant concepts (although toolbars, buttons and colours have changed); and two more demonstrating how to use Custom Queries in conjunction with other features.

V3 Animated Tutorials

Unfortunately, the last few steps of this tutorial skip past too quickly, so to recap this Custom Query should have the following entries.

Columns tab: and Rows tab:
Heading Expression Filter
Individual %INDI% Exclude if %INDI.~SPOU>NAME:SURNAME% is null
Spouse Name(SURNAME) %INDI.~SPOU[1]>NAME[1]:SURNAME% Exclude if %INDI.SEX% matches Male
Spouse Name(SURNAME) %INDI.~SPOU[2]>NAME[1]:SURNAME%

Then clicking the red triangular Run Query button should produce a Result Set of Married Women.

Custom Query Types

It is important to consider which type of Query will be most useful for the problem you want to solve.

    • Individual queries are the most popular. They return a set of Individual records; the columns can include records linked to an Individual (e.g. Family or Source records, or Individual records for spouses and children).

      For example, if you want to identify a set of Individuals who meet a set of criteria (e.g. Male Individuals who were born between two specified years and still alive in 1914, so might have legally served in World War 1) an Individual query is what you need.

      However, to show more than a few selected Facts in the Result Set will require a Column for every field of each and every type of Fact plus each and every instance of multiple Facts such as Census. That could run to dozens if not hundreds or thousands of Columns.

      If your focus is on the Facts for a set of Individuals, consider a Fact Query instead.

    • A Fact Query focuses on Facts, not records, but the Columns and Rows can refer to any items, including records associated with a Fact.

      For example it can include, each in a different Column, the Individual or Family record that owns the Fact, or any Source records cited by the Fact However, each Row in the Result Set is keyed to just one unique Fact, and that Fact is only represented on one Row.

      So, if you want to view all the Education Facts for people in your family tree, a Fact query is what you want.

    • Family queries will return a list of Family records that meet your chosen criteria.

      So, if you want to see a list of couples who married between two dates in a particular place, a Family query is what you want.

    • You can also construct Queries based on Media, Note, Place and Repository records.For example, you can query to see all the Sources you consulted at a particular Repository.

Columns Tab Advice

In the Columns Tab:

    • Data Reference Fields are chosen using the Fields pane on the left. Click on any + sign to expand a sub-list, and having selected a Field, click on the Add Field as Column button > at the bottom. This will add a Heading and an Expression to the Columns pane on the right.

      Although these Columns are listed vertically, they will appear horizontally in the Result Set, and moving them up and down, actually moves them left and right in the results.

    • If you have created Text Schemes in Diagrams, then these Expressions are almost identical, and similarly can include Functions.

      Look at other Queries and you will see how they use Fields and Query Functions. For an Introduction to Data References and Functions see Understanding Data References and Understanding Functions.

    • Remember that many Data Reference Fields can have more than one instance, but only the first one is added by default.

      For example the Individual > Name field has an Expression of %INDI.NAME[1]%. If any Individual has Alternate Names then %INDI.NAME[2]% and %INDI.NAME[3]% will also exist, and these must be entered as extra Columns and the index adjusted to [2] or [3] by hand.

      It is easily overlooked that Attributes, Events, Sources, and many other fields often have multiple instances that need to be incorporated.

Sorting a Fact Query on Owner Surname

In a Fact Query the Owner of the Fact is displayed using one of these Column Expressions:

  • =FactOwner(%FACT%,1,MALES_FIRST) for Family Fact Husband or Individual Fact Owner.
  • =FactOwner(%FACT%,2,MALES_FIRST) for Family Fact Wife (otherwise blank).
  • =GetRecord(%FACT%) for Family Fact Husband & Wife record or Individual Fact Owner record.

They all display the name of the owner with forenames first and surname last, so it is not possible to sort them in surname order. The following column expressions allow you to obtain surname sort order.

These Column Expressions work in FH V5 and above:

  • =FieldText(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME:SURNAME_FIRST') which displays the surname first, and sorts in surname order, but is NOT an active link to the Individual record.
  • =Field(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME') which still displays forenames first, but sorts in surname order, and is an active link to the Individual record.

For FH V6 and above, those two Expressions can be combined into one Result Set Column that exhibits the best features of both:

  • 1st Column uses Expression: =FieldText(…) with Sort: None and Type: Normal so appears in Result Set.
  • 2nd Column uses Expression: =Field(…) with Sort: Ascending and Type: Buddy so is hidden, but defines the sort order of 1st Column and provides an active link.

 

 

 

 

 

Last update: 10 Aug 2020

Related Content

Related Forum Discussions

CSV date issues in spreadsheets