How to identify built-in source templates

RootsMagic
,
1

Years ago, when I first started using RootsMagic, I used the built-in source templates. Then I developed a style of source citation that I like. Now I want to change how those original source citations appear. Since I can’t edit the templates that came with RootsMagic, how can I identify sources that use them so I can copy and modify them?

2

There is not a specific report you can run. There are two ways I can think of from within RM to find the information. One is to go into your source list and scroll down it. You can see the template name for your sources as you scroll. That’s a lot of eyeballing.

The other way from within RM has a similar problem. Namely, you can make a GEDCOM that includes RM’s Extra Details and then look at the GEDCOM with a text editor. You would have to look for things like “TID 22” or “TID 45” where the number is the Template ID Number.

You might be able to have a text editor smart enough to tell you what Template ID numbers are in your GEDCOM without looking at them one at a time by hand. Obviously many of the templates you use will be used multiple times. But unless you have some smartness in your editor, you would have to get back into eyeballing. Finally, the name of the template is not in the GEDCOM. Only the Template ID number is in the GEDCOM. So you would have to find them in the template list in RM by counting down from the beginning. All in all, not very optimal.

Finally, it’s pretty easy from outside of RM using SQLite if you have some programming skills. In fact, I’ll give you the necessary code. You would have to be able to install an SQLite manager to be able to run the code. Once you have the SQLite manager set up, this query would run in a tiny fraction of a second.

SELECT ST.Name
FROM SourceTemplateTable AS ST
JOIN SourceTable AS S ON S.TemplateID = ST.TemplateID
GROUP BY ST.Name
ORDER BY ST.Name
3

OK, thanks. I delved into SQLite and found a report on sqlitetoolsforrootsmagic.com that listed the sources and even showed the people using them. I was able to modify it for RM9 and run it in the recommended SQLite Spy app. Now I’m stuck on one simple thing. Is there a way to save the data from the report as a csv so I can make a check-off list in Excel?

4

OK, I figured it out. Edit Select All. Edit Copy. Even though it looks like it only selects one column, it does select all of them. For reference, below is my modified query. I don’t really understand why the original query had all the NULL AS columns, but I just left them in.

SELECT SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name',
   CASE WHEN SourceTemplateTable.TemplateID < 10000
     THEN 'Built-In'
     ELSE 'User-Defined'
   END AS 'TemplateType',
   SourceTable.Name COLLATE NOCASE AS 'Source Name',
   'Person' AS 'Citation Type',
   NULL AS 'Fact Type',
   NULL AS MRIN,
   NameTable.OwnerID AS 'RIN 1',
   NameTable.Surname COLLATE NOCASE AS 'Surname 1',
   NameTable.Suffix COLLATE NOCASE AS 'Suffix 1',
   NameTable.Prefix COLLATE NOCASE AS 'Prefix 1',
   NameTable.Given COLLATE NOCASE AS 'Given 1',
   NameTable.Nickname COLLATE NOCASE AS 'Nickname 1',
   NULL AS 'RIN 2',
   NULL AS 'Surname 2',
   NULL AS 'Suffix 2',
   NULL AS 'Prefix 2',
   NULL AS 'Given 2',
   NULL AS 'Nickname 2',
   COUNT(1) AS Records
  FROM SourceTemplateTable
    INNER JOIN SourceTable
      ON SourceTemplateTable.TemplateID = SourceTable.TemplateID
    INNER JOIN CitationTable
      ON SourceTable.SourceID = CitationTable.SourceID
    INNER JOIN CitationLinkTable
      ON CitationTable.CitationID = CitationLinkTable.CitationId
    INNER JOIN NameTable
      ON CitationLinkTable.OwnerID = NameTable.OwnerID
  WHERE SourceTemplateTable.TemplateID < 10000 AND NameTable.IsPrimary = 1
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17
5

OK, on closer inspection I think something is wrong in my joins. Most of the citations are joined to the wrong people. Any ideas on why this would be? The part that I modified had to do with adding in the CitationLinkTable.

6

The CitationLinkTable has a field OwnerType whose value specifies which table’s record id is pointed to by OwnerID. Your query links all of them to just the NameTable resulting in erroneous links.

I’m sure the site has more suitable queries but I think another oft-referenced there has some recent utilities that more directly address your goal. Have a look at @RichardOtter 's

7

The little query I created for you answered the question you asked very literally: which source templates are in use? So it didn’t try to identify where the templates were used. In fact, the query went out of its way to list each template that was used just once by using the GROUP BY clause. Also, you said you used the built-in templates, so the query assumed that was true and it didn’t even filter by TemplateID < 10000 to be sure it was looking only at the built-in templates. As has often been said, assumptions are the source of many mistakes.

I’m repeating what Tom said, but the query you are using is correct down to the point where it joins to the CitationLinkTable. The error is in the join that says “ON CitationLinkTable.OwnerID = NameTable.OwnerID”. That join is good only for joins to the main person record. It is not good for joins to facts or to families or to names. That’s why you got bad results for many of your citations. To fully identify where citations are really used, the query needs to become a bit more complex and to filter by the owner of each citation. I’ll see if I can whip up something for you. Since you have found the SQLite Tools for RootsMagic site, I’ll post the query there.

