Forskel mellem versioner af "Maintenance - Pooled Places with no Events"

Fra TNG_Wiki
Spring til navigation Spring til søgning
(new report when using Pooled Places)
 
(Text replacement - "enclose="div">" to ">")
 
(8 mellemliggende versioner af den samme bruger vises ikke)
Linje 6: Linje 6:
  
 
== Purpose ==
 
== Purpose ==
−
This article shows you how you can create an Admin Report that lists all [[Pooling Places|pooled places]] not linked to any event.  
+
This   article shows you how you can create an Admin Report that lists all   [[Pooling Places|pooled places]] not linked to any event.  
 +
 
 +
 
 +
'''<span style="color: red">NOTE this report is only if you have multiple trees and share or pool the Places between trees.</span>'''
 +
 
 +
 
 +
If you only have one tree you should use the [[Maintenance - Pooled Places with no Events]]
  
 
== Creating the Report ==
 
== Creating the Report ==
Linje 19: Linje 25:
 
*Copy the SQL code listed below and paste in the last text box (above the 'Save Report' button).  
 
*Copy the SQL code listed below and paste in the last text box (above the 'Save Report' button).  
  
−
[[File:Places-SQLcodebox4.jpg]]
+
[[File:Places-SQLcodebox4.png]]
  
 
*Save the Report.
 
*Save the Report.
  
 
== SQL Code ==
 
== SQL Code ==
−
<syntaxhighlight lang="SQL" enclose="div">
+
<syntaxhighlight lang="SQL" >
 
