Question with respect to Changing a Master Source to a Different Source Template #sources #sourcetemplates

Posted on 2014-11-142019-01-27 by thejerrybryan

I probably should post this more as a question than as a new wiki page, but here goes anyway.

I’m continuing apace with my project to convert all my sources into extremely split sources. As such, I ran into an interesting situation.

I now have several dozen obituaries that have been entered as extremely split sources. All of the extremely split obituary sources are using the built-in Free Form source template. When I need to make a new obituary source, I simply copy an existing obituary source and edit it a bit to reflect the new obituary source.I then memorize and paste the citation for the new obituary source in lots of appropriate places because an obituary typically mentions a fairly large number of people.

I’m very pleased with results. In particular, if I need to change something in an extremely split obituary source then the change is automatically applied to all its citations without having to chase down each of the citations. However, I have found myself wanting to make minor tweaks to each of my several dozen extremely split obituary footnote sentences. This is still a manual operation, and will only get worse as I add more obituary sources to my collection of extremely split sources. So I’m beginning to wish that I had created a source template for my extremely split sources. My source template would store all the data in the Master Source itself and nothing in the Source Details. With this convention in place, I could still make any needed changes just once for the Master Source and all the changes would immediately be applied to all the citations, and GEDCOM export would be compatible with third party software. But more importantly, by using a source template I could make certain minor tweaks just once to the source template, and the tweaks would be applied immediately (I think!) to all the extremely split Master Sources.

That raises two questions:

Is it really true that if I had started out using my own custom source template instead of the built-in Free Form one that tweaks to the template would be applied immediately to all the Master Sources using the source template?
Is there any straightforward way to convert my Free Form Master Sources for obituaries to templated Master Sources and thereby to avoid having to redo all my citations?

Thanks,
Jerry

Added 12/15/2013

I think I’ve managed to figure out a solution that I’m happy with, and as always Tom’s comments were very helpful. I didn’t really explain very well in my initial post but my ambitions are actually much more modest than creating a fully automated solution that will convert the source templates for all my extremely split obituary sources all in one go. I’m willing to convert them manually and one at a time. If I had thousands of them at this time, I might think differently, but I have exactly thirty-two of them to convert and that’s really not so bad to do manually. What I’m not willing to do manually is for each of my thirty-two extremely split obituary sources to create a new Master Source with a different source template because then for each of the thirty-two I would have to go through and do the memorize and paste thing for each of the many, many citations. So my goal is not to have to redo all the citations, which is really the issue which got me into extreme splitting of sources in the first place. So here’s the process.

