Query with marriage date


Randy Dykhuis
 

I will confess upfront that I've not spent a lot of time with queries. However, as my database has grown, and I'm starting to look for data that goes beyond using the Property box individual by individual.

I would like to write a query that returns a list of all children of my great-grandfather, their birthdate, their spouses' names, spouses' birthdates, and marriage dates. I can write a query that gets me everything except marriage. There is no marriage event so I am unable to add a column for that. I am using an Individual query and thought perhaps I needed a family query but that didn't seem to work either. It seems like there must be a simple way to do this but I sure am not finding it. Any help would be appreciated.


Brian Horridge
 

Randy

You are right that the marriage is a "Family" event but you can still show it in an "Individual" Query.

In your Query in the "Columns" tab expand on "Family as Spouse" and then you can pick any of the spouse's events / attributes you want.  If you you want the Parents' details, choose from "Family as Child" events / attributes

If you want a column to show a spouse's ID use  the expression =RecordId(%INDI.~SPOU>%) .  A second's spouse's ID can be shown as =RecordId(%INDI.~SPOU[2]>%) etc by using the "[2]" in most queries.

Hope that does what you want.

Brian

On 17/03/2021 01:25, Randy Dykhuis wrote:
I will confess upfront that I've not spent a lot of time with queries. However, as my database has grown, and I'm starting to look for data that goes beyond using the Property box individual by individual.

I would like to write a query that returns a list of all children of my great-grandfather, their birthdate, their spouses' names, spouses' birthdates, and marriage dates. I can write a query that gets me everything except marriage. There is no marriage event so I am unable to add a column for that. I am using an Individual query and thought perhaps I needed a family query but that didn't seem to work either. It seems like there must be a simple way to do this but I sure am not finding it. Any help would be appreciated.

Virus-free. www.avg.com


Randy Dykhuis
 

Well, that was easy. I knew I must have been missing something. Thanks, Brian.

A follow-up question: How would one learn more about creating Expressions such as the one you used for spouse's ID? Is there information in the FH help? Or in the KnowledgeBase? I've looked but haven't found a comprehensive overview & explanation. Thanks.


From: "Brian Horridge via groups.io" <brian.horridge@...>
To: "family-historian@groups.io" <family-historian@groups.io>
Sent: Wednesday, March 17, 2021 12:40:25 AM
Subject: Re: [family-historian] Query with marriage date

Randy

You are right that the marriage is a "Family" event but you can still show it in an "Individual" Query.

In your Query in the "Columns" tab expand on "Family as Spouse" and then you can pick any of the spouse's events / attributes you want.  If you you want the Parents' details, choose from "Family as Child" events / attributes

If you want a column to show a spouse's ID use  the expression =RecordId(%INDI.~SPOU>%) .  A second's spouse's ID can be shown as =RecordId(%INDI.~SPOU[2]>%) etc by using the "[2]" in most queries.

Hope that does what you want.

Brian

On 17/03/2021 01:25, Randy Dykhuis wrote:
I will confess upfront that I've not spent a lot of time with queries. However, as my database has grown, and I'm starting to look for data that goes beyond using the Property box individual by individual.

I would like to write a query that returns a list of all children of my great-grandfather, their birthdate, their spouses' names, spouses' birthdates, and marriage dates. I can write a query that gets me everything except marriage. There is no marriage event so I am unable to add a column for that. I am using an Individual query and thought perhaps I needed a family query but that didn't seem to work either. It seems like there must be a simple way to do this but I sure am not finding it. Any help would be appreciated.


Virus-free. www.avg.com




Brian Horridge
 

Randy

I learnt most of my "query tricks" by looking at the wide variety of Standard Queries that come with FH and how they return and display different information (even if the standard query is not really what I want the expressions may be useful).  I would copy individual column expressions into my own specific custom query (and also the Results window column if useful there). In addition, by looking at these varied queries, you get a feel for the relevant syntax required (ie the use of "[2]" and "[3]" etc.)

I've been using FH for a very long time (before the start of the KnowledgeBase) so I've picked up a lot hints and tips along the way from this e-mail list and latterly the FHUG Forum.  You can get a list of "Functions" from the "Help-Search Help" and type in Functions.  The results do not always give examples but using this help and the Knowledgebase (and other standard queries) you can usually work it out.  It's also worth looking at examples of Family queries to again get a feel for the syntax.

A lot of it was trial and error and I kept going till I managed to get rid of the FH error messages!!  

Another tip - something I do (to overcome the frustration of having to scroll down through the long list of queries) is that when I save a custom query, I name it "aa-myquery"  etc as the leading "aa-" makes it appear at the top of the list so can be seen without scrolling.  Therefore, the queries I use most are always at the top.

Hope this helps

Brian

On 17/03/2021 12:44, Randy Dykhuis wrote:
Well, that was easy. I knew I must have been missing something. Thanks, Brian.

A follow-up question: How would one learn more about creating Expressions such as the one you used for spouse's ID? Is there information in the FH help? Or in the KnowledgeBase? I've looked but haven't found a comprehensive overview & explanation. Thanks.


