* Report / query listing birthdays etc

[Deirdre784]

I'm looking to finish off my books (my own and a number i'm finishing for friends) with a list of birthdays, marriage dates and deaths, but would like to restrict to direct line relatives only for each book owner (and list relationship), and ideally to only include those with a known date, not just the month / quarter. Is this possible?

I've seen the topic from a few years back and downloaded the queries for 'birthdays (all)' and 'birthdays and anniversary calendar for relatives' but can't work out how to amend them to get what i'd like to use

[tatewise]

Are you happy with the Result Set format of the Birthdays (all) standard Query?

If so, then create a custom Query from that standard Query and adjust the Rows tab filters as follows.:
Delete the existing filter.
On the Relations tab:
Add if... Ancestor This Individual and tick Parameter Label: Starting Person
Add if... Descendant This Individual and tick Parameter Label: Starting Person
On the General tab:
Exclude if... %INDI.BIRT[1].DATE:DAY% is null

Create a similar custom Query but on both the Columns tab and Rows tab change every BIRT tag to DEAT to produce a similar Result Set for Death dates.

Create a similar custom Query but on both the Columns tab and Rows tab change every BIRT tag to FAMS[1]>MARR to produce a similar Result Set for Marriage dates.
However, that only lists details of the 1st family partnership of each person.
You will need FAMS[2]>MARR, FAMS[3]>MARR, and so on to cater for 2nd, 3rd, etc, partnerships.

Alternatively, we will need to design a Family type of custom Query instead of the Individual queries above.

[Deirdre784]

Thanks Mike, i'll give that a go. Just made a complete mess of trying myself

[Deirdre784]

Thanks Mike, after a few wrong turns i've done the basic births

Can i ask:
how to add in age (this year)?
Is it possible to include siblings / aunts / uncles?
Can the results be saved to add to the book?

I've done very few queries and don't find them easy

[tatewise]

Before we go too much further it is important to get a precise definition of exactly what you need.

Regarding Age today, that only works for living people, so what do you want for people who are deceased?
Also, if you publish the list in a Book those Ages will soon become inaccurate as time passes.

Are you happy with three separate lists of Birth dates, Death dates, and Marriage dates?
If not then how must they be combined?
e.g. What goes in the Age column for all three cases?

What order do you need for the lists?
e.g. Age order, chronological date order, or as the current Query which is grouped by Month, then Day, and finally Year.

Exactly what relationships are to be included?
Direct line ancestors and descendants, plus Siblings, Aunts & Uncles (i.e. Siblings of the Parents) of the Starting Person.

Do you want the relationships included in the listing?

What format is required in order to save into your book?
The list can be saved as a CSV file for a spreadsheet table or a text file with comma or tab separators for the columns.

[Deirdre784]

Sorry Mike, I got carried away there! I was looking for age this birthday alive or not, but yes of course it will be out of date in the book so i'll forget that one.

I was going to keep it simple and do 3 separate tables, births, marriages and deaths, and am happy with the current query sort order.

I was thinking of direct line ancestors and descendants, siblings, and then all children of the grandparents etc (where date known) of the starting person if possible?

How related would be good

Not sure how to add the table to my books which are made up of family group sheets reports, free text intros to each family and photos in media reports. All done in FH7. Just saved my query as both text and csv but not sure about either. Maybe use the snipping tool and post as an image, but no good for a large table......

[tatewise]

Sorry, but what does "all children of the grandparents etc" mean? Especially the "etc"?
Do you mean the immediate children of every direct line ancestor (but not the children of those children)?

What about the spouses of everyone included by the agreed rules?

Unfortunately, Queries only work with specifics, so I need to know exactly what you need.

Apologies, I did not realise you were referring to the FH Publish > Books feature to compose your book.
Yes, it is not obvious how to get the Query Results into a Book page.

[Deirdre784]

Sorry Mike, not explaining myself very well.

Do you mean the immediate children of every direct line ancestor (but not the children of those children)? Yes please to children of direct line ancestors, and not their children

What about the spouses of everyone included by the agreed rules? Ideally yes

I thought i'd worked out how to add 'relationship' but i'm being asked for 'individual' and 'starting person' when i run the query but as it's the same person it's not quite right. I assume i need to change "Starting Person" to individual?

birthdays relationship query.jpg (64.38 KiB) Viewed 878 times

Apologies, I did not realise you were referring to the FH Publish > Books feature to compose your book. Yes, and I'm really pleased with it now, thanks

