Intro

We might want to modify a group that we created using one of the parameterised scripts by adding or deleting persons that our programmed rules just can’t catch. For example, a Census Needed group might have someone in it who immigrated to the census jurisdiction after the census year or emigrated from it beforehand. Its rules are not complex enough to filter out these persons. So as we work through a group, we will find that there are persons we would like to remove from the group without getting all fancy about the rules. This page shows how we can go about building a script that we can run in tandem to ‘unmark’ or ‘mark’ selected persons for a group.

WARNING: you may use this type of refresh only on groups fully refreshed by an outboard SQLite query or on empty groups BUT NOT on groups last built or modified by using RM4’s Mark Group/Unmark Group functions as it is possible that the deletion of a person from the latter group may actually remove a range of persons with consecutive Record Numbers.

Group Unmark List

This script could be one of several similar ones with different lists of people. If we have a Census needed #1871 Canada group and a Census needed #1900 Pennsylvania group, the two sets of people to unmark will be very different. So we cannot use a single list with runtime parameters. We will need an Unmark script for each one. Each script will have a corresponding name and hardcoded parameters by which it will select and operate on the correct group. Let’s make an Unmark script for the group “*Census needed #1871 Canada” which we have previously built using the script at Census Needed – Named Group.

This first block of code clears out the temporary table GroupParmTable and re-creates it for the Unmark operation:
NB- LabelID instead of LabelValue error corrected 2011-11-27 21:45 EST

DROP TABLE IF EXISTS GroupParmTable
;
CREATE TEMP TABLE IF NOT EXISTS GroupParmTable
AS
SELECT LabelValue AS GroupID, LabelName COLLATE NOCASE FROM LabelTable
WHERE LabelName LIKE '%Census needed%#1871%Canada%'
;

This table stores the GroupID corresponding to the Group Name that matches the search string ‘%Census needed%#1871%Canada%’ for use by later statements in the script.

The next statement contains the list of RINs that are to be deleted from the group and deletes them.

-- Unmark (list of manual unmarks)
 
DELETE FROM GroupTable
WHERE GroupID LIKE
(
SELECT GroupID FROM GroupParmTable
)
AND StartID IN (78,829) -- list of RINs to unmark, separated by commas
;
 
-- END OF SCRIPT

And that’s all there is to it. As you find more persons you want to delete from the group, you just edit the list of RINs between the parentheses … (78,829,97,12345,2,678) No need to keep them sorted. Putting it altogether, here is my working script: Census needed #1871 Canada – group Unmark list.sql

Group Mark List

A similar procedure for refreshing the ‘manual’ marking of a group follows…

It starts with the same first block of code as Group Unmark above to ensure GroupParmTable registers the group to which we want to add people.

If the list is to replace all that is currently in the group, then we need to clean out the current members of the group with this block of code:

-- delete all persons from the named group whose id is stored in the temp table set up at the start
DELETE FROM GroupTable WHERE GroupID =
(
SELECT GroupID FROM GroupParmTable
)
;

If, instead, the list is to be added to a non-empty group, skip the foregoing block. BUT, be warned that the following code does not prevent the duplication of existing persons in the group. That requires some extra measures TBA.

Now, we add the members in the list to the group:

-- Mark (list of manual marks)
INSERT INTO GroupTable (GroupID, StartID, EndID)
SELECT GroupID, MarkList.*, MarkList.*  FROM GroupParmTable
LEFT JOIN
(
SELECT 78       -- RIN to be marked
UNION           -- required for each successive RIN to be marked
SELECT 829      -- RIN to be marked
--UNION         -- uncomment for next RIN to be added
)
AS MarkList
;

That’s the end of the script. RINs 78 and 829 get added to the target group when the script is run against your database.

If the list got very large, it would be cumbersome to maintain and you can consider a couple of alternatives. One is to keep the list of RINs in a spreadsheet table and use formulas to produce the SELECT RINUNION statements, copy and paste into the script. You can also do it with a regular expression text editor such as PSPad ( Find: (d+) Replace: SELECT $1 UNION ). Another is to create a table of RINs either within your database or in a separate database and revise the procedure to SELECT DISTINCT RIN FROM databasename.tablename, replacing all the SELECT RIN UNION statements with this one.