Census Plus International - SQL Reports

From TNG_Wiki
Jump to: navigation, search

Missing Census Transcripts

The following report can be created to find Census Events that do not have Census Plus International transcripts associated with them.

Note that you need to change the 32 in the tng_events.eventtypeID = '32' to match the eventtypeID associated with your Census Event

SELECT  
tng_events.persfamID,  
tng_events.eventtypeID,
tng_events.eventdate,
tng_events.eventdatetr
FROM tng_events
LEFT  JOIN cp_link ON tng_events.persfamID = cp_link.cpersonID AND  tng_events.gedcom = cp_link.gedcom AND RIGHT(tng_events.eventdate, 4) =  cp_link.censusYear
WHERE tng_events.eventtypeID = 32 AND cp_link.cpersonID IS NULL
ORDER BY tng_events.eventdatetr, tng_events.persfamID

Trouble Shooting Reports

The following TNG SQL Reports can be created in TNG Admin >> Reports in order to trouble shoot problems with the Census Plus International mod without require phpMyAdmin access.

Create the following SQL Reports in TNG but do not make them active so they are only available to the TNG Admin and can be edited and run when the Mod Developers request additional information.

Census Plus Base Record

Report name: Census Plus Base Record

Description: Display Census Plus base record using transcriptID is known. Update transcriptID in order to get a specific report

enter the following in the direct SQL SELECT statement

SELECT cpbaseID, transcriptID, location, series, censusYear FROM cp_base WHERE transcriptID = "dbid=9999&iid=36"

You will need to update the transcriptID = value based on the transcriptID or Created ID for the transcript with which you are having an issue.

If the Mod Developers want the output of this report, then click the Comma-separated CSV file button to capture the output

Census Plus Data Record

Report name: Census Plus Data Record

Description: Display Census Plus base record using the cpbaseID. Update the cpbaseID obtained from the Census Plus base record report in the SQL below in order to get a specific report.

enter the following in the direct SQL SELECT statement

SELECT cpdataID, cpbaseID, surname, givenname, relation, race, age, birthdate, birthplace, location, censusYear, lineno, cplineno, household FROM cp_data WHERE cpbaseID = 36

You will need to update the cpbaseID obtained from the Census Plus base record report in the SQL section of the TNG Admin > Report in order to get a specific report for the transcript with which you are having an issue.

If the Mod Developers want the output of this report, then click the Comma-separated CSV file button to capture the output

Census Plus Link Record

Report name: Census Plus Link Record

Description: Display Census Plus link record using the cpbaseID. Update the cpbaseID obtained from the Census Plus base record report in the SQL below in order to get a specific report.

enter the following in the direct SQL SELECT statement

SELECT cplinkID, cpdataID, cpbaseID, gedcom, cpersonID, eventtypeID, cpeventID, cplinktype FROM cp_link WHERE cpbaseID = 36

You will need to update the cpbaseID obtained from the Census Plus base record report in the SQL section of the TNG Admin > Report in order to get a specific report for the transcript with which you are having an issue.

If the Mod Developers want the output of this report, then click the Comma-separated CSV file button to capture the output

Census Plus TNG Event Record

Report name: Census Plus TNG Event Record

Description: Display TNG Event record when transcriptID or CreatedID is known. Update eventID in order to get a specific report using the cpeventID from the Census Plus Link Record report

enter the following in the direct SQL SELECT statement

SELECT eventID, gedcom, persfamID, eventtypeID, eventdate, eventdatetr, eventplace FROM tng_events WHERE eventID =

You will need to update the eventID = value based on the Census Plus Link Report

If the Mod Developers want the output of this report, then click the Comma-separated CSV file button to capture the output

Related Links

Census Plus International mod

Using_the_Census_Plus_Mod

Census Plus International - Options

Census Plus International - Find

Census Plus International - Add

Census Plus International - Edit

Census Plus International - Delete

Census Plus International - Import

Census Plus International - Update

Census Plus International - Link IDs

Census Plus International - Image

Census Plus International - Restore

Census Plus International - Styles

Census Plus International - SQL Reports

Census Plus International - Census Events

Census Plus considerations when using TNG only

Census Plus considerations when importing GEDCOM

Census Plus International and Family Census

Census Plus International - Source Citation

Media - Census Plus International mod