Intro

RootsMagic 4+ users have been seeking an improvement to Named Groups, namely “dynamic refresh” or, at least, a manual refresh button. The idea is that a group’s membership should be updated on demand following the rules that set it up in the first place. Currently, a group is static, requiring the user to edit the group, setting up the rules all over again.These sql scripts provide outboard manual refresh for specific groups.

This page collects queries designed to build membership lists for pre-named groups in RM4 databases. These queries search the subject database’s list of named groups for one whose name matches the query’s hard-coded or parameterised criteria. They delete the current members from the group and build a new set according to the programmed rules. In RM4+, selecting another group and then re-selecting the target named group updates its displayed list of members.

The queries can also include a list of persons’ Record Numbers to be unmarked from the group and a list to be marked (included) into the group. These may not be advisable for parameterised queries which use a common script as the manual inclusions and exclusions may need to be different.

For parameterised queries, SQLite Expert Personal or SQLite Developer is required. SQLiteSpy does not support run-time parameters.

Typical Usage

1. Backup your database before you do this every time until you are confident that it does no harm.
2. In RM4+, create a named group having the key words required by the script. Make sure first that you have no empty groups before creating a new one – there’s a bug in RM4 – and immediately add someone to your group before creating any other new ones.
3. Open your RM4 database with SQLite Expert Personal and load the sql script into the SQL editor.
4. Execute the query (F5)
5. You will be prompted to enter a value for one or more “Missing parameters”, e.g., a RIN, a Year, a Place….
6. When the query has finished, you will need to refresh the RM4 sidebar view of your group by selecting another group and then re-selecting it.

Named Group Scripts

NB- LabelID instead of LabelValue error corrected 2011-11-27 21:45 EST – Replace all downloaded scripts!
Ancestors Named Group: ancestors of a user-defined person, including all parental relationships
Census Needed – Named Group: persons whose lifetime probably spanned a user-defined Census Year and who had some event in the user-defined jurisdiction but not a Census fact for that year.
Named Group – Mark or Unmark List refresh: a one-button refresh of a group with arbitrary selection of persons to be marked or unmarked. Can be used after an outboard rule-based refresh on the same group (but not on any group refreshed from within RM4+).

Ranges vs Row per Person

The RM4+ GroupTable allows for group members to be defined in ranges of consecutive numbers between a StartID and EndID, thus shortening the table versus one row per member. These scripts make no attempt to use this capability so it consumes one row per member with EndID=StartID. I don’t know if there is any significant efficiency gained as a result of ranges.

Update 7 Dec 2012.
I finally figured out a way to generate the StartID-EndID ranges from a list of numbers. The query GroupTableWrite.sql listed below does the job and could be incorporated in the Named Group Scripts. Whether that is of any real advantage is even more in question now that I have examined the question a little more closely. In fact, one might argue that a table with one row per GroupID, PersonID with just one PersonID field could operate faster than the current design.

In a sample database of ~162,000 persons, ~54,000 contain the same surname string as part of the surname. Grouped into ranges, they are described by ~31,000 records in GroupTable, of which, ~23,000 are for just one person. 54,000 records is 74% more than 31,000 records but is hardly a deal-breaker – memory is cheap. Moreover, the 31,000 records store 4 values = 124,000 values while 54,000 records storing just 3 values = 162,000, only 31% more, closer to a draw on the memory front. What about the number of operations? For the current table design, there are 5 comparisons per GroupTable record for each person (PersonID >= StartID AND PersonID <=EndID) x 31,000 records X 162,000 persons = 25 billion comparisons. In the simpler concept, there is but one comparison per GroupTable record for each person (PersonID = MarkID) x 54,000 records X 162,000 persons = <9 billion comparisons. Furthermore, the single person per row structure is perfectly suited for a SQLite table index which may provide the biggest advantage as there is currently no defined index for GroupTable and it may not lend itself to SQLite’s AutoIndex.

As it turns out, writing the GroupTable with ranges is itself not majorly time-consuming, at least not for GroupTableWrite, which wrote the 31,000 records from the 54,000 record list (itself 4 subqueries of another previously defined group) in less than 5 seconds.

GroupTableWrite.sql

-- GroupTableWrite.sql
/*
2012-12-07 Tom Holden ve3meo
 
Creates StartID-EndID ranges for use in GroupTable from
a list of Marked PersonIDs and
Writes records to GroupTable
 
Replace marklist with the desired list, however generated, in
the form 1,3,479,480,481,...
 
In the Update GroupTable section,
replace the GroupID value with the one desired.
 
*/
 
DROP TABLE IF EXISTS tmpStartIDTable;
DROP TABLE IF EXISTS tmpEndIDTable;
CREATE TEMP TABLE tmpStartIDTable
AS
 
 SELECT PersonID AS StartID
 FROM PersonTable
 WHERE
 StartID IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 AND
 StartID -1 NOT IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 
;
 
CREATE TEMP TABLE tmpEndIDTable
AS
 
 SELECT PersonID AS EndID
 FROM PersonTable
 WHERE
 EndID IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 AND
 EndID +1 NOT IN
 (
-- marklist
SELECT PersonID FROM PersonTable, GroupTable
WHERE GroupID = 2
AND PersonID BETWEEN StartID AND EndID
  )
 
;
 
-- Update GroupTable
-- change GroupID value to match LabelValue for group from LabelTable
DELETE FROM GroupTable WHERE GroupID = 3
;
 
INSERT INTO GroupTable
SELECT NULL AS RecID, 3 AS GroupID, * FROM tmpStartIDTable StartID INNER JOIN tmpEndIDTable EndID
ON StartID.rowid = EndID.rowid
;