Jump to content


Photo

Problem with source templates


  • Please log in to reply
9 replies to this topic

#1 robertjacobs0

robertjacobs0

    Advanced Member

  • Members
  • PipPipPip
  • 344 posts

Posted 23 September 2020 - 09:17 AM

When I converted from TMG to RM6 perhaps seven or eight years ago, the conversion brought a great many TMG source templates into RootsMagic. They appear in the source template list prefixed by _TMG_. I'm now in the process of trying to rationalize my sources and citations, which quite frankly, are all higgledy-piggledy. Part of the project is to delete unused TMG sources templates. (I'm up to RM7, the latest)

 

Whenever I attempt to delete one I get a warning that it is in use & that any sources of its type will be removed from the database. I know that some of them were never used but there's doubt about  many others. How can I find out whether they have or have not been used?

 

I understand that if I delete a template the sources of its type will be deleted. What happens to citations which utilized those sources?



#2 Bob C

Bob C

    Advanced Member

  • Members
  • PipPipPip
  • 268 posts

Posted 23 September 2020 - 10:29 AM

Lists>Source Lists>Select your source>Print will show everyone that has that source attached.



#3 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3955 posts

Posted 23 September 2020 - 10:30 AM

The citations will also be deleted along with the sources if you delete a source template which is in use.

You can find out which sources (and also which citations) are using a particular source template via a fairly simple SQLite script. But there is not a way to make this determination from within the RM7 user interface. Perhaps RM8 will improve the situation?

 

The best you can do in RM7 is to go the opposite direction. Scroll through the Source List making note of which template is in use for each Master Source. As you go through the Source List in this manner, you will not be able to see right away which Master Sources are actually in use. The only way to tell is the Lists>Source Lists > Select your source > Print mechanism, one Master Source at a time. Again, it's easy to find unused Master Sources with SQLite, but not so easy in the RM7 user interface.

 

Jerry



#4 robertjacobs0

robertjacobs0

    Advanced Member

  • Members
  • PipPipPip
  • 344 posts

Posted 23 September 2020 - 11:35 AM

Thanks, Jerry. It seems a tedious process from within RM7. Is there a suitable SQL script? I know how to look at the tables and run SQL scripts using SQLite expert, but I haven't constructed any programs. If I knew that RM8 would provide an easy way to do this I would wait, but part of the purpose of this clean-up is to deliver a nice clean database for the RM8 conversion when it comes.



#5 robertjacobs0

robertjacobs0

    Advanced Member

  • Members
  • PipPipPip
  • 344 posts

Posted 23 September 2020 - 12:14 PM

Lists>Source Lists>Select your source>Print will show everyone that has that source attached.

Thanks, Bob. That's useful, but the underlying problem is to find out which source templates are in use. Multiple sources can use the same template; if I want to convert them to use RM7 templates or even to delete the unused TMG templates it seems I have to do it manually, source by source if I am to remain within the confines of RM7.



#6 Bob C

Bob C

    Advanced Member

  • Members
  • PipPipPip
  • 268 posts

Posted 23 September 2020 - 12:34 PM

Have a look at this, it might be of some help

 

https://sqlitetoolsf...rce-list-query/



#7 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3955 posts

Posted 23 September 2020 - 01:46 PM

SELECT S.Name, COUNT(C.CitationID) AS Num_citations
FROM SourceTable AS S
         LEFT JOIN
     CitationTable AS C ON C.SourceID = S.SourceID
GROUP BY S.Name
ORDER BY Num_citations, S.Name;

This is to find how many times Master Sources are used. I realize that you are after how many times Source Templates are used. Give me a few more minutes for that one.

 

I have sorted by number of times Master Sources are used, so the unused ones will have 0 uses and therefore will sort to the front of the list.

 

Jerry



#8 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3955 posts

Posted 23 September 2020 - 03:27 PM

Here's a report on each Source Template and how many times each has been used. (I was away from the computer longer than expected. Sorry for the delay.)

SELECT ST.Name, COUNT(S.Name) AS Num_Sources
FROM SourceTemplateTable AS ST
         LEFT JOIN
     SourceTable AS S ON ST.TemplateID = S.TemplateID
