Maintenance - Places with no Events

From TNG_Wiki
Jump to navigation Jump to search

Purpose

This article shows you how you can create an Admin Report that lists all places not linked to any event. Note that there is a new TNGv10 Places Subject to Deletion Mod that, at least to some degree, supersedes this article.

Creating the Report

  • In Admin/Reports select the 'Add New' tab.
  • Enter a Report Name and Description:

Places-NewReport.jpg

  • Select the 'No' radio button against 'Active' (this ensures the report is viewable only by the system administrator).
  • Copy the SQL code listed below and paste in the last text box (above the 'Save Report' button).

Places-SQLcodebox3.jpg

  • BE SURE you replace ALL instances of 'mytree' with your tree ID (inside single quotes). OR, if you use are Pooling Places or have only one tree in your database, then you should be able to omit the tree and gedcom field references altogether.
  • Save the Report.

SQL Code

Please note that this SQL code does not include these LDS fields SELECT initplace FROM `tng_people
SELECT confplace FROM tng_people
SELECT endlplace FROM tng_people
SELECT sealplace FROM tng_children
SELECT sealplace FROM tng_families

SELECT id, gedcom, place, longitude, latitude, notes
FROM tng_places
WHERE gedcom = 'mytree'
AND place
IN (

SELECT pl.place
FROM tng_places AS pl
LEFT JOIN (

SELECT gedcom, birthplace AS place FROM tng_people
WHERE gedcom = 'mytree'
UNION SELECT gedcom, altbirthplace FROM tng_people
WHERE gedcom = 'mytree'
UNION SELECT gedcom, marrplace FROM tng_families
WHERE gedcom = 'mytree'
UNION SELECT gedcom, divplace FROM tng_families
WHERE gedcom = 'mytree'
UNION SELECT gedcom, deathplace FROM tng_people
WHERE gedcom = 'mytree' 
UNION SELECT gedcom, burialplace FROM tng_people 
WHERE gedcom = 'mytree'
UNION SELECT gedcom, eventplace FROM tng_events
WHERE gedcom = 'mytree' 
UNION SELECT gedcom, personID FROM tng_medialinks
WHERE gedcom = 'mytree' AND linktype='L'
UNION SELECT 'mytree' as gedcom, place FROM tng_cemeteries
) AS p
USING ( gedcom, place )
WHERE pl.gedcom = 'mytree'
AND isnull( p.place )
)

Note that the last two UNION clauses in the SQL statement above will find

  1. Places that are linked to media but are not used in the people, families, or events tables, and
  2. Places that are associated with a cemetery but not used in burial events.

So, you might wish to remove those two clauses from the SQL statement.

Viewing and Using the Report

After saving the report changes, click the 'Test' button. The resulting report should look like this:

Places-ReportResults3.jpg

Related pages

Acknowledgments

Headline text