From: "Brian Horridge via groups.io" <brian.horridge@...>
To: "family-historian@groups.io" <family-historian@groups.io>
Sent: Wednesday, March 17, 2021 12:40:25 AM
Subject: Re: [family-historian] Query with marriage date

Randy

You are right that the marriage is a "Family" event but you can still show it in an "Individual" Query.

In your Query in the "Columns" tab expand on "Family as Spouse" and then you can pick any of the spouse's events / attributes you want.  If you you want the Parents' details, choose from "Family as Child" events / attributes

If you want a column to show a spouse's ID use  the expression =RecordId(%INDI.~SPOU>%) .  A second's spouse's ID can be shown as =RecordId(%INDI.~SPOU[2]>%) etc by using the "[2]" in most queries.

Hope that does what you want.

Brian

On 17/03/2021 01:25, Randy Dykhuis wrote:
I will confess upfront that I've not spent a lot of time with queries. However, as my database has grown, and I'm starting to look for data that goes beyond using the Property box individual by individual.

I would like to write a query that returns a list of all children of my great-grandfather, their birthdate, their spouses' names, spouses' birthdates, and marriage dates. I can write a query that gets me everything except marriage. There is no marriage event so I am unable to add a column for that. I am using an Individual query and thought perhaps I needed a family query but that didn't seem to work either. It seems like there must be a simple way to do this but I sure am not finding it. Any help would be appreciated.


Virus-free. www.avg.com





Helen Dignum
 

This Spouse ID expression is very useful.   I have long wanted to be able to display the spouse in the Individual Records Display to help distinguish between the many individuals of the same name married to different spouses (previously I named as Stephen /Clay (spouse Eliz) , Stephen/ Clay (spouse Ann) etc.)

So following on from above, I created a custom query of Spouse, adding the %INDI.~SPOU[1]>% to the column expression.
then I can configure columns using add from query, adding query spouse.
The add column from query seems to override all the usual default columns so you need to add back in other columns like dates, birth place etc to suit.
Thanks, Helen


Brian Horridge
 

Helen

I don't fully understand the problem you are having.  Normally, clicking the "Add" puts the new expression at the bottom and then you need to use the up / down arrows to position it.

However, I must admit that I never normally start a query from scratch - I've always picked the "best fit" from the standard ones, save that as my own Custom Query and then add the new column into "my" Custom Query.  If there are Columns in the "best fit" that I don't want I just remove them from my version till I only have the ones I want.

Brian

On 17/03/2021 13:57, Helen Dignum via groups.io wrote:
This Spouse ID expression is very useful.   I have long wanted to be able to display the spouse in the Individual Records Display to help distinguish between the many individuals of the same name married to different spouses (previously I named as Stephen /Clay (spouse Eliz) , Stephen/ Clay (spouse Ann) etc.)

So following on from above, I created a custom query of Spouse, adding the %INDI.~SPOU[1]>% to the column expression.
then I can configure columns using add from query, adding query spouse.
The add column from query seems to override all the usual default columns so you need to add back in other columns like dates, birth place etc to suit.
Thanks, Helen

Virus-free. www.avg.com


Brian Horridge
 

Randy

I also forgot to mention that there are quite a few customised queries on the FHUG in the "KnowledgeBase - Downloads" page.  They used to be submitted by users themselves if they'd found something useful they thought others might benefit from but I think that might now have changed (it used to identify who uploaded it and when).

Brian

On 17/03/2021 12:44, Randy Dykhuis wrote:
Well, that was easy. I knew I must have been missing something. Thanks, Brian.

A follow-up question: How would one learn more about creating Expressions such as the one you used for spouse's ID? Is there information in the FH help? Or in the KnowledgeBase? I've looked but haven't found a comprehensive overview & explanation. Thanks.


From: "Brian Horridge via groups.io" <brian.horridge@...>
To: "family-historian@groups.io" <family-historian@groups.io>
Sent: Wednesday, March 17, 2021 12:40:25 AM
Subject: Re: [family-historian] Query with marriage date

Randy

You are right that the marriage is a "Family" event but you can still show it in an "Individual" Query.

In your Query in the "Columns" tab expand on "Family as Spouse" and then you can pick any of the spouse's events / attributes you want.  If you you want the Parents' details, choose from "Family as Child" events / attributes

If you want a column to show a spouse's ID use  the expression =RecordId(%INDI.~SPOU>%) .  A second's spouse's ID can be shown as =RecordId(%INDI.~SPOU[2]>%) etc by using the "[2]" in most queries.

Hope that does what you want.

Brian

On 17/03/2021 01:25, Randy Dykhuis wrote:
I will confess upfront that I've not spent a lot of time with queries. However, as my database has grown, and I'm starting to look for data that goes beyond using the Property box individual by individual.

I would like to write a query that returns a list of all children of my great-grandfather, their birthdate, their spouses' names, spouses' birthdates, and marriage dates. I can write a query that gets me everything except marriage. There is no marriage event so I am unable to add a column for that. I am using an Individual query and thought perhaps I needed a family query but that didn't seem to work either. It seems like there must be a simple way to do this but I sure am not finding it. Any help would be appreciated.


