TNG report to display individuals with NO dates, but who have a spouse and/or children with dates from more than 110 years ago

[JMM]

• TNG version: 14.0.6 and 15.x
• Data source: Legacy Family Tree v10

Hello MySQL experts,

Firstly, I took a look at all of the reports written by Henny Savenije, and also the reports list here in the Wiki, but unfortunately none of them are what I am looking for.

Background:  When I first got into Genealogy more than 25 years ago, I was very lax setting new users to either Living or Not Living.  As a result, I have many (possibly thousands of) people in my tree who lived many centuries ago, but currently show in TNG as Living because there are no birth/dates associated with them.

Both Legacy and TNG can set/display users as Living or Not Living, based on settings & utilities/processes, but they can only do so when dates are known.

I have my TNG sites set to correctly protect (not display certain things), based on if users are logged in or not.  That is not the problem, and not what I am inquiring about here.

Problem:  As mentioned above, I have many (possibly thousands of) people in my tree who lived many centuries ago, but currently show in TNG as Living because there are no birth/dates associated with them.  An example of where this becomes sorta comical (but also annoying!) is when displaying ancestor charts.  I've randomly come across some ancestor charts on my site, which has one or more individuals showing birth and/or death dates from centuries ago (example, dates in the 1700s), but above them is listed an ancestor showing as Living (because I have no dates for that ancestor).

To illustrate easily what I am trying to explain, I temporarily removed all dates from one of my ancestors in order to make the below screenshot.  Look at the below screenshot, and note on the 2nd generation from the top, the husband of Israel MEADEN.  Even though he lived in at least the 1600s and possibly 1700s too, and therefor would've died about 300 years ago, in TNG he is still showing as Living :

In the above example, the problem is NOT with TNG... TNG is correctly displaying what it is setup to display.  The problem is with my Legacy program exporting that person as still living, so I need to manually change that & similar people in situations like that to Not Living in Legacy.

But how can I know, which individuals who lived 125+ years ago, are set in my Legacy database as still Living?  Using TNG's report-writer, I very-easily created a basic report to display all people on my TNG site who do not have a born/baptized/died/buried date.  However, many of those individuals displayed using that report were born less than 125 years ago, so could quite possibly still be alive.

Solution:  I am hoping that a MySQL expert can help me create a report, which will display all users:

1. with NO died/buried date; and
2. who have a spouse and/or children who have ANY date associated with them that was more than, say, 110 years ago.  By dates associated with them, the dates could be for any of the following (but at least one of those dates):
   • born/baptized/died/buried;
   • any alternate date;
   • any other date, such as: census, education, marriage, residence, marriage, etc.
3. So for criteria 2 (above), rather than the MySQL report searching for specific dates, hopefully the MySQL code can just search for ANY date associated with those spouses & children which were more than 110 years ago.

Would such a report be doable?  If yes, then I could run that report myself, which will tell me which specific individuals in my Legacy database that I need to individually change from Living to Not Living manually .

Thank you in advance, and have a great day.

Regards,
John

[GOGGS]

Hey John, 