WHERE ST.TemplateID >= 10000  -- report only on user defined templates
GROUP BY ST.Name
ORDER BY Num_Sources, ST.Name;  

I took the liberty of reporting only on user defined Source Templates, omitting any use of RM's built-in Source Templates. Most of them will surely be unused anyway because there are so many of them. I didn't filter just by your TMG Source Templates, but that would be easy enough to do if you are interested.

 

You will probably need to use both reports in combination. Use the second report to find Source Templates that are not used and use the first report to find Master Sources that are not used. It would be easy enough to combine the two reports into one, except that I couldn't quite figure out what I wanted such a combined report to look like. For example, you could have a Source Template that is used for a few Master Sources but all the Master Sources in question are unused. I think it would probably be better for you to be able to eyeball the issue of unused Source Templates separately from the issue of unused Master Sources. And of course if you delete all your unused Master Sources, that might cause some of the Source Templates that were used by those Master Sources to become unused. Let me know if you have any problems with these reports and if there are any tweaks that you might need.

 

Jerry



#9 robertjacobs0

robertjacobs0

    Advanced Member

  • Members
  • PipPipPip
  • 344 posts

Posted 23 September 2020 - 04:02 PM

Thanks so much, Jerry and Bob.

 

I just ran Jerry's source template script and found to my astonishment that all but four of the _TMG_ templates had been used at least once. Fifteen of them had been used only once. Those probably represent my earliest entered sources and the ones most in need of revision and replacement.

 

I wish I'd known more about all of this twenty years ago when I started in, first with Ultimate Family Tree, and then with TMG.



#10 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3955 posts

Posted 25 September 2020 - 07:31 AM

I need to post an addition here due to a longstanding and unresolved RM4 through RM7 design issue. Namely, both queries I posted for Robert can give slightly misleading results if there are any duplicate Source Templates in your database. For example, suppose a person in your database is using a user defined Source Template. Then suppose you Drag and Drop that person to a different database, make some changes to that person in the original database, and Drag and Drop that person to the other database a second time. You can merge that one person in the other database as a part of the Drag and Drop or you can merge that one person in the other database after the Drag and Drop. Either way, the user defined Source Template will be duplicated in the other database rather than being merged.

 

I hope this design issue is resolved in RM8. In any case, my query for Robert "merges" the duplicate Source Template for reporting purposes and therefore can fail to report on an unused Source Template. The duplicate Source Temple is not really merged, of course. The query just treats it as if it were merged. There is a very simple fix to the second query I posted, namely replace GROUP BY ST.Name with GROUP BY ST.TemplateID. The first query I posted for Robert has the same problem with respect to Master Sources. This simple fix for this query is to replace GROUP BY S.Name with GROUP BY S.SourceID.

 

I doubt that Robert encountered this problem when he ran my query because I doubt that he had any duplicate Source Templates or duplicate Master Sources. But because the _TMG Source Templates arose via an import from TMG into RM, who knows what might have happened with respect to duplicating Source Templates or Master Sources.

 

I get these duplicate Source Templates in my RM database regularly but infrequently. They arise as follows. RM7 does not support copying a fact from one person to another, but sometimes I do so anyway with the following convoluted process.

 

1. Drag and Drop the person who has the fact to a new and empty RM database.

2. In the new database, delete all the data from the person except for the one fact I wish to copy.

3. Drag and Drop the person from the new database back to the original database.

4. Merge the dummy person with the one fact I wish to copy into the person I wish to have the fact. Because I deleted all the data from the person being moved around except for the fact I wished to copy, the only cleanup needed after the merge is to delete the Alternate Name that arises.

 

Just as I hope that the problem in RM7 of duplicate Source Templates failing to Merge is fixed in RM8, I hope that RM7's inability to copy a fact from one person to another is fixed in RM8.

 

Jerry

 

 

P.S. Duplicate Master Sources can be merged in RM7. Duplicate Source Templates cannot be merged in RM7. I merge them manually with an SQLite update script, not the scripts I posted for Robert. In addition to automatic merging of identical Source Templates when Merging people, RM8 needs a Merge button for Source Templates like it has for Master Sources.