Virus-free. www.avg.com





Lorna Craig
 

Helen, if you are talking about adding the column to the Individuals tab of the Records window you can do that by using Configure Columns, then select <other> from the bottom of the list on the left hand side, then enter the data reference and click OK.  You don't need to 'Load from Query'.

However, having set the columns as you like them it is a good idea to 'Save to Query' and call the query something like 'Records Window Individuals tab' so that if you need to reinstall FH or experiment with other columns you can quickly get back to your preferred configuration by using 'Load from Query' which will reinstate all your custom columns.  There is then no need to add any manually.

Lorna

On 17/03/2021 13:57, Helen Dignum via groups.io wrote:
This Spouse ID expression is very useful.   I have long wanted to be able to display the spouse in the Individual Records Display to help distinguish between the many individuals of the same name married to different spouses (previously I named as Stephen /Clay (spouse Eliz) , Stephen/ Clay (spouse Ann) etc.)

So following on from above, I created a custom query of Spouse, adding the %INDI.~SPOU[1]>% to the column expression.
then I can configure columns using add from query, adding query spouse.
The add column from query seems to override all the usual default columns so you need to add back in other columns like dates, birth place etc to suit.
Thanks, Helen
_.


Mike Tate
 

One source of advice that has not been specifically mentioned is the FHUG Knowledge Base ‘Understanding Data References’:

https://fhug.org.uk/kb/kb-article/understanding-data-references/

That has a link to an online copy of the FH V6 Help page of the same name:

https://www.family-historian.co.uk/help/fh6/hh_start.htm#understandingdatareferences.html

FH V7 has an updated version of that.

 

See also the companions ‘Understanding Functions’ and ‘Understanding Expressions’:

https://fhug.org.uk/kb/kb-article/understanding-functions/

https://fhug.org.uk/kb/kb-article/understanding-expressions/

 

Mike Tate

 

 


David
 

Sounds a good idea - but how does one do that?

On 17/03/2021 14:34, Lorna Craig via groups.io wrote:
However, having set the columns as you like them it is a good idea to 'Save to Query' and call the query something like 'Records Window Individuals tab' so that if you need to reinstall FH or experiment with other columns you can quickly get back to your preferred configuration by using 'Load from Query' which will reinstate all your custom columns.
--
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


Mike Tate
 

How does one do what?
Do you need to know how to configure Records Window columns or save/load as query?
Right-click in the tab and choose Columns... or use the Lists > Configure Records Window Columns... command.
The Load from Query... and Save to Query... buttons are at the bottom of that dialogue.

Mike Tate


Randy Dykhuis
 

Thanks, Brian. These are good suggestions. I have used the standard queries, too, as a basis for my custom queries. That got me to wondering how to move beyond that. Thanks for your pointers.


From: "Brian Horridge via groups.io" <brian.horridge@...>
To: "family-historian@groups.io" <family-historian@groups.io>
Sent: Wednesday, March 17, 2021 10:17:42 AM
Subject: Re: [family-historian] Query with marriage date

Randy

I also forgot to mention that there are quite a few customised queries on the FHUG in the "KnowledgeBase - Downloads" page.  They used to be submitted by users themselves if they'd found something useful they thought others might benefit from but I think that might now have changed (it used to identify who uploaded it and when).

Brian

On 17/03/2021 12:44, Randy Dykhuis wrote:
Well, that was easy. I knew I must have been missing something. Thanks, Brian.

A follow-up question: How would one learn more about creating Expressions such as the one you used for spouse's ID? Is there information in the FH help? Or in the KnowledgeBase? I've looked but haven't found a comprehensive overview & explanation. Thanks.


From: "Brian Horridge via groups.io" <brian.horridge@...>
To: "family-historian@groups.io" <family-historian@groups.io>
Sent: Wednesday, March 17, 2021 12:40:25 AM
Subject: Re: [family-historian] Query with marriage date

Randy

You are right that the marriage is a "Family" event but you can still show it in an "Individual" Query.

In your Query in the "Columns" tab expand on "Family as Spouse" and then you can pick any of the spouse's events / attributes you want.  If you you want the Parents' details, choose from "Family as Child" events / attributes

If you want a column to show a spouse's ID use  the expression =RecordId(%INDI.~SPOU>%) .  A second's spouse's ID can be shown as =RecordId(%INDI.~SPOU[2]>%) etc by using the "[2]" in most queries.

Hope that does what you want.

Brian

On 17/03/2021 01:25, Randy Dykhuis wrote:
I will confess upfront that I've not spent a lot of time with queries. However, as my database has grown, and I'm starting to look for data that goes beyond using the Property box individual by individual.

I would like to write a query that returns a list of all children of my great-grandfather, their birthdate, their spouses' names, spouses' birthdates, and marriage dates. I can write a query that gets me everything except marriage. There is no marriage event so I am unable to add a column for that. I am using an Individual query and thought perhaps I needed a family query but that didn't seem to work either. It seems like there must be a simple way to do this but I sure am not finding it. Any help would be appreciated.


Virus-free. www.avg.com