Jump to content
TNG Community
MichaelM

* SOLVED* List people having same events

Recommended Posts

MichaelM

I'd like to list all persons who were marked as "emmigrants" as a personal event.
Same might apply to "religion" or "profession".

I can use a custom event type "Emmigration" and fill out   emmigration date, location and remark but I do not know how to filter persons having this event.

May be I have tomatoes on my eyes..

 

 

Background:
In 1852    about 458 Individuals (1/8 of the population at this time) left my hometown to Amerika;
maybe a lot of them have common pedigrees with my family..

 

Share this post


Link to post
Share on other sites
manofmull

Michael

From the Find menu, search people but leave all fields blank.

Scroll to bottom left Other Events. Click on arrowhead link to display and find your custom event.

In the Fact field, select "exists" and then Search

Share this post


Link to post
Share on other sites
MichaelM

Thank You   Man of Mull,

that's what I was looking for.

Do know, how to discover the SQL behind ?
Then I could create a more extented report (with emmigration date, emmigration target..)

Michael

 

Share this post


Link to post
Share on other sites
MichaelM

Solved  * Solved * Solved

I could help me with report editor

Thanks

Share this post


Link to post
Share on other sites
manofmull
1 hour ago, MichaelM said:

Thank You   Man of Mull,

that's what I was looking for.

Do know, how to discover the SQL behind ?
Then I could create a more extented report (with emmigration date, emmigration target..)

Michael

 

Michael

Sorry no, I cannot compile reports.

I'm now too old and too busy to learn!

Share this post


Link to post
Share on other sites
Robin Richmond

To build a SQL query that displays the names of people who have a given custom event, you probably want to start with the Events table, and link from it to the People table and, significantly, to the Eventtypes table, which translates between the numeric eventtypeIDs that the Events uses into their tags (RESI, OCCU, EMIG, EDUC, etc.). And even if you have one tree in your database, it is turns out to be fairly important to refer to the TreeID (the field named 'gedcom') in most tables.

I'll also say up front that the TNG reporting tool can give you more information than you asked for. For instance, if you give it a treeID (the gedcom field) and personID, it will always turn the personID into a hyperlink to the person's record.  And I think that is the same circumstance in which it will give you a birth date even if you don't ask for it.

I'll give you a few examples, starting with a simple one that looks for the Probate event, which is likely to occur at most once in a person's record. The field ee.display is the event label associated with a tag, e.g. "PROB" => "Probate"

SELECT ee.display, e.gedcom. e.personID, p.lastname, p.firstname, e.eventdate, e.eventplace
FROM tng_events e
INNER JOIN tng_eventtypes ee ON ee.eventtypeID = e.eventtypeID
INNER JOIN people p ON p.personID=e.personID AND p.gedcom=e.gedcom
WHERE ee.tag = "PROB"
ORDER BY lastname, firstname

Or you could sort by probate date by changing "ORDER BY lastname, firstname" to "ORDER BY eventdatetr".
(The event date field can have modifiers such as ABT, BEF, AFT, and they get in the way of sorting, so  most date fields in TNG have an associated sortable date field with the suffix "tr".  BEF 1850, ABT 1850, and AFT 1850 will be sorted as you would hope.) The query just above will always display the "display date", even if it is sorted by the "sortable date".

Now, let's take it to another level with an event such as RESI (residence), since people can have multiple RESI events. If you just plug "RESI" into the query above, you'll get residence events, and you can sort them by name and then date (lastname, firstname, eventdatetr) or by date and then name (eventdatetr,lastname,firstname).  But lets focus on residence events from the 1920 census (where the event date is "1920", and the sortable event date would be "1920-00-00")

SELECT ee.display, e.gedcom. e.personID, p.lastname, p.firstname, e.eventdate, e.eventplace
FROM tng_events e
INNER JOIN tng_eventtypes ee ON ee.eventtypeID = e.eventtypeID
INNER JOIN tng_people p ON p.personID=e.personID AND p.gedcom=e.gedcom
WHERE tag = "RESI" and eventdate="1920"
ORDER BY lastname, firstname

Or Residence events at "Chicago, Cook County, Illinois, USA"

SELECT ee.display, e.gedcom. e.personID, p.lastname, p.firstname, e.eventdate, e.eventplace
FROM tng_events e
INNER JOIN tng_eventtypes ee ON ee.eventtypeID = e.eventtypeID
INNER JOIN tng_people p ON p.personID=e.personID AND p.gedcom=e.gedcom
WHERE tag = "RESI" AND eventplace = "Chicago, Cook County, Illinois, USA"
ORDER BY lastname, firstname

It is important to note that, practically speaking, you have to search for placenames exactly as they are in the database. If you have USA-centric database in the form "Chicago, Cook, IL", well, you'll have to specify that value.  And if you have different representations for Chigago in different records, well, you them one at a time or get more complex as in

WHERE tag = "RESI" AND eventplace IN ("Chicago, IL", "Chicago, Cook County, Illinois") etc

or, since Chicago is a pretty distinctive placename, you could ar

WHERE tag = "RESI" AND eventplace LIKE "Chicago%"

And, you can capture every placename within Chicago if you add another percent sign, like this

WHERE tag="RESI" AND eventyplace LIKE "%Chicago%"

 

I'll describe an obnoxious set of special cases in another post

 

Share this post


Link to post
Share on other sites
Robin Richmond

Unfortunately,  not all eventtypes can be represented uniquely by a tag. When my source database feeds me Arrival events, the have the tag "EVEN" and and a "type" attribute of "Arrival".  You can display the Eventtypes at Admin>>Custom Event Types to see exactly how they are represented interally.

To identify Arrival events, we have to do this:

SELECT ee.display, e.gedcom. e.personID, p.lastname, p.firstname, e.eventdate, e.eventplace
FROM tng_events e
INNER JOIN tng_eventtypes ee ON ee.eventtypeID = e.eventtypeID
INNER JOIN tng_people p ON p.personID=e.personID AND p.gedcom=e.gedcom
WHERE tag = "EVEN" AND ee.description = "Arrival"
ORDER BY lastname, firstname

Note that this query uses the table alias on the eventtype description field, because "description" is a commonly-used fieldname. I'm pretty sure that none of the other tables in this particular query use it, but its just safer to be specific. And there is an argument for using the table alias on every field reference, as I did in the SELECT clause.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×