Fact Type – Convert Census to yyyy Census and back #facttypes #census

This page and scripts respond to a wish posted by Jerry Bryan to the RootsMagic Forum, quoted in part:

… 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.

An example of the results from these scripts can be seen in the screenshot:

FactType - Convert Census to Year_Census.PNG
The script created custom fact types from the standard Census and Census (family) fact types by prepending any year found among all the census events in the database. It then switched each event from the standard fact type to the year-specific custom census fact type. Thus, the People View can have one or more of these yyyy-Census events in columns, four chosen for this screenshot. With multiple yyyy Census columns, holes in the census pattern and migration can be easily seen.

Note that People View shows Fact or Event data for only the Principal of an Individual (Personal) fact type; Family type events are not shown nor are events for persons that are ‘sharers’, i.e., having a role other than Principal.

The scripts make no distinction among census jurisdictions; a database with many countries may have the 1901 Census fact type for UK, Canada, etc. I thought of attempting to incorporate the Country in the name of the fact type but this is impractical given the variety of Place name practices among users and the range of jurisdictions.

Requires a SQLite Manager with an extension for a RMNOCASE collation sequence.


1. Execute once and once only if you intend this to be a temporary change.

2. To revert said temporary change to the original, one or more tables is stored in the database, named, e.g., “xCensusTypeTranspose”; you then select and execute the script in comments at the end of the script file and the special table(s) is dropped from the database after the events (and roles) have been restored to standard type.

3. If, after step 1, you add more standard events and re-execute, the reversion script can only return to the state prior to the last conversion. Only those yyyy-Census fact types that become unused through reversion will be deleted.*

4. A separate reversion script may be developed to convert all events of fact type name like “yyyy Census%” to standard Census types.

Two versions of scripts were developed. One covers both shared and unshared census events; the other can be used on a database having no shared census events and will take less time, notably on large databases.

FactType – Convert Census_shared to Year_Census.sql Use this script if you have shared Census events.
FactType – Convert Census_unshared to Year_Census.sql Use this script if you have only unshared Census events.

  • If People View has a column for a fact type that has been deleted, the next time you customize that view and exit from it, RootsMagic will appear to stall and, after a wait, throw an error message indicating that an index is out of bounds. To clear that problem, open “Customize this view” and select and Remove the blank row(s) at the bottom of the panel “Columns to display”. OK the customization and the error should be cleared.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.