Get the new source template as “just right” as possible. I’m not so worried about the exact format of the footnote, short footnote, and bibliography sentences as I am being sure that the template collects the correct data elements. The sentence structure can be easily tweaked. The data elements are not so easy to tweak. This will be my first user defined source template, so it will have TemplateID = 10000. But whatever the TemplateID of my new template is, make note of it.
From within RM, go to the existing source and make a temporary copy of the existing footnote, short footnote, and bibliography sentences – for example, in Notepad. I don’t even need to save the temporary copy in a file, just in the Notepad window. The reason for making the copy is that step #4 is going to have the effect of destroying the existing footnote, short footnote, and bibliography sentences.
Make note of the SourceID for the Master Source for which I’m about the change the template. This is actually much easier than doing a WHERE clause based on the name of the Master Source or anything like that.
Execute the following SQL: UPDATE SourceTable SET TemplateID = 10000 WHERE SourceID = 1403; (or whatever – the 10000 is the TemplateID from step #1 and the 1403 is the SourceID from step #3).
From within RM, edit the Master Source in question. Copy and paste the appropriate data elements I have saved in the Notepad window into the appropriate places in the Master Source. This is really easy to do manually, but would be hard to program in a fully automated fashion.
Repeat steps #2 through #5 for each of the extremely split obituary sources.
Celebrate. No citations are impacted by this process, so I’m done.

Jerry

Some relevant pages:
Source Template, A BETTER Free Form
Sources – Adventures in Extreme Splitting

Discussions & comments from Wikispaces site

Inline comment: “‍Is it really true that if I had started out using my own custom source template instead of the built-in Free Form one that tweaks to the template would be applied immediately to all the Master Sources using the source template? ‍”

ve3meo
04 September 2018 01:40:30

ve3meo Dec 14, 2013

Yes, but do not change the names of any fields that have been used by a source because those values will disappear.

ve3meo

Inline comment: “‍Is there any straightforward way to convert my Free Form Master Sources for obituaries to templated Master Sources and thereby to avoid having to redo all my citations? ‍”

ve3meo
04 September 2018 01:41:53

ve3meo Dec 14, 2013

From Free Form to !MyFreeForm3 is fairly easy as is conversion from lumped to ultimately split. But what you want probably requires high level programming, ideally with an XML interpreter and a regular pattern in your current sources. Give some examples.
ve3meo Dec 14, 2013

I had a second thought this morning and it is reinforced by you saying you did not want an automated solution. It’s along the lines of your procedure but with a major difference – no need to copy the footnote sentences to Notepad because it’s all done within RM. Basically it’s the same principle I used for conversion to !MyFreeForm3 but with an added switch. The new Footnote template contains the [Footnote] field in a switch that outputs it if non-empty; when [Footnote] is empty, it outputs all the other new fields and their bridging phrases and punctuation. A complementary approach would output the latter when any of the new fields is non-empty, rather than depending on the [Footnote] field, which would probably be better, allowing the original footnote sentence to remain intact in its field to be compared against the evolving new footnote sentence as the new fields are filled.
ve3meo Dec 14, 2013

Similarly, you would construct in the new template a switch around the [ShortFootnote] field in the Short Footnote sentence template; the Bibliography sentences template would have a switch around [Bibliography]. I assume you do not use [Page] at all, but, if you do, then it must be in the same part of the switch as [Footnote] or [ShortFootnote].
ve3meo Dec 14, 2013

Using the template Obituary/Newspaper item converted to ultimate split, I added the fields {Footnote], [ShortFootnote], [Bibliography] and [Page], the latter as a Source Detail as that’s what it is in Free Form. I revised the sentence templates to:
Footnote: <[ItemID], [Newspaper]< /[[TranslatedName]/]>, [PubPlace], [Date]<, [Details]><. [Annotation]>.|[Footnote], [Page].>
Short Footnote: <[Newspaper], [Date].|[ShortFootnote], [Page].>
Bibliography: <[Newspaper]. [PubPlace], [Date].|[Bibliography].>
i.e., just the original sentence template in each case with the addition of a switch to output the original Free Form fields. That would be your step #1,
I skip to your step #4 and then iterate #5 for each master source working entirely within the Edit Source window, copying elements from the original Footnote sentence into the appropriate fields until the new sentence looks right. At all times the original FF values are always available to compare against the developing sentences.

There could be another step – conversion of Free Form sources to Ultimate Split with a corresponding change in the new template, or that conversion could be done after the FF-to-template conversion
thejerrybryan Dec 14, 2013

The approach of adding [Footnote], [ShortFootnote], [Bibliography] and [Page] to the source template looks really promising to avoid all the nonsense with Notebad. I’ll give it a try before proceeding further. It may be tomorrow night before I get a chance to look at it. My newest grandson (three months old) is “helping” me decorate my Christmas tree tonight.
ve3meo Dec 14, 2013

Wonderful! I may be doing the same thing with my first grand child at 9 mos, next Christmas.
ve3meo Dec 14, 2013

Stimulated by this discussion, I have just conceived a solution to my wish for the ability to use templates to help in the drafting of Free Form sources and the same solution can be used in the opposite direction. It is based on having both the set of template fields and the set of Free Form-like fields in the template as above but with the addition of a non-printing field [ForceFF] acting as a control over which set is output. The revised sentence templates for this example are:
Footnote: <? [ForceFF]|[Footnote], [Page].|<[ItemID], [Newspaper]< /[[TranslatedName]/]>, [PubPlace], [Date]<, [Details]><. [Annotation]>.|[Footnote], [Page].>>

Short Footnote: <? [ForceFF]|[ShortFootnote], [Page].|<[Newspaper], [Date].|[ShortFootnote], [Page].>>

Bibliography: <? [ForceFF]|[Bibliography].|<[Newspaper]. [PubPlace], [Date].|[Bibliography].>>

If the ForceFF field is empty, then the sentences come from the template fields, if none are empty, else from the FF-like fields. If the ForceFF field has a value in it, the sentences come from the FF-like fields.
thejerrybryan Dec 14, 2013

I’m basically now to the point of following Tom’s suggested model, and I’m having great success. I added [Footnote], [ShortFootnote], and [Bibliography] to my obituary source template, and converted all my extremely split obituary sources to use the new template. The data is all there. Tom is correct that I don’t use [Page], but I don’t know how I would get at it anyway since I’m only dealing with the SourceTable and [Page] is in the CitationTable. In any case, with this approach my existing footnote, short footnote, and bibliography sentences show up just fine and the nonsense with Notepad is not required.

And like Tom, I’ve come to realize the great value of having data elements that are collected on the “input” side of the source template but which might never appear on the “output” side of of the source template (which is to say, in any of the footnote, short footnote, or bibliography sentences). For example (and as Tom says), such data may be able to serve as switches. Or right now while I’m fine tuning my obituary source template, I can quickly switch back and forth between an output sentence consisting just of one data element such as [Footnote] on the one hand, or else consisting of multiple data elements such as [DeceasedName], [Newspaper], [NewspaperDate] etc. on the other hand.

There are many problems with RM’s implementation of source templates, and one key problem is just that there are so many of them and users can’t figure out which one to use. For example, some of the simplest source templates surely have to be the ones for books, but why do there have to be seven different such templates and how is a user to figure out which one of the seven to use? I think they could all be combined into one. For example, one issue is that in the bibliography sentence the author must be listed last name first for proper alphabetic order, except for when the author is something like the Podunk County Genealogical Society. So there are two separate source templates for these two cases. It seems much better to me to have only one source template and to differentiate these cases with switches.
ve3meo Dec 14, 2013

I ran with the combining of Free Form into templates and developed a batch query on the page Source Templates – Versatile Free Form Hybrids. One potential problem with the idea of combining variants of a template into one is that the Edit Source window precludes access to the bottom of the list if there are more than a certain number of fields.

Inline comments

Names – Move Prefix to empty Suffix #names

Posted on 2014-10-302019-01-27 by Tom Holden

This little query responds to the desire expressed by RootsMagic Forums member Ksquared to relocate Titles of Nobility from Prefix to Suffix. This is where they ended up in the direct import of his TMG project to RootsMagic. It moves the prefix for each name to the suffix field, provided it is empty. If the suffix field is non-empty, the prefix is left in situ.

The query require a SQLite manager having the RMNOCASE collation, such as SQLiteSpy or SQLite Expert Personal. See SQLite Managers for download links.

Having opened the RootsMagic database with the SQLite manager, copy the statement below into the SQL editor and execute it.

After executing, use the RootsMagic menu File > Database Tools to check database integrity, if not OK then Rebuild Indexes and repeat.

UPDATE NameTable
SET Suffix = Prefix
    , Prefix = ''
WHERE
Prefix NOT LIKE ''  -- only those records with both non-empty Prefix
AND NameID          -- AND empty suffix
NOT IN
(
  SELECT NameID     -- records having a non-empty suffix
  FROM NameTable
  WHERE
  Suffix NOT LIKE ''
)
;

Names – Edit NameTable with SQLite Expert #names #alternatenames #editable #sqliteexpert

Posted on 2014-10-292022-04-28 by Tom Holden

A question by MaryLou1955 about swapping a Name with an Alternate Name prompted this look at an easy way to do it, especially for someone new to SQLite. This page starts by showing how one can answer MaryLou’s question by directly editing the RootsMagic database with a SQLite manager without needing to know anything about writing SQL statements. Then it introduces a simple SQLite script that can expedite future edits of the NameTable and introduce you to some very basic SQLite commands and expressions.

While I tend to use SQLiteSpy for most things, I find myself turning to SQLite Expert Personal more frequently than before and this is one of them. While its user interface is a bit more daunting to start with, it has features that ultimately make it more suited to this task – editing a subset of columns from a table. If you do not have it already, visit the page SQLite Managers and follow the links to download SQLite Expert Personal and the associated fake RMNOCASE extension.

Editing RootsMagic Database with no knowledge of SQL

Start SQLite Expert
File > Open Database
Browse to the folder holding the copy of your RootsMagic database (file extension .rmgc and, of course, you made a backup or this is a copy you have renamed)
Start typing the name of the RM database file in the File name field – a list should drop down. Select the one you want and click Open.
Right-click on the name of the database that has now appeared in the sidebar and from the drop-down menu select Load Extension. Browse to where you stored unifuzz.dll and select it. OK the default entry point in the pop out window
Click on the table name “NameTable” listed in the sidebar under the database name and then on the Data tab. The result should be similar to this:
You can start editing right now but there are some features you should explore to make it easier and safer:
1. There are a lot of columns so let’s reduce to the ones we are interested in. Right-click anywhere in the table display and select from the drop-down menu “Select columns”. Uncheck all those that are of no interest and use to your purpose. RecNo is simply the row number of the current result set and has nothing to do with RootsMagic’s Record Number. NameID is not to be touched and is invisible to you in RootsMagic. OwnerID is RootsMagic’s Record Number so check it but do not edit it! After selecting columns, you now have a more compact table to work with as in this screen:
2. There’s more: Click on the heading of any column (not on the little funnel icon that will show when the pointer hovers over the header) to sort alternately between ascending and descending on the values in that column.
3. And really powerful is that little funnel, the filter row below the headings and the filter row at the bot with the Cusize button. These are three complementary controls for filtering the results of any table or query to those of interest. For a simple example, I want to swap the primary Name and an Alternate Name for the person with RootsMagic RIN of 53. I simply type “53” into the filter row under OwnerID and get this:Now we see all of the names for Mary Haws-53 altogether and only her names. The NameType numbers represent Birth (2), Married (5) and Nickname (6). The IsPrimary number {1,0} is a True/False indicator. 0 or False means the name is an Alternate Name; 1 or True is the Primary Name. Only one of a person’s names should be marked 1 or Primary; any more disappear from RM screens. Much more complex filtering can be done without knowing any SQLite.
4. Let’s change the Primary Name from Mary Haws to Polly Haws. There are shortcuts but here is a procedure that works with all types of fields. Right-click on the Mary Haws IsPrimary cell to open the Text Editor on that field. Replace the “1” with “0” and OK. Note the ungraying of the little button with a checkmark in it situated above the results pane. The change you made has yet to be applied to the database file. Clicking on that “Post edit” button completes the edit as does simply selecting a cell in another row. Now we have no Primary Name and RootsMagic has a Person with only Alternate Names. So now we edit Polly Haws’ IsPrimary field from 0 to 1, Post Edit and, voila, RootsMagic now has Polly Haws as the primary name.
5. As with most Windows programs, there is more than one way to do editing. All the fields of one record can be accessed from an edit screen via the edit record control or by double-clicking on a cell. Experiment with a Play database to get to know the controls and become comfortable with the procedures.
After editing, when you return to RootsMagic, run its Database Tools: Integrity Check, if not OK then: Rebuild Indexes, repeat.

A SQLite script to setup the screen

If you have not written or run a SQLite script before, here is a simple one that will execute the reorganization of the table display into a more compact form sorted by OwnerID (RIN). Names-Edit_NameTable.sql

Download it to a place where you can readily find it.
Click on the SQL tab. Below it, there will appear one or more SQL panes with tabs named SQL1, SQL2, … If this is your first time, there will be one empty pane.
Either via the Main Menu > SQL or by right-clicking in the SQL pane, select “Load SQL script”, browse to and select the .sql file just downloaded. The SQL pane will now show the contents of the script.
To execute the script, be sure that you have the desired database selected in the sidebar, then click the “Execute SQL” button. You will see results similar to this:
You can reorganize the results using the same tools as for the Data display and edit the same way. Had the query involved any other table or contained anything other than the raw data from one table, it would be uneditable.
You will note that the order of columns is different from the Data display which, by default, shows the order in the NameTable’s definition. That is a consequence of the order set in the SQLite SELECT statement, which you can change by editing the script and re-executing. You can also change the order in either SQL results or Data displays by dragging the column headers left or right. You can add or delete columns in the script, save it and reuse later.

Where to from here?

I hope this proves to be an easy enough entry into a wider understanding of the RootsMagic database and how it can be viewed and manipulated using a free, but good, SQLite manager. For a powerful search tool and to gain some insight into the roles of the various tables that make up a RootsMagic database, look at Search – wayfinding from data tables to RootsMagic screens.

Future of SQLite Tools for RootsMagic wiki #admin

Posted on 2014-10-242019-01-27 by Tom Holden

Wikispaces terminated free subscriptions for non-education wikis on Oct 15, 2014. SQliteToolsforRootsMagic would have ended 30 days later unless we started paying Wikispaces $5/30days or $50/yr or migrate it to some other free hosting service. I could not imagine the work involved in transferring, unable to find any equivalent, free host. I asked if you were willing to contribute to keep it going here – 10 people x $5/person = one year. Several members responded generously to the call and a subscription has been purchased through to Nov 12, 2016 – details in the spreadsheet below.

Thank you, donors!

Tom

Action
On Oct 14, 2014, a PayPal donation button was added to the site and subscription taken for US$5 payments every 30 days via PayPal starting Nov 13, at the end of the free trial period. A spreadsheet to track donations and expenses was also added. On Oct 23, 2014 the Balance exceeded $50 and a one-year subscription was purchased. We can pre-purchase additional 12 month blocks if the balance permits or else revert to recurring payments until it does.With a balance of almost $50 on Oct 24, it seemed timely to close off the fundraising, and purchase another year. That’s done and fundraising for the next subscription period will reopen in October 2016.

What do we get for $5/mo in addition to continuing the wiki?

Storage capacity of 2GB instead of 0.5 GB. We currently use 0.1GB so we are still far from the Free limit.

What do you think we should do?

I am not an expert on this type of thing so have no idea as to whether free, easy to migrate to, alternatives exist. I am happy to be one of the “10” to help keep this site running; it is very useful.. MVS.

Database – Copy Master Lists to Shell

Posted on 2014-10-142019-01-27 by Tom Holden

A feature oft-requested that, as of version 6.3.3.2, RootsMagic still does not offer while other software does is the ability to create a new database with custom elements from an existing database. This script works around that shortcoming by copying what might be called “Master Lists” from a developed database to a shell database newly created by RootsMagic. It supersedes an earlier script developed for RootsMagic 4 and 5 Depopulate but keep Customs, Places, Sources, published in Dec 2011. (RootsMagic 7 added the feature File>Import Lists from another database which addresses most of what this procedure does. 2019-01-27 TH)

This new script is also a complementary process to the old one. The new one starts with a new database and copies records from the developed database. The old script took a copy of a developed database and deleted people et al from it.

The script copies from a developed RootsMagic database to a new empty database of the same version:

customized standard fact types
custom fact types
master sources
media items tagged to sources and places
addresses for repositories and contacts
custom source templates
user defined places
webtags for sources and places
user settings for File Options rev 2014-10-14

Requirements:
SQLite Expert Personal with unifuzz.dll extension loaded OR equivalent SQLite manager that supports runtime variables and fake RMNOCASE collation. See RMNOCASE – faking in in SQLite Expert, …
Usage:

Create a new empty database using RootsMagic; note the full path; close the database. rev 2014-10-14
Open the developed database in the SQLite manager (it will be known as “main” in the script);
Right-click on the name of the database in the sidebar and select Load extension; load unifuzz.dll;
Load and execute the SQL script in a SQL tab;
Enter the full path to and name of the shell database at the prompt;
On finishing the script, open the shell database with RootsMagic; run Database Tools > Rebuild Indexes. rev 2014-10-14
Close and reopen the database with RM to reset the start person and last person config settings. rev 2014-10-14

Database-CopyMasterListsToShell.sql

Depopulate but keep Customs, Places, Sources #database #places #placedetails #sourcetemplates #sources #facttypes #custom #media #roles

Posted on 2014-10-132019-01-27 by Tom Holden

This script deprecated in favour of Database – Copy Master Lists to Shell (2014-10-13).

Makes an empty Master from current database, preserving custom fact types and source templates, Places, Master Sources and associated Repositories and Addresses, and Place/Source Media. Written for RM4 with instructions to edit for RM5.
CustomDatabaseShell.sql
There are two ways to do this:

Use SQLite Developer with a fake RMNOCASE collation and execute the query directly on a .rmgc database. This should pose no risk as it adds nothing to the database. Moreover, RM5 is especially assured because it has the REINDEX Database Tool which will rebuild the indexes against the internal collation.
Use other SQLite managers with the queries on Convert Database to NOCASE to create a NOCASE clone of the database. Then run CustomDatabaseShell query to depopulate the database. This NOCASE clone is usable in RM and probably will not throw up any sorting or indexing issue with the English alphabet, any more so than would the original; however, other language alphabets may be problematic. It would be advisable to add a dummy person, assign every fact, source and place to him, and drag and drop to a new database to restore the structure to RMNOCASE.

-- CustomDatabaseShell.sql
-- 2011-12-09 ve3meo
 
/*
 DELETES all records from a DB3_NOCASE copy of a RM4/5 database
 (OR, with SQLite Developer and a fake RMNOCASE collation, all records
 from the .RMGC database file)
 except for Addresses, Config, FactTypes, GroupNames, Places, Roles,
 Master Sources, Source Templates and associated Media thus creating
 a Master database shell.
 NOTE THE UNCOMMENT INSTRUCTION FOR USE WITH RM5
*/
BEGIN TRANSACTION ;
DELETE FROM AddressLinkTable ;
--DELETE FROM AddressTable ;
DELETE FROM ChildTable ;
DELETE FROM CitationTable ;
--DELETE FROM ConfigTable ;
DELETE FROM EventTable ;
DELETE FROM ExclusionTable ;
--DELETE FROM FactTypeTable ;
DELETE FROM FamilyTable ;
DELETE FROM GroupTable ;
--DELETE FROM LabelTable ;
DELETE FROM LinkTable ;
DELETE FROM MediaLinkTable WHERE OwnerType NOT IN (3,5) ;
DELETE FROM MultimediaTable WHERE MediaID NOT IN (SELECT MediaID FROM MediaLinkTable) ;
DELETE FROM NameTable ;
DELETE FROM PersonTable ;
--DELETE FROM PlaceTable ;
/*
*** For RM5, UNcomment the following DELETE FROM ResearchLinkTable
*** For RM4, keep as comment
*/
--DELETE FROM ResearchLinkTable ;
DELETE FROM ResearchTable ;
--DELETE FROM RoleTable ;
--DELETE FROM SourceTable ;
--DELETE FROM SourceTemplateTable ;
DELETE FROM WitnessTable ;
COMMIT TRANSACTION ;

TMG-RM Convert TMG_ID to Record Number #tmg #rin

Posted on 2014-10-082019-01-27 by Tom Holden

Why?

RootsMagic 6.3.3.2+ imports the TMG_ID into the standard RootsMagic Reference No. fact type (REFN). While this fact type can be displayed after the name of the person in main views and reports, it cannot be displayed in the sidebar Index and in some other places. Some users would prefer that it did; the only number that does is the Record Number (RIN), which is also the fastest search mechanism in RootsMagic Explorer.

How?

This procedure substitutes the TMG_ID value from REFN for all references to PersonID or RIN, which can optionally be displayed after the name. RIN is the most universal ID used across RootsMagic displays and outputs. However, it is volatile when transferred or imported into other databases, unlike the Reference Number fact or the TMG_ID fact formerly created with imports using 6.3.3.1.

Update History

2014-10-08 revised to support imports using RootsMagic 6.3.3.2; no longer supports earlier versions.
2014-09-24 handles single or small number of changes with proportional time; previous version was only suited to changing all; logs potential conflicts and
warnings in temp table xRIN_TMGIDlog which should be opened if the trial query aborts in order to find and resolve the cause of the error; logs a warning if
multiple TMG_ID facts for same person. Log displayed if script completes without an abort.

N.B. Be sure to close the database from RM before running this procedure

Many tables are changed so the procedure may take quite a while on large databases.While speed optimisation has not been a focus,
a 5000 person database with 15000 events and 11000 citations was operated on for 32 seconds to change 98% of the records, 2 seconds to change a few.
TMG-RM_convertTMG_IDtoRIN.sql

Surplus Reference No. Facts

Now that the TMG_ID is in the RootsMagic RIN, there seems little reason to preserve the Reference Number facts. Fortunately, the import placed TMG Reference tags into a custom RootsMagic fact type “TMG_REF” and added only one Reference No. (REFN) fact to each person to hold the TMG_ID. So a simple SQLite statement can get rid of them all:

DELETE FROM EventTable WHERE EventType = 35 ;

Convert TMG_REF to REFN?

Now that it is no longer necessary to preserve the TMG_ID in REFN or to permit only that REFN for each person for the script to be successful, do we want to keep this custom fact type. Were it exported from TMG to GEDCOM, it would be with the REFN tag. There is probably some logic that it should go to that tag from RootsMagic as well. Moreover, some users may want to optionally display the Reference value after the person Name. Both of those functions are supported if the TMG_REF type facts are converted to REFN facts. A simple SQLite statement can do so:

UPDATE EventTable
SET EventType = 35
WHERE EventType =
(
  SELECT FactTypeID
  FROM FactTypeTable
  WHERE ABBREV LIKE 'TMG_REF'
)
;

Then you can delete TMG_REF from the Fact Type List.

TMG-RM Fact Sentence Tweaks #tmg #facttypes #roles #events #sharedevent

Posted on 2014-09-282019-01-27 by Tom Holden

After importing a TMG project, there is considerable cleanup to be done in the resulting RootsMagic database. There are differences in their respective sentence template languages that cannot translate well or could be translated better (improvement possible in the direct import process). This script addresses some of these outcomes and some unwanted artifacts that could have more to do with the TMG user’s style in customizing sentences in TMG. There are very likely more common problems that could be ameliorated by extensions to the script; post suggestions as comments on this page.

There are limits to what can be done within SQLite because it lacks regular expression search and replace within a field. A higher level language using a SQLite connection is required to go beyond SQLite’s own limitations. This script does use the REGEXP extension to find records having a field value that matches a regular expression but the REPLACE() function does not support regex for its search and replace, not even a wild card character; only explicit strings can be used as parameters.

The script processes all sentences associated with facts/events in RootsMagic:

the default fact type sentence for the principal;
the custom override sentence for the principal(s) for each fact/event;
the default role sentence for each sharer role of a shared fact type;
the custom override sentence for each sharer (witness) of a fact/event.

The functions performed include:

adds [PlaceDetails] to sentences having only [Place] (Direct import translated [L] to [Place])
adds :plain modifier to [Date], [Place], [PlaceDetails] if preceded by a common preposition to correct, e.g., “of in”; RootsMagic prepends a default preposition if the :plain modifier is not present.
reverses order in switch “<was|and [Spouse] were>” which RM expands to “was” regardless; this first part is considered true in the absence of a variable in it.
deletes “[L=English]” (revise the script to cover other unsuppressed language statements).
relaces “<|” with “< ” (RM would always output nothing; the first part is true in the absence of a variable)
juggles some instances of space characters surrounding switches to correct spacing. This covers some patterns; there are others it does not.

TMG-RM_Sentence_Tweaks.sql N.B. Requires a SQLite manager such as SQLiteSpy that supports the REGEXP extension; SQLite Expert Personal does not.

The script is not restricted solely to be used on databases imported from TMG. It may help with other databases, too. The caveat is that it, too, may have some unwanted results so it is important to keep the original and compare it to the tweaked one to conclude whether the benefits outweigh the collateral damage.

Events – Move Short Note to Description #events #tmg #notes

Posted on 2014-09-252019-01-27 by Tom Holden

Do you have a lot of empty Description fields for facts or events that you would like to fill with short contents from the corresponding Note field? This might be the case for certain fact types after an import from TMG, directly or via GEDCOM. Or maybe you have chosen to enable the Description field for a fact type after adding many with sentence-useful content in the Notes. This script may help you with that chore.

The query moves content from Event Note to empty but enabled Event Description if the note is 100 chars or less for all such events of a selected Fact Type. To select a Fact Type, you must use a SQLite manager that supports SQLite run-time variables, such as SQLite Expert Personal (SQLiteSpy does not). When the query is run, a box will pop out prompting for the entry of the Fact Type’s abbreviation. This is the label as seen in the Edit Person screen. By entering the wildcard character (%), all Fact Types having the Description enabled will be processed.

EventNoteToDescription-Move.sql

Some TMG users exploit its split-Memo capability in which the text is sub-divided by the “||” separator. It may be that a variant of this query would move only the first part (M1) to the Description field provided it is 100 characters or less.

Conversely, given that imported Descriptions may contain the separator, a query that would move everything after it from Description to Note might be useful.

Comments?

Discussions & comments from Wikispaces site

moving only part, based upon separator, would be awesome!

robertjm
25 December 2015 21:07:42

I’d love to be able to move only a part of the Note to different fields. When I first used TMG I made copious use of the Note field, with separators. Now, I’m finding I want to move stuff around. But, I want to be particular of what I move, and where. Having a rudimentary understanding of Sqllite, I’m not able to “role my own” right now.

ve3meo

ve3meo
25 December 2015 21:18:34

To be able to programmatically extract something from the Note field requires that it be delineated systematically.

Notes Invisible Revealed #notes #tmg #gedcom

Posted on 2014-08-232019-01-27 by Tom Holden

GEDCOM files imported from The Master Genealogist and probably other sources may exhibit disappearance of citations and notes for tags in the TMG Name Group. Citations and memos for tags from the Name group that export to the GEDCOM NAME tag will disappear from those that are the first or only NAME tag imported by RootsMagic. They are in the database – just in places you cannot get at through the current user interface. The loss of citations from GEDCOMs of other origins was addressed with SQLite queries in Citations Invisible Revealed. This page provides queries to address the Notes issue.

Notes, Invisible – List.sql Lists the persons and their invisible notes.

Notes, Invisible – Convert to General.sql Appends the invisible note from the primary name to the general note for the person and deletes the first.

Discussions & comments from Wikispaces site

Inline comment: “‍Is it really true that if I had started out using my own custom source template instead of the built-in Free Form one that tweaks to the template would be applied immediately to all the Master Sources using the source template? ‍”

Inline comment: “‍Is there any straightforward way to convert my Free Form Master Sources for obituaries to templated Master Sources and thereby to avoid having to redo all my citations? ‍”

Inline comments

Comment: Yes, but do not change the names of a…

Comment: From Free Form to !MyFreeForm3 is fai…

Editing RootsMagic Database with no knowledge of SQL

A SQLite script to setup the screen

Where to from here?

Why?

How?

Update History

Surplus Reference No. Facts

Convert TMG_REF to REFN?

Discussions & comments from Wikispaces site

moving only part, based upon separator, would be awesome!