Maintenance - Places with no Events
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:
- 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).
- 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.
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
- Places that are linked to media but are not used in the people, families, or events tables, and
- 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:
- Maintenance - Places - Zoom & Place Levels - Places with no Zoom or Place Level set
- Maintenance - Places with no maps - Places with no Latitude or Longitude set
- Places Subject to Deletion Mod
- Original SQL - Luke Weerts
- See TNG Forum: https://tng.community/index.php?/forums/topic/3093-not-used-placenames-report/&tab=comments#comment-14401 for a variety of 'tweaks' to the original SQL and that provided here.