SELECT id, gedcom, concat('<a
 
SELECT id, gedcom, concat('<a
 
href=admin_places.php?tree=',gedcom,'&searchstring=',REPLACE(place,'
 
href=admin_places.php?tree=',gedcom,'&searchstring=',REPLACE(place,'
 
','%20'),'&exactmatch=yes&findplace=1&newsearch=1>',place,'</a>') as place,
 
','%20'),'&exactmatch=yes&findplace=1&newsearch=1>',place,'</a>') as place,
 
longitude, latitude, notes  
 
longitude, latitude, notes  
−
FROM tng_places  
+
FROM tng_places
−
WHERE place  
+
WHERE place
−
IN  
+
IN
−
( SELECT pl.place FROM tng_places AS pl  
+
( SELECT pl.place FROM tng_places AS pl
−
LEFT JOIN  
+
LEFT JOIN
−
(  
+
(
−
SELECT birthplace AS place FROM tng_people  
+
SELECT birthplace AS place FROM tng_people
−
UNION  
+
UNION
−
SELECT altbirthplace FROM tng_people  
+
SELECT altbirthplace FROM tng_people
−
UNION  
+
UNION
 
SELECT baptplace FROM tng_people
 
SELECT baptplace FROM tng_people
−
UNION  
+
UNION
 
SELECT deathplace FROM tng_people
 
SELECT deathplace FROM tng_people
−
UNION  
+
UNION
−
SELECT burialplace FROM tng_people  
+
SELECT burialplace FROM tng_people
−
UNION  
+
UNION
 
SELECT marrplace FROM tng_families
 
SELECT marrplace FROM tng_families
−
UNION  
+
UNION
−
SELECT divplace FROM tng_families  
+
SELECT divplace FROM tng_families
−
UNION  
+
UNION
−
SELECT eventplace FROM tng_events  
+
SELECT eventplace FROM tng_events
−
) AS p  
+
UNION
−
USING ( place )  
+
SELECT place FROM tng_cemeteries
 +
UNION
 +
select personID FROM tng_medialinks
 +
WHERE linktype = "L"
 +
) AS p
 +
USING ( place )
 
WHERE  isnull( p.place ) )
 
WHERE  isnull( p.place ) )
 +
 +
</syntaxhighlight>
 +
 +
'''Note''' that the following was added after the initial posting '''to detect Places in the Cemeteries tab'''le
 +
 +
<syntaxhighlight lang="SQL" >
 +
UNION
 +
SELECT place FROM tng_cemeteries
 +
</syntaxhighlight>
 +
 +
'''Note also that the following was added to the SQL to also exclude places to which Media record is linked'''
 +
 +
<syntaxhighlight lang="SQL" >
 +
UNION
 +
select personID FROM tng_medialinks
 +
WHERE linktype = "L"
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
== Viewing and Using the Report ==
 
== Viewing and Using the Report ==
−
After saving the report changes, click the 'Test' button. The resulting report should look like this:
+
After saving the report changes, click the 'Test' button to run the report  from the Admin screen. The resulting report should look like this:
 +
 
 +
[[File:Places-PooledReportResults.png]]
 +
 
 +
Click  on the link in the returned list to open the Place in another window or  tab, where you can then verify that no events are associated with the  place.
 +
 
 +
[[File:Places-PooledReportLinkedPlace.png]]
 +
 
 +
The  verify will confirm that no Events are linked to this Place, but since  the report does not check for Media linked to the place, it is possible  that a Media record is linked to the place as shown below.  In which  case you will need to relink that Media record before deleting the Place  record.
 +
 
 +
[[File:Places-PooledReportMediaLinkedPlace.png]]
 +
 
  
−
[[File:Places-PooledReportResults.jpg]]
+
You can then click the Delete icon to delete the place from your database.
  
 +
[[File:Places-PooledReportDeletePlace.png]]
  
 
== Related Links ==
 
== Related Links ==
Linje 82: Linje 121:
 
* Original SQL - Luke Weerts  
 
* Original SQL - Luke Weerts  
 
* Pooled Places SQL - Mark Wonson
 
* Pooled Places SQL - Mark Wonson
−
* See TNG Forum: http://www.tngforum.us/index.php?showtopic=3273 for a variety of 'tweaks' to the original SQL and that provided here.
+
*   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.
  
 
[[Category:Reports]]
 
[[Category:Reports]]

Nuværende version fra 7. feb 2021, 07:23


Purpose

This article shows you how you can create an Admin Report that lists all pooled places not linked to any event.


NOTE this report is only if you have multiple trees and share or pool the Places between trees.


If you only have one tree you should use the Maintenance - Pooled Places with no Events

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-SQLcodebox4.png

  • Save the Report.

SQL Code

SELECT id, gedcom, concat('<a
href=admin_places.php?tree=',gedcom,'&searchstring=',REPLACE(place,'
','%20'),'&exactmatch=yes&findplace=1&newsearch=1>',place,'</a>') as place,
longitude, latitude, notes 
FROM tng_places
WHERE place
IN
( SELECT pl.place FROM tng_places AS pl
LEFT JOIN
(
SELECT birthplace AS place FROM tng_people
UNION
SELECT altbirthplace FROM tng_people
UNION
SELECT baptplace FROM tng_people
UNION
SELECT deathplace FROM tng_people
UNION
SELECT burialplace FROM tng_people
UNION
SELECT marrplace FROM tng_families
UNION
SELECT divplace FROM tng_families
UNION
SELECT eventplace FROM tng_events
UNION
SELECT place FROM tng_cemeteries
UNION
select personID FROM tng_medialinks
WHERE linktype = "L"
) AS p
USING ( place )
WHERE  isnull( p.place ) )

Note that the following was added after the initial posting to detect Places in the Cemeteries table 

UNION
SELECT place FROM tng_cemeteries

Note also that the following was added to the SQL to also exclude places to which Media record is linked 

UNION
select personID FROM tng_medialinks
WHERE linktype = "L"

Viewing and Using the Report

After saving the report changes, click the 'Test' button to run the report from the Admin screen. The resulting report should look like this:

Places-PooledReportResults.png

Click on the link in the returned list to open the Place in another window or tab, where you can then verify that no events are associated with the place.

Places-PooledReportLinkedPlace.png

The verify will confirm that no Events are linked to this Place, but since the report does not check for Media linked to the place, it is possible that a Media record is linked to the place as shown below. In which case you will need to relink that Media record before deleting the Place record.

Places-PooledReportMediaLinkedPlace.png


You can then click the Delete icon to delete the place from your database.

Places-PooledReportDeletePlace.png

Related Links

Maintenance - Places with no maps

Maintenance - Places with no Events

Maintenance - Pooled Places with no Events

Maintenance - Places with identical geocodes

Maintenance - Places - Zoom & Place Levels

Places - Merging

Pooling Places


Acknowledgments

Hentet fra "https://tng.lythgoes.net/wiki/index.php?title=Maintenance_-_Pooled_Places_with_no_Events&oldid=65517"