Jump to content


Photo

Census Fact Type to Year-Census and Back

Census Fact Type

  • Please log in to reply
17 replies to this topic

#1 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 20 November 2014 - 09:37 AM

Jerry, I took the liberty of starting a new topic, thinking that yours is a wish for a capability of viewing Census events within RootsMagic that can currently be realised only by workarounds of some sort. Is it your desire to be able to view census events in something like a pivot table? (With Place instead of the Count?)
 
Row Labels	        1830	1840	1850	1860
ALEXANDER, John-10		1	1	1
ALEXANDER, John-17	1			
FERGUSON, Martha-18		1		1
KEEBLER, Jacob-50	1		1	1
KEEBLER, John-51	1			
WOODS, Levi-16			1	

 

 

Tom, this discussion might better go on your SQLite wiki, but I'm thinking of going the other direction. Which is to say, for U.S. censuses I'm thinking of creating user defined fact types called census1790, census1800, census1810, etc. through census1940. These user defined fact types would be exact clones of the built-in census fact type. For example, there is nothing that would tie the census1790 fact type to the year 1790 except for the name of the fact type. Then I would have a script that changes all census events for the year 1790 to census1790 events for the year 1790 and so forth for each U.S. census year. Nothing would change in any report that would come out of RM.

 

Why would I do such a strange thing? Because then I could profitably put census events into People View. At the present time, it's a big waste of time to try to put census events into People View because census events are effectively duplicate events and only one such duplicate can be displayed in People View. With the new scheme, I could put any particular U.S. census year into People View.

 

And of course, on the back end, I would want to restore each of the census1790, census1800, etc. events back to standard census events. It's probably going to be a few weeks until I have time to put the script together (even though it's a pretty simple script). So maybe with a little luck (and if this idea tickles your fancy), you might have this code done within the next five or ten minutes. You are way faster with SQLite than I am. This idea is even a good candidate for RMTrix. And if it doesn't tickle your fancy or if you don't have time, I'll do it myself in a few weeks.

 

Jerry

 

 


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#2 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3591 posts

Posted 20 November 2014 - 02:54 PM

I guess the answer is yes and no about wanting to display the data in People View as if it were a pivot table. I can already do what I want for displaying the data in pivot table format using SQLite. But I would prefer to be able to display the data in RM's People View, which is why I want to change the census fact type to a user defined fact type.

 

For example, suppose I create a fact type called census1860 which is an exact clone of the built-in census fact type. The next step would be to use SQLite to change every census event whose year was 1860 to a census1860 event. At this point, narrative reports and the like would look absolutely no different than they do now because the sentence template for the census1860 events would be the same as for census events, and the date for census1860 events would be 1860 (duh!). But the new thing I could do would be to make "census1860 place" and/or "census1860 date" be columns in People View just like you can put birth date and birth place etc. into People View. So individuals with and without 1860 census events would stand out like sore thumbs in People View.

 

The same would be true of any other census year. I might or might not put census events for multiple census years into People View at the same time to get the pivot table effect. That's why my answer to your question is yes and no.

 

Having converted census events for the year 1860 into census1860 events for the year 1860, I might not ever want to restore the census1860 events back into census events. For my own use, there would be no adverse impact on leaving it that way. But I might want to change them back to export to other users or to export to third party software.

 

I still question whether I should have posted to the wiki instead of to here. But if this kind of facility were readily available, I bet lots of users would be interested in using it.

 

As far as this possibly being a wishlist item for RM, I'm definitely not wishing for a report of any kind, including not wishing for a pivot table report. I'm really wishing to be able to this kind of work completely within People View without having to run a report.

 

Jerry



#3 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 20 November 2014 - 03:13 PM

Something like this? I bashed together a rough script to do it. But wouldn't it be nice if RootsMagic had a Census View akin to this People View without the machinations of creating custom fact types. It would internally generate virtual fact types or whatever for the column selection. These virtual fact type names derived from the event data, as you imagined...

 

Fact_Type_Convert_Census_to_Year_Census.


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#4 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3591 posts

Posted 20 November 2014 - 03:23 PM

Yes, precisely.

 

Actually, there is a long standing wish list item (I submitted it myself) to support virtual census fact types that are the concatenation of the word "census" with the census year. If such virtual fact types were available and if they were supported by People View, then the problem would be solved instantly without any further ado in SQLite or anything like that.

 

Jerry

 



#5 Renee Zamora

Renee Zamora

    Advanced Member

  • Support
  • PipPipPip
  • 8470 posts

Posted 20 November 2014 - 04:41 PM

Confirming enhancement request is in our tracking system.


Renee
RootsMagic

#6 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 20 November 2014 - 05:06 PM

The script is now available at Fact Type - Convert Census to yyyy Census and back


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#7 Nettie

