Report for Marriage with # of Children and hyperlinks

[KevyNC]

Hi,

Thought I would share this as I have not seen a report that did the result I was looking for.

First I created a view, then I create a report using that view.  You can adapt the Where clause to what you need: Branch,  Surname of Husband, Surname of Wife,  Decade of Marriage etc:

The result is this (my hyperlinks are RED if they have not been selected then green if previously visited)

Create View SQL (usually done in PHP myAdmin Panel) 

CREATE VIEW MarriageChildren AS 
SELECT 

CONCAT(
		'<a href="/familychart.php?familyID=',
		f.familyid,
		'">',
		'Family_Chart',
		'</a>'
		) AS GroupChart,

CONCAT(
		'<a href="/familygroup.php?familyID=',
		f.familyid,
		'">',
		'Group_Sheet',
		'</a>'
		) AS GroupSheet,

count(c.personid) as Children, 

CONCAT(
		'<a href="/famsearch.php?nr=500&fidqualify=equals&myfamilyid=',
		f.familyid,
		'">',
		f.familyid,
		'</a>'
		) AS FID,

CONCAT(
		'<a href="/getperson.php?personID=',
		f.husband,
		'">',
		f.husband,
		'</a>'
		) AS HusID,

h.lastname as SurnameHus, h.firstname as FirstnameHus,  
f.marrdate,		
CONCAT(
		'<a href="/getperson.php?personID=',
		f.wife,
		'">',
		f.wife,
		'</a>'
		) AS WifeID,
w.lastname as SurnameWife, w.firstname as FirstnameWife,  
f.marrplace, f.branch, f.marrdatetr,

Floor(Year(f.marrdatetr)/10)*10 AS Decade,
Floor(Year(f.marrdatetr)/100)*100 AS Century

FROM  tng_families as f  
LEFT  JOIN tng_people as h on f.husband = h.personid 
LEFT  JOIN tng_people as w on f.wife = w.personid 
LEFT  JOIN tng_children as c ON f.familyid = c.familyid 
GROUP BY f.familyid 
ORDER BY h.lastname, h.firstname, marrdatetr, w.lastname, w.firstname

 

Report

SELECT 
GroupChart, GroupSheet, Children, FID, 
HusID, SurnameHus, FirstnameHus, 
marrdate, 
WifeID, SurnameWife, FirstnameWife,  
marrplace, Century, Decade

FROM MarriageChildren
WHERE branch LIKE '%b%'
ORDER BY SurnameHus, FirstnameHus, 
marrdate, SurnameWife, FirstnameWife

 

[Chris Lloyd]

Thanks for that - very useful

[KevyNC]

Thanks Chris.

Yes ---  for those not familiar with Views they are basically a virtual table.  The advantage is that you an adapt the report(s) to what you need. (By Surname, By Branch and so on...) .  You can also reference the views if you use your own code /queries.

[klooster]

How do you implement views in TNG??

[KevyNC]

 

On 2/10/2021 at 2:03 AM, klooster said:

How do you implement views in TNG??

@klooster

Sorry I missed this -- I create the views from the cPanel phpMYadmin.  I have not tried creating them via a report.  You would also have to check "if exists" or similar  before attempt.

[klooster]

On 2/9/2021 at 8:57 PM, KevyNC said:

SELECT GroupChart, GroupSheet, Children, FID, HusID, SurnameHus, FirstnameHus, marrdate, WifeID, SurnameWife, FirstnameWife, marrplace, Century, Decade FROM MarriageChildren WHERE branch LIKE '%b%' ORDER BY SurnameHus, FirstnameHus, marrdate, SurnameWife, FirstnameWife

I did run the SQL in PhP Admin,

then created the report in TNG admin, bu I get no results, see printscreen

[KevyNC]

Hi Cees,

did you create a view ?  Did it show in  the phpMyadmin?

it should look like image below

M

make sure you have created a view. (also I believe its case sensitive with most )

CREATE VIEW MarriageChildren AS 
SELECT 

CONCAT(
		'<a href="/familychart.php?familyID=',
		f.familyid,
		'">',
		'Family_Chart',
		'</a>'
		) AS GroupChart,

CONCAT(
		'<a href="/familygroup.php?familyID=',
		f.familyid,
		'">',
		'Group_Sheet',
		'</a>'
		) AS GroupSheet,

count(c.personid) as Children, 

CONCAT(
		'<a href="/famsearch.php?nr=500&fidqualify=equals&myfamilyid=',
		f.familyid,
		'">',
		f.familyid,
		'</a>'
		) AS FID,

CONCAT(
		'<a href="/getperson.php?personID=',
		f.husband,
		'">',
		f.husband,
		'</a>'
		) AS HusID,

h.lastname as SurnameHus, h.firstname as FirstnameHus,  
f.marrdate,		
CONCAT(
		'<a href="/getperson.php?personID=',
		f.wife,
		'">',
		f.wife,
		'</a>'
		) AS WifeID,
w.lastname as SurnameWife, w.firstname as FirstnameWife,  
f.marrplace, f.branch, f.marrdatetr,

Floor(Year(f.marrdatetr)/10)*10 AS Decade,
Floor(Year(f.marrdatetr)/100)*100 AS Century

FROM  tng_families as f  
LEFT  JOIN tng_people as h on f.husband = h.personid 
LEFT  JOIN tng_people as w on f.wife = w.personid 
LEFT  JOIN tng_children as c ON f.familyid = c.familyid 
GROUP BY f.familyid 
ORDER BY h.lastname, h.firstname, marrdatetr, w.lastname, w.firstname