8

The slightly longer SQLite query I wrote to answer your questions in more detail has now been uploaded to the forum at SQLite Tools for RootsMagic

I was hoping Tom would answer your question about making a CSV from the SQLite report. I think there is a way, but I don’t know what it is. To tell you the truth, I usually structure my SQLite queries so that they identify work to do, and such that as I do the work in RM the to do items disappear from my SQLite query. For example, I will query 1850 census facts without media. As I add the media, the items will disappear from my query. I will continue until my query is empty. So I don’t need to make a CSV.

Also, even though I do use SQLite a great deal, I strive not to use it and to use RM’s own tools as much as possible. In the same spirit, my main “to do” list in RM is a refreshable group. I make a refreshable group of stuff that’s missing in some sense or other. As the missing stuff gets filled in, I can refresh the group and the group gets smaller. I keep going until the group is empty. To this end, I display my group in People List View filtered by the group, and People List view now has a way to refresh the group directly from the People List View screen without needing to go into the Groups screen. I find this way of working much more efficient than any sort of task list or to do list that I have ever tried, including those provided by RM itself.

9

There was no need. Quick learner @geomouchet discovered how with SQLiteSpy before I got to read the discussion.

What I thought might be more directed toward his goals rather than a list of sources or source templates was Richard’s RootsMagic Change source template that is in use: “ChangeSourceTemplate”

10

Thanks, it works great!

11

Thanks. Yes, switching source templates will be the next thing. I have some questions about how the tables link together. Does every citation in CitationLinkTable have an OwnerType 0 record that links back to the person? Or, for example, for a fact citation, does it link back to EventTable in order to get back to the person? Is there someplace that tells me what keys link each table together?

12

It’s revealed on Tom’s site. On the site, it’s Learn => RM Database Design.

From there, there are several formats available to see the table structures, For example, there are Database Diagrams if that’s the format you prefer. I prefer the spreadsheets you get by clicking on Data Definitions and then Data Definition spreadsheets. I prefer the DataDefsMstr tab. From there I can use the browser Find function to find anything I want to see very quickly. Absent the browser Find function, I might prefer going back to the Database Diagrams.

1 Like
13

While the most definitive listing is probably at SQLiteToolsForRootsMagic, I had a hard time finding it. I did find a set of lookup table data that’s not in any tables, but reverse engineered.

I have a work in progress listing that may fill in some details missing at the other site-

1 Like
14

Yes.

I agree with @thejerrybryan about the usefulness of the data definitions spreadsheet and with @RichardOtter about the difficulty of finding information in some cases on the sqlitetoolsforrootsmagic.com site which I administer as it is a blog platform sistered with a basic forum and has content spanning over 14 years and 6.5 versions of RM.

A post which does consolidate the table linkages set by OwnerType is this one by @PatJ
https://sqlitetoolsforrootsmagic.com/understanding-the-rootsmagic-8-database-ownership/

15

While “yes” is the correct answer, it’s a bit more complicated than that for couple facts such as Marriage and Divorce. For individual facts such as Birth or Death, the fact in the RM database links directly to the person. For couple facts, the fact in the RM database links to the couple and the couple links to the two people. You can see that effect in the SQLite script I posted on Tom’s site. In my experience, that effect always makes a script for facts a little tricky if the script is to include both individual and couple facts.

RM speaks of Marriage and Divorce as family facts, but I prefer calling them couple facts because the facts do not incorporate the children. This effect is especially noticeable for RM’s family facts such as Census (Family) where the name of the fact suggests that includes the children when it actually does not.

RM is effectively using the same data model that is used in GEDCOM for families. Because GEDCOM is so ubiquitous, this model is more or less the standard model for all of genealogy as far as linking spouses and children together into a family.

In GEDCOM, the relevant tags are FAM, FAMS, and FAMC. GEDCOM uses the term “family” rather than “couple”, and as already mentioned and for the most part so does RM. In GEDCOM, the FAMS tag is sort of “family spouse” tag and is used to link spouses together. The FAMS information is stored in RM’s FamilyTable. In GEDCOM, the FAMC tag is sort of “family child” and is used to link children to the family and in turn you have to go to the FAMS tag to find out who the parents really are. The FAMC information is stored in RM’s ChildTable.

There is nothing in this model that would prevent the children from being included in the fact for Census (Family). RM would simply have to look both in the FamilyTable and the ChildTable to identify all the relevant children. But by the same logic, a Marriage and Divorce fact would also include the children even though they didn’t get married or divorced. And it would be difficult and illogical to treat Census (Family) as a special case. For example, an RM user can create custom family/couple facts. Should such facts apply only to the couple or should they apply to the couple plus the children? So RM’s very sensible approach is to apply family/couple facts only to the couple.

1 Like
16

Being more of a neophyte in database design and admin, I find that the most useful definitions are those that clearly show how each value of a field branches to other tables. Here are direct links to posts that Jerry and Tom mentioned:

There is no rm9 equivalent that I am aware of. Am hoping that someone can post a link if I am wrong. While I don’t have the skills to lead such an effort, I am willing to help. Finally, I realize that my post is probably better suited for the forum on toms site - Forum – SQLite Tools for RootsMagic - and will start a forum conversation there.

1 Like