Nettie

    Advanced Member

  • Members
  • PipPipPip
  • 1647 posts

Posted 21 November 2014 - 02:17 PM

I use Excel for this purpose.  Personally do not want it in RM...


Genealogy:
"I work on genealogy only on days that end in "Y"." [Grin!!!]
from www.GenealogyDaily.com.
"Documentation....The hardest part of genealogy"
"Genealogy is like Hide & Seek: They Hide & I Seek!"
" Genealogists: People helping people.....that's what it's all about!"
from http://www.rootsweb....nry/gentags.htm
Using FO and RM since FO2.0 


#8 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 21 November 2014 - 03:27 PM

I use Excel for this purpose.  Personally do not want it in RM...

Nettie, maybe that's a good topic for the Tips forum - could you write up your procedure and take a screenshot?


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#9 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 21 November 2014 - 04:05 PM

Fact Type - Convert Census to yyyy Census and back now supports shared Census events.


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#10 Nettie

Nettie

    Advanced Member

  • Members
  • PipPipPip
  • 1647 posts

Posted 22 November 2014 - 07:22 AM

Tom, yes I can.  A table like you show earlier, is a simple table. Would not mean much to me. Mine is more complicated as I use this information for documenting what I have in my system and  is done before I enter the record into RM.  Is only Direct Line ancestors and includes all the data entry places that I put the census data in to evaluate and document what I have for each person/family.  A sample: is listed below but will do a full document, that I use in Classroom teaching. 

 

A Table in my Direct Line Surname Excel file is a tab for documenting:

Column 1 Given name; Col 2, 1790; Col 3, 1800 [ a column for each census year] etc...

In each column are these codes.  RM for data entry in RootsMagic, CM = CensusMate is a pre 1850 form for determining ages  [by John Haynies]; CT = Census Tracker [by Gary Minder]; J = jpg copy downloaded; HC = hardcopy copy; ON = OneNote for downloaded information in Surname Notebook on the census record; C&H = 1790 - 1940 Excel spreadsheet of the abstracted census record [by Gary Minder with my additions, which will be in the full document].


Genealogy:
"I work on genealogy only on days that end in "Y"." [Grin!!!]
from www.GenealogyDaily.com.
"Documentation....The hardest part of genealogy"
"Genealogy is like Hide & Seek: They Hide & I Seek!"
" Genealogists: People helping people.....that's what it's all about!"
from http://www.rootsweb....nry/gentags.htm
Using FO and RM since FO2.0 


#11 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 22 November 2014 - 01:43 PM

Nettie, if this is a manual practice involving no interface between RootsMagic and Excel (e.g. via a report), then it is more a research practices topic for a genealogy website rather than a RootsMagic usage topic.


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#12 Nettie

Nettie

    Advanced Member

  • Members
  • PipPipPip
  • 1647 posts

Posted 23 November 2014 - 11:21 AM

In my opinion, RM is not the place for it, but Facebook,  The Organized Genealogist is.    :)  or a blog or website it. 


Genealogy:
"I work on genealogy only on days that end in "Y"." [Grin!!!]
from www.GenealogyDaily.com.
"Documentation....The hardest part of genealogy"
"Genealogy is like Hide & Seek: They Hide & I Seek!"
" Genealogists: People helping people.....that's what it's all about!"
from http://www.rootsweb....nry/gentags.htm
Using FO and RM since FO2.0 


#13 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3423 posts

Posted 01 April 2015 - 12:11 PM

Actually, there is a long standing wish list item (I submitted it myself) to support virtual census fact types that are the concatenation of the word "census" with the census year. If such virtual fact types were available and if they were supported by People View, then the problem would be solved instantly without any further ado in SQLite or anything like that.

 

Jerry

 

Somehow I missed this thread first time round but being the closest to helping find and fill census information through the use of People View I chose this existing thread to share my own thoughts for discussion.

First thought which has nothing to do with filling census information is why can we not have custom tabs or multiple People Tabs which could be set up by users for differing needs. I always saw People View as a live report which I had long wished for so like designing a custom report please introduce the ability to design additional People View set ups on additional tabs.

Legacy has a Census option designed to find people missing from a particular census. This is something which fills a need but I am finding it flawed in operation. Rootsmagic do need to move on filling that gap through some means, in my opinion,  and I like Jerrys idea above. Anything that aids the research goal and speeds the process of improving quality of data must be a winner in the genealogy market.

What I do like in the Legacy option is the drop down list of 18 countries (Legacy 7.5) and once the census year is chosen it populates the correct date in most circumstances. I have always kept such reference information in a side file for my own benefit but it is nice to have this information contained within the program.

When you consider many countries with differing census years you can see the list like depicted above would become very large so I believe it would need to be Group specific like Any Fact > Place > Contains > Ireland to restrict the field of research. That immediately becomes a problem for the many US based users that have openly expressed that they prefer to leave United States out of their place notation so should RM then be looking at the Standardized Place for such matches?

So looking back at the screen capture of TomH, this is would be a perfect display within the confines of a country but if I were researching missing Ireland census entries, where there are only two currently available, after selecting the country of interest that is what I would want RM to be displaying.

Going back to US census entries, what about an individual who was 50 years old on the 1850 census return and did not appear on the 1940 census return, how should Rootsmagic display that data without appearing misleading? I would suggest background colour coding on People View itself of potential problem or missing data. On a potentially missing census People type view I would suggest user input of the country of interest and the maximum age of desired inclusions, Rootsmagic could then background highlight those census years potentially missing.

In 2010 I suggested the same background colour coding on People View for problem data like Text Dates, Places and Place Details still to be geocoded (see below), potentially missing census data could be visually highlighted in the same way. This would make People View into a much more powerful data reconciliation tool for all to improve the quality of their data.
 

people-view-highlight.png


We are all limited by our visions and abilities

Whilst we can borrow from the visions of others we cannot always deliver.

 

User of Family Historian 6.2.7, Rootsmagic 7.6.2, Family Tree Maker 2014 & Legacy 7.5

 

Excel to Gedcom conversion - simple getting started tutorials here

 

Root


#14 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3591 posts

Posted 01 April 2015 - 03:12 PM

What I have done since all this previous discussion took place was to convert all my census facts to user defined census facts which are year specific. For example, I have a census fact called "Census 1940" for the 1940 U.S. census, etc. The user defined census facts look the same in all reports as do regular census facts.

 

Except for the occasional person who was enumerated twice in the same census, I no longer have any individuals with multiple census facts. The year specific census facts work great in People View, color coding, Named Groups, custom reports, searching in RM Explorer, etc.

 

I used an SQLite script developed by TomH to accomplish the conversion.

 

Jerry



#15 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3423 posts

Posted 02 April 2015 - 06:16 AM


I used an SQLite script developed by TomH to accomplish the conversion.

 

I have also used the same script so firstly thank you to TomH.

 

I would still wish for the ability to create multiple additional People View Tabs to suit different needs and I still believe the Rootsmagician should be looking at ways to report potential missing census information in a live view such as People View as it is frequently requested here and elsewhere.

 

My little screen shot below shows my results using any fact > place > contains > wayne, looking at birth and death information and census which have been entered immediately highlights those which are missing in my test database, I have taken the liberty to further highlight some 1900 misses in red for better visual effect.

 

censussearch.png


We are all limited by our visions and abilities

Whilst we can borrow from the visions of others we cannot always deliver.

 

User of Family Historian 6.2.7, Rootsmagic 7.6.2, Family Tree Maker 2014 & Legacy 7.5

 

Excel to Gedcom conversion - simple getting started tutorials here

 

Root


#16 zhangrau

zhangrau

    Advanced Member

  • Members
  • PipPipPip
  • 1518 posts

Posted 02 April 2015 - 01:10 PM

Since I'm not SQL-savvy, I'm manually doing my conversion from the built-in Census fact to custom fact types. I have census data entered from 1624 to 1945 (although not every year !!) for the US, Canada, and UK. Doing the conversion manually is encouraging me to review each individual for other adjustments, including revising sort dates to improve the order of events in my reports. A big task, but I'm liking my results....



#17 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3591 posts

Posted 02 April 2015 - 01:33 PM

People View can be a great asset in doing the conversion by hand. If I were doing it that way, I would make the date field for the built-in census fact into one of the People View columns.

 

You would know you still had conversions for a person if their census date column in People View was not blank. You would know when you were done with the entire conversion when the date column for the built-in census fact was completely blank. You could sort on the census date column to move the non-blank ones to the top of the list.

 

I would further make extensive use of Named Groups with People View to break up my entire database into smaller and more manageable pieces of work.

 

Jerry

 



#18 zhangrau

zhangrau

    Advanced Member

  • Members
  • PipPipPip
  • 1518 posts

Posted 02 April 2015 - 08:27 PM

Yep, that's one reasonable approach.

What I'm doing is not so well-organized.

 

Some days I run a Report - List - selected for Census Date is less than x - where x is currently 1869

That gives me a list of a few hundred individuals, which I save in text format, then work my way through the list, making revisions to the database and deleting the people from the text file. Since most individuals where given a Census fact as part of a family, I work on two to four generations of the family branch - usually in Descendant view, and working my way from top to bottom of the list.

 

Some days I don't feel like creating a list, so I just work sorta randomly on family branches of current interest.

 

Other days, I make the Census to CensusNNNN revisions as I'm working on some other line of research.

 

I have about 15K individuals that still need the Census revisions, so I'll be at that for quite some time... but that's OK, I don't seem to have a deadline...