I've got the current query into the book by running the query, print preview, snipping tool to save the image, adding the image to an 'unrelated individual' and adding the report to the book. Works fine, but I might have to re-visit if too many people in the list.

[tatewise]

Yes, the text inside [" "] must be the same in all cases to use the same prompt.
Here are the settings that I've used.

Birthday Columns.png (13.87 KiB) Viewed 842 times

This gives all Ancestors of the Individual, 1 Generation of their Children, all Descendants of the Individual, and all their Spouses.

Birthday Rows.png (37.05 KiB) Viewed 842 times

I've found a way of copying all the cells of the Query Result Set into an MS Word table, which can be formatted and then copied into a Book Free Text Page as follows:

1. In the FH Query Result Set use Ctrl-A and Ctrl-C to select and copy all cells.
2. In MS Word, in a blank document, use Ctrl-V to paste all the cells and then Ctrl-A to select all the text.
   Use Insert > Table > Convert Text to Table... with these settings and click OK:

   

   Text2Table.png (13.21 KiB) Viewed 817 times

   Then use Replace to Find every " and Replace with nothing to remove all the string quotes.
   Add the table headings to new first row and format as required, e.g. bold, select Day column and set Right Justify.
3. In the FH Book Free Text page, use Edit and Copy & Paste the table from MS Word into the Rich Text editor.
   Change the Chapter Heading for the Free Text page and add any explanation of the table.

The same technique works in other free word-processors such as OpenOffice.
This process needs to be added to the FHUG Knowledge Base.
Ideally, it would be useful if FH could Copy & Paste from any Query Result Set into a Rich Text window Table.

I leave you with the exercise of creating a similar Death Dates Query, then we can discuss the Marriage Dates Query which is a bit more complicated.

[Deirdre784]

Gosh thanks for that Mike, in work at the moment (just had a quick look) but will have a go at this tonight.

[Deirdre784]

Mike, that's fantastic, does exactly what I wanted to do and the three people whose trees I'm currently finalising (related to each other) are equally thrilled. I may 'play' with the row filters to include more generations / exclude spouses of descendants if I want to, depending on the tree.

I'll do deaths later (probably not as interesting to see who shared death dates as birthdays!) and then get back to you about marriages.

The query into the book has worked well too; my option was fine but limited to short tables.

[Deirdre784]

one more query - and I know I said 'no' to children of the ancestors' children - but if I extend the generations to 2 (or even 3) to include cousins, is there a way to exclude the 1st cousin once-removed (and all the once-removed)?

[tatewise]

Unless I misunderstand, I thought most of the children of children of an ancestor are nth cousins once/twice removed.

See https://www.rd.com/article/second-cousin-once-removed/ especially the chart under So what are second cousins?
Which of those relatives do you want to be included?
Is it just 1st Cousins?

If so then I think I've worked out how to do that.

[Deirdre784]

Having seen the query results i realised that my third cousin - who i have been researching our mystery Irish branch with for some years - is missing. She also shares her birthday with my dad. So ideally i would like 1st 2nd and 3rd cousins (but none of the ‘removeds’). Only three of our shared great great grandparents children survived but my paternal grandmother was one of 13, and my paternal grandfather one of 8, so there are lots of cousins, though I don’t know all their actual dates of birth so some will be missing.

[tatewise]

The screenshot below will include all the descendants of your ancestors except those who are 'removed'.
I'm fascinated by what you will do if you find some 'removed' cousin has the same Birth Date as a close family member.
Presumably, this only applies to your family book and not the other three families.

Birthday Rows No removed.png (37.22 KiB) Viewed 757 times

[Deirdre784]

That’s brilliant Mike, thanks so much for all your help, not only on this puzzle but throughout my FH journey. I am really pleased with how my own book is shaping up - just a few more family groups to add in - and the current three ‘others’ are very impressed not only with my findings but also how it fits together with this software (and i know i have barely touched the surface).

PS i doubt i’ll ever know if any of the removeds have the same birthday as someone close

[Deirdre784]

Mike, using your 'removed' line as an example I've now excluded the 'great (x2) great aunts' and uncles (and x3 and x4) too.

[tatewise]

Excellent! Creating the Death Dates should be easy

I have added the FHUG Knowledge Base page: Copy a Query Result Set to a Table in Notes or Book Item.

[Deirdre784]

That’s tonight’s exercise and thanks for adding the process