That's a really cool idea. This kind of "query" (if even doable) would be immensely complex, especially in TNG where the report function doesn't allow temporary tables in a query execution (making a query that creates a result that can be joined to more tables or results). Just to check husband and wife in one simple query requires joining the families table to the people table twice (once for husband and once for wife) to get each's details. And the number of conditions you're checking makes really long and complex WHERE clauses. For reference, I created a query that checks husbands and wives as you desired, but only for deathdate (not all of the other possibilities (see below). I think you might have better luck exporting a subset of the data and putting in in Excel to do VLOOKUP's and auto-filter. I can help with a couple of queries to pull out parents with certain conditions, and another for kids.

I also have to say that the Living flag is really intended for privacy in TNG, but if you have users with privacy "turned on", then cleaning up the data makes sense.

Here's what I ran on my data (and found 122 examples out of 56,000 people). You can add more fields in the SELECT clause and WHERE clause, but again, the WHERE clause will start getting really complex:

 

SELECT h.personID AS husband, h.deathdatetr AS hus_death, h.living AS h_living, w.personID AS wife, w.deathdatetr AS wif_death, w.living AS w_living 
FROM tng_families f  
LEFT JOIN tng_people h ON f.gedcom = h.gedcom AND f.husband = h.personID 
LEFT JOIN tng_people w ON f.gedcom = w.gedcom AND f.wife = w.personID  
WHERE 
((h.deathdatetr = '0000-00-00' AND w.deathdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(w.deathdatetr) > 110 AND h.living = 1)
OR 
(w.deathdatetr = '0000-00-00' AND h.deathdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(h.deathdatetr) > 110 AND w.living = 1))

Cheers

GOGGS

[JMM]

Wow.... thanks, Gary.

2 hours ago, GOGGS said:

For reference, I created a query that checks husbands and wives as you desired, but only for deathdate (not all of the other possibilities (see below).

I created a new report with your code as-is, and it found 1 couple.  In Legacy I then corrected the person who I still had as living, re-imported the GEDCOM, and then that report found 0 couples.

I then copied your code as-is into 3 new reports (for birth, baptized & buried), but changed the death items to birth, bapt & burial respectively), and those 3 new reports worked too.

The death report found 1 couple; the baptized & burial reports found 0 couples; and the birth report found 32 couples .

I then tried to modify your code slightly, to include the husbands & wives lastnames & firstnames, but here is where I have a hiccup :

If you look at that report HERE, and ignore all columns that are Last Name and First Name, everything is working correctly.  However if you then look at the Last & First names columns, note that the wives' last (w.lastname) & first (w.firstname) names are also showing up in the husbands' last & first names columns, instead of the husbands' actual last (h.lastname) & first (h.firstname) names.

Your slightly modified code that I am trying, that has the wives' last & first names in the 2 husbands' columns is:

SELECT h.personID AS husband, h.lastname, h.firstname, h.birthdatetr AS hus_birth, h.living AS h_living, w.personID AS wife, w.lastname, w.firstname, w.birthdatetr AS wif_birth, w.living AS w_living 
FROM tng_families f  
LEFT JOIN tng_people h ON f.gedcom = h.gedcom AND f.husband = h.personID 
LEFT JOIN tng_people w ON f.gedcom = w.gedcom AND f.wife = w.personID  
WHERE 
((h.birthdatetr = '0000-00-00' AND w.birthdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(w.birthdatetr) > 110 AND h.living = 1)
OR 
(w.birthdatetr = '0000-00-00' AND h.birthdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(h.birthdatetr) > 110 AND w.living = 1))

Is there anything in my slightly-modified code that jumps out at you that might be causing my hiccup?

Apologies, but MySQL is all foreign to me.

Thanks in advance, Gary, and have a good evening.

Regards,
John

[GOGGS]

When I paste your code into phpMyAdmin, it runs as it should. There might be an anomaly with the TNG report code for rendering the SQL, so I renamed the columns with names to remove ambiguity with the original column names. When I pasted this modified version in the report tool, it fixed the problem.

Here's the code:

SELECT h.personID AS husband, h.lastname AS hlast, h.firstname AS hfirst, h.birthdatetr AS hus_birth, h.living AS h_living, w.personID AS wife, w.lastname AS wlast, w.firstname AS wfirst, w.birthdatetr AS wif_birth, w.living AS w_living 
FROM tng_families f  
LEFT JOIN tng_people h ON f.gedcom = h.gedcom AND f.husband = h.personID 
LEFT JOIN tng_people w ON f.gedcom = w.gedcom AND f.wife = w.personID  
WHERE 
((h.birthdatetr = '0000-00-00' AND w.birthdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(w.birthdatetr) > 110 AND h.living = 1)
OR 
(w.birthdatetr = '0000-00-00' AND h.birthdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(h.birthdatetr) > 110 AND w.living = 1))

 Cheers

 

GOGGS

[tngrlkrz]

Gary,

The report results are looking correct.  I know some PHP and HTML, but my SQL is weak.  A while ago, I thought I had cleaned up those living who should be deceased.  But with your query code, I found another 146 individuals to mark not living.  Actually, I make the changes within Family Historian, my PC software, which is then exported to TNG (and Family Tree Maker).  

Also, thanks to the OP, John, for inspiring Gary to do the code.

Ron

[GOGGS]

John, Ron - no problem - tackling these once in a while helps me stay sharp...

To round out the discussion (for any curious readers), a lot of complex data compilations in TNG are done with multiple queries and those results are stored in variables/arrays in PHP, and then combined with all of its cool built-in functions. That's how you can do more complex things like what John was originally asking for. To make a new PHP page that does this isn't that hard, but would take more time than a simple query. If it was important enough and a lot of people needed it, that could be an option.

With the query from my previous post, you could also add more conditions for other date fields (baptized, buried, etc.). I didn't since I don't use them and wouldn't have any data to test.

I think I'll play around with the kids to parents version (e.g. a kid has an "old" death date where the parent does not and is "living") and let you know. 

 

GOGGS 

[JMM]

Gary, as Ron mentioned, your most-recent code above works perfectly!  Thank you, Sir.

25 minutes ago, GOGGS said:

With the query from my previous post, you could also add more conditions for other date fields (baptized, buried, etc.).

That's exactly what I did a few hours ago, as you can see with my reports 10, 11, 12 & 13 here .  They work perfectly too.

I tried modifying your code some more for yet another report, this one to similarly search those older dates for Events (eventdatetr), but when I tested it, it bombed, as I know know nothing about MySQL .

25 minutes ago, GOGGS said:

I think I'll play around with the kids to parents version (e.g. a kid has an "old" death date where the parent does not and is "living") and let you know. 

That is exactly the case of what happened in the screenshot in my original post, whereby I temporarily removed all birth/baptism/death/burial information for Christopher's father in order to make that screenshot.  If you succeed with one for "kids to parents", I strongly feel that that report will produce many more results... at least on my site.

Thanks again for your knowledge & expertise, and your willingness to help out.

Regards & have a great weekend,
John

[tngrlkrz]

John, 

Could you PM me with the other variations of Gary's quoted query to weed out more mistakenly labelled living persons?   That would be most helpful. 

Ron

[JMM]

Ron,

51 minutes ago, tngrlkrz said:

Could you PM me with the other variations of Gary's quoted query to weed out more mistakenly labelled living persons? 

I just tried to PM you, but I was told:

tngrlkrz cannot receive messages.

Standby while I try something...

[JMM]

Ron, actually, you've already created a report with Gary's original code for death.

Just create 3 more new reports, copy & paste in the exact same code, then:

• For births: replace all of his code with the word death to the word birth
• For baptisms: replace all of his code with the word death to the word bapt
• For burials: replace all of his code with the word death to the word burial

That's it .

Regards & have a good weekend,
John

[Ken Roy]

John and Gary,

Why not post the reports on the TNG Wiki so any and all TNG users can access the reports?

Thanks,

[GOGGS]

Ken, great idea. I'll post the first one since I think that is the most universal (again, because I don't use most of the other date fields), and maybe John and Ron can add more...

 

GOGGS

[tngrlkrz]

8 hours ago, JMM said:

I just tried to PM you, but I was told:

tngrlkrz cannot receive messages.

John,  Sorry, will check it out.  I think my allotted space in the PM area is full (again).  

Question for anyone:  Is there an easy way to manage PM area messages, e.g., delete the largest ones?

Ron

[tngrlkrz]

9 hours ago, JMM said:

you've already created a report with Gary's original code for death

Thanks, actually I had Gary's original code for 'birth', which checked out OK. Will try the others.

[GOGGS]

OK, I have some SQL for the version that tests whether a parent is shown as living, but their child has died. The SQL scares me a little with 5 tables joined together, but it ran fast on my server (0.02 seconds) and produced good results. 

I used the test of whether a child has died more that 80 years ago, but since children may have died at/near birth, users may want to adjust. Also, the birth date for the child could be used instead or in addition by modifying or adding to the WHERE clause. And any other dates (for a child) as well. 

For clarity, the child date fields would be in the "k" table (k for kids), so k.birthdatetr would be for the child's birth date. Likewise, "h" and "w" for the husband and wife table selectors.

SELECT c.personID AS kidID, k.firstname AS kidfirst, k.lastname AS kidlast, k.birthdatetr AS kidbirth, k.deathdatetr AS kiddeath, 
f.husband AS husbID, h.firstname AS husbfirst, h.lastname as husblast, h.deathdatetr AS husbdeath, h.living AS husbliving, 
f.wife AS wifeID, w.firstname AS wifefirst, w.lastname AS wifelast, w.deathdatetr AS wifedeath, w.living AS wifeliving 
FROM `tng_children` c 
INNER JOIN tng_families f ON c.gedcom = f.gedcom AND c.familyID = f.familyID 
INNER JOIN tng_people h ON f.gedcom = h.gedcom AND f.husband = h.personID 
INNER JOIN tng_people w ON f.gedcom = w.gedcom AND f.wife = w.personID 
LEFT JOIN tng_people k ON c.gedcom = k.gedcom AND c.personID = k.personID 
WHERE 
(h.living = 1 AND k.deathdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(k.deathdatetr) > 80)
OR 
(w.living = 1 AND k.deathdatetr <> '0000-00-00' AND YEAR(CURDATE()) - YEAR(k.deathdatetr) > 80)

 

GOGGS 

[tngrlkrz]

23 minutes ago, GOGGS said:

whether a parent is shown as living, but their child has died.

Gary, 

Seems to have produced 37 rows of legit results. With 15k+ persons, ran nearly instantly on my local server (don't know how to get execute time).

Ron

[tngrlkrz]

Posted this to the tng users list:

Shouldn’t the following gedcom record 1 DEAT cause the living flag to be blank in TNG and override an import setting which specifies  if no birth date assume living? Is a death date necessary?

0 @I2990@ INDI
1 NAME Mary //
2 GIVN Mary
1 SEX F
1 DEAT

Ron

UPDATE: Darrin's response:

I think you have to have something after 1 DEAT, like 1 DEAT Y.

For some reason my Family Historian software stopped putting the 'Y' after.