Logo Home    Page list

Submit Your Problem #requestforhelp

Problem Queries
Create a New Page for your problem by editing this page, inserting a link from the Editor menu which will permit you to insert the text for the hyperlink and name your new page. After saving this revised page, click on your new link to create and edit your new problem page. On your new page, describe the problem and insert a link (Use the 'Insert Images or Files' button of the Editor bar) to the .rmgc or .ged file holding the sample data you are querying; this will allow you to upload the file. Discussion can then proceed around your page which can be revised as progress is made. Once solved, then your working query may be added to the RM4 Queries page.

Discussions

thejerrybryan

Page Number Field in Citations using Freeform Source Template

thejerrybryan 05 June 2011 17:56:23

Using the Freeform source template, citations include a field called Page Number. To see it, edit an individual, go to the Citation Manager sub-window, choose a citation, and edit the citation. The page number field is towards the bottom of the sub-window in the green area, and is just under where it says "source details".

I have been trying to figure out where this field is stored so that I may run an SQLite query against it, but I can't it. It almost has to be in the CitationTable, but the only possibility in the Citation Table is a column entitled "Fields". It display in hex in SQLiteSpy. I have a test database with only 5 citations. The page number is different in the 5 citations, and the hex data for Fields as displayed by SQLiteSpy is the same for all 5 citations. So the data must be somewhere else, or else SQLiteSpy is not displaying the entire contents of the Fields column in the CitationTable.

Jerry Bryan

ve3meo
ve3meo 05 June 2011 19:51:22

Use CAST to convert the BLOB to TEXT:
SELECT CAST(Fields AS Text) FROM CitationTable;

You will see a result like this for a Free Form template:
<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields><Field><Name>Page</Name><Value>Email 2 June 2008</Value></Field></Fields></Root>

Tom

craigg43

Add fact for color coding

craigg43 24 January 2013 03:12:45

I have a system of color coding all persons in my data base. I also have defined a fact call _color which I would like to add to each person to reflect their current color coding. Once that is done, I can use color coding for other purposes and still would be able to revert to my color coding system.
So I would like to be able to take all people and add a _color fact reflecting their current color coding.

ve3meo
ve3meo 24 January 2013 03:32:48

Adding a _color fact to store a current color code is one approach and lends itself to RM's Search Persons tool. Is there any reason to use this fact to restore the color-coding? That would require a complementary query.

A second approach that is actually easier to implement is to snapshot the color settings to a new table in the database. Not searchable within RM. Easy to restore the setting from the table with a complementary query.

Tom

JoyceAE

Listing of Ref No's for Spouses & Parents

JoyceAE 21 January 2014 22:51:51

What I want is a list (report) of everyone in my database with the following information:

Person's RefNo, Person's Name, Spouse's RefNo, Spouse's Name, Father's RefNo, Father's Name, Mother's RefNo & Mother's Name

For example:

Person's Spouse's Father's Mother's
RefNo Name RefNo Name RefNo Name RefNo Name
H456 George Harris H717 Ed Harris L954 Edith Lister No spouse
J001 Joyce Smith M124 Fred Brown J002 Bill Smith G056 Mary Good Spouse, father & mother
M546 Harry Mead M856 Roy Mead Only a father
R563 Edith Royce W067 Angela Woodman Only a mother

If the person does not have a spouse then that information should be blank. The same thing goes for either of the person's father or mother.

Can someone point me to an SQL query that might provide this kind of information? Right now, I am trying to maintain this information in an Excel spreadsheet but it is very labour-intensive and prone to errors and discrepancies. Any help on this would be greatly appreciated.

wegm
wegm 07 May 2015 20:25:34

This should do what you want:

-- Listing of Ref No's for Individuals and Spouses & Parents

SELECT
PersonTable.PersonID AS [PersonsRefNo],
CASE
WHEN ifnull(NameTable.Prefix, '') = ''
THEN ''
ELSE NameTable.Prefix || ' '
END || CASE
WHEN ifnull(NameTable.Given, '') = ''
THEN ''
ELSE NameTable.Given || ' '
END || CASE
WHEN ifnull(NameTable.Nickname, '') = ''
THEN ''
ELSE '(' || NameTable.Nickname || ') '
END || ifnull(NameTable.Surname, '') || CASE
WHEN ifnull(NameTable.Suffix, '') = ''
THEN ''
ELSE ', ' || NameTable.Suffix
END AS [PersonsName],
CASE
WHEN PersonTable.Sex = 0
THEN famSpouse.MotherID
ELSE famSpouse.FatherID
END SpouseRefsNo,
CASE
WHEN ifnull(spouseName.Prefix, '') = ''
THEN ''
ELSE spouseName.Prefix || ' '
END || CASE
WHEN ifnull(spouseName.Given, '') = ''
THEN ''
ELSE spouseName.Given || ' '
END || CASE
WHEN ifnull(spouseName.Nickname, '') = ''
THEN ''
ELSE '(' || spouseName.Nickname || ') '
END || ifnull(spouseName.Surname, '') || CASE
WHEN ifnull(spouseName.Suffix, '') = ''
THEN ''
ELSE ', ' || spouseName.Suffix
END AS [SpousesName],
famParents.FatherID AS [FathersRefNo],
CASE
WHEN ifnull(FatherName.Prefix, '') = ''
THEN ''
ELSE FatherName.Prefix || ' '
END || CASE
WHEN ifnull(FatherName.Given, '') = ''
THEN ''
ELSE FatherName.Given || ' '
END || CASE
WHEN ifnull(FatherName.Nickname, '') = ''
THEN ''
ELSE '(' || FatherName.Nickname || ') '
END || ifnull(FatherName.Surname, '') || CASE
WHEN ifnull(FatherName.Suffix, '') = ''
THEN ''
ELSE ', ' || FatherName.Suffix
END AS [FathersName],
famParents.MotherID AS [MothersRefNo],
CASE
WHEN ifnull(MotherName.Prefix, '') = ''
THEN ''
ELSE MotherName.Prefix || ' '
END || CASE
WHEN ifnull(MotherName.Given, '') = ''
THEN ''
ELSE MotherName.Given || ' '
END || CASE
WHEN ifnull(MotherName.Nickname, '') = ''
THEN ''
ELSE '(' || MotherName.Nickname || ') '
END || ifnull(MotherName.Surname, '') || CASE
WHEN ifnull(MotherName.Suffix, '') = ''
THEN ''
ELSE ', ' || MotherName.Suffix
END AS [MothersName]
FROM
PersonTable
INNER JOIN NameTable ON PersonTable.PersonID = NameTable.OwnerID AND NameTable.IsPrimary = 1
LEFT JOIN FamilyTable famSpouse ON (
(
PersonTable.Sex = 0
AND famSpouse.FatherID = PersonTable.PersonID
)
OR (
PersonTable.Sex = 1
AND famSpouse.MotherID = PersonTable.PersonID
)
)
LEFT JOIN NameTable spouseName ON spouseName.IsPrimary = 1
AND (
(
PersonTable.Sex = 0
AND famSpouse.MotherID = spouseName.OwnerID
)
OR (
PersonTable.Sex = 1
AND famSpouse.FatherID = spouseName.OwnerID
)
)
LEFT JOIN ChildTable ON ChildTable.ChildID = PersonTable.PersonID
LEFT JOIN FamilyTable famParents ON famParents.FamilyID = ChildTable.FamilyID
LEFT JOIN NameTable FatherName ON famParents.FatherID = FatherName.OwnerID AND FatherName.IsPrimary = 1
LEFT JOIN NameTable MotherName ON famParents.MotherID = MotherName.OwnerID AND MotherName.IsPrimary = 1
ORDER BY
PersonTable.PersonID,
CASE
WHEN PersonTable.Sex = 0
THEN famSpouse.WifeOrder
ELSE famSpouse.HusbOrder
END
--LIMIT 100
;

niall15

Home Person

niall15 26 May 2015 17:01:15

How do I find the Home Person who is not necessarily PersonId 1?
Cheers
Niall

ve3meo
ve3meo 26 May 2015 17:53:21

It is stored in record 1 of the ConfigTable in the XML "file" stored as a Blob in the DataRec field, e.g.,
<Root><Version>6000</Version><RootPerson>1</RootPerson><Las...

This query will let you inspect the XML data
SELECT *, CAST(DataRec AS TEXT) FROM `ConfigTable` WHERE 1 LIMIT 100;

Tom

boltongenes

SQLite query required that lists Citation Details in separate columns

boltongenes 29 November 2015 02:42:23

I have looked at the Source queries but find all the ones with Citation Fields in them list all the fields together in one cell. I would like a query that lists the citation fields in separate columns. As the list of citation fields can be different between any two sources the query would have to be limited to the user specifying one of the following:

1. which Source Template to run the query for eg Basic Ancestry Database Template ( where I have multiple Master Sources based on a single Source Template it would include all of those sources), or
2. which specific Master Source for eg My English Birth Certificates (from the Master Source List).
Fields required:

RecNo, Source Name, Surname, Given Name, OwnerID, Event Type, Cit Field 1, Cit Field 2, Cit Field 3,....etc, Citation Quality

Any help would be appreciated.

thanks
Anne-Marie

ve3meo
ve3meo 29 November 2015 05:17:41

That is likely beyond the capabilities of the SQLite language and would require a higher level language and processing. At least, it is beyond me. The problem is that the column names and values you want are in XML in one column and defined by a cell in another table. SQLite does not have any native XML support.

spR6sMtno

Database Schema

spR6sMtno 06 March 2016 11:14:06

Hi folks!

I work with DBs for a few years now, but the desing of the RM6 DB is not clear for me. And I'm not in genealogy - I do this for my wife.

Currently I work on a program that imports a RM DB and generates a graphical view of the relationships for direct ancestors. But I have trouble always detecting the correct parents, and this is where I need your assistance.

I have a name, and using nametable.ownerid, I go to the person. Then the problem begins. There are at least three different tables where I could look up the parents:
childtable ( ... where childtable.childid = persontable.personid) (I dont belive this table really helps me)
familytable ( select fatherid, motherid from persontable where familytable.childid = persontable.personid)
parenttable (select parentid from persontable where persontable.personid = nametable.ownerid).

Ok, enough confusion.

What's the correct way to look up a person's parents?

Thank you four your help!

ve3meo
ve3meo 09 March 2016 20:36:30

Just received notification of your message. Also am not able to access SQLite at moment so this is from memory and may not be accurate. Yes, PersonTable.PersonID = ChildTable.ChildID to find FamilyID WHERE RelFather =0 AND RelMother =0 to get birth parental couple. Then ChildTable.FamilyID = FamilyTable.FamilyID to get RINs of birth parents. Then FamilyTable.FatherID = NameTable.OwnerID WHERE NameTable.IsPrimary to get name of Father and likewise for mother.

Ksquared333

Merge unknown spouses

Ksquared333 05 February 2017 23:24:06

I have a good number of people where one of the spouses is unknown (usually the wife). When I imported to RM (from The Master Genealogist) a separate unknown spouse was assigned to each of the children. For instance, in TMG I had John Smith with 1 unknown wife and 5 children. In RM I have John Smith with 5 unknown wives, each with 1 child. I need a SQLite that will merge the children under the known spouse and combine all the unknown spouses.

Thanks!

Ksquared333
Ksquared333 19 February 2017 23:52:54

So I did that. I backed up my original and then restored the one you sent. I put the guy I was having trouble with in the Family View and it worked fine. Then I started checking other RINs from my list. It was all going fine until I tried 10019 and the same thing happened. When in the Family View I got a blank Family View. I closed the database and reopened it. Navigated to him through his grandparents and parents this time instead of using the Explorer, but I still got a blank Family View when I made him (10019) the primary. This isn't happening on databases that I haven't run the script on, so I don't think it's my system or my copy of RM. Would you mind having a further look around the database to see if it happens to you after you've looked at several. I think it happened on the 5th one I looked at. I looked at
14284, 13770, 13784, 10012, then 10019 and the blank Family View happened.

momakid

Alternate name problem

momakid 24 July 2017 13:00:10

In entering names in the suffix field for Alternate Name with name type Married, I entered some names like ( Doe Sally). With a space between the parenthesis and the D. I did the following query and got the people I wanted:
Select suffix
from nametable
where suffix like '( %';

A space between the parenthesis and the percent.

So I created the following update query:
update nametable
set suffix = '(%'
where suffix like '( %';

the problem is it wiped the names out and put (% in the suffix field.

What should the update query have been?
What query can I run to make it (Doe Sally) again?

When I run the following query:
Select suffix
from nametable
where suffix like '(%';

I get the following results:
(Doe Sally)
(%
(White Snow)

I want the entries with (% fixed. Of course it would be OK to update all suffix entries because I am sure there may be some typos.

If I wanted to change the suffix name to (Sally Doe) what would the update query be? I do have husbands who have no wives and wives that have no husbands. I do not want a suffix entry in those.

ve3meo
ve3meo 24 July 2017 19:52:54

Always make a copy or backup before you modify the database. That's the easiest way to get back to where you were so you can try again.

Use a SELECT to evaluate the expression you draft as an UPDATE before applying the UPDATE. For example, you would have seen that your UPDATE would produce the results you got from:
SELECT '(%' AS Suffix where suffix like '( %';

The % wild card is only a wild card, not a memory variable. You need to use the REPLACE() function:
SELECT REPLACE(Suffix, '( ', '(') AS Suffix where suffix like '( %';
If satisfied, then:
UPDATE NameTable SET Suffix = REPLACE(Suffix, '( ', '(') AS Suffix where suffix like '( %';

I'm unclear whether you are asking for a way to swap the first and last names already entered in Suffix or a way to populate the Suffix with the Given and Married Surname from the database records. The former would count on there being an existing Suffix entry having only two names with a space separating them. The latter would require that there be an Alternate Name fact with its type set to Married; alternatively, it would have to invent the Married Surname from the husband's Surname. Then there is the question of handling multiple spouses - multiple Surnames.

Tom

faliondg

Fix for marriages imported thru Ancestry TreeShare - invalid IDs cause "[Couple] were married..." sentences

faliondg 18 January 2018 21:12:03

I have 34 marriage records in my RM file with a sentence that begins with "[couple] married on..." instead of the spouse's names. All affected marriages were from records added in Ancestry and pulled into RM. I've narrowed down the problem in the data: there are marriage events for each of the spouses, but the OwnerType is 0, not 1, and the FamilyID is 0 instead of the FamilyID for the couple.

Below are the changes I developed. I've verified the data afterwards, by data dumps and printing the narrative reports. I had to do this as a multi-step process; there may be a more way to code the SQL but I'm sure this worked.


-- test table development below
-- create temporary table to hold problem records

DROP TABLE IF EXISTS TesttableEventTable ;

CREATE TEMP TABLE TesttableEventTable AS
Select *
FROM EventTable
where EventType = 300
and OwnerType <> 1
Order by Date;

-- this SQL Identifies the two rows where individuals also have a valid marriage record (these records to be deleted)
-- and stores the data into a temporary testing table:
DROP TABLE IF EXISTS TestEventsToDELETE
;
CREATE TEMP TABLE TestEventsToDELETE AS

select * from TestTableEventTable T
Where T.OwnerID in
(Select F.FatherID from FamilyTable F
INNER JOIN EventTable E on F.FamilyID = E.OwnerID
AND E.OwnerType = 1
And E.EventType = 300
)
UNION
select * from TestTableEventTable T
Where T.OwnerID in
(Select F.MotherID from FamilyTable F
INNER JOIN EventTable E on F.FamilyID = E.OwnerID
AND E.OwnerType = 1
And E.EventType = 300
)

;
-- END this SQL stores the above data into a temporary testing table


-- this SQL stores the IDs to be updated into a temporary testing table:
DROP TABLE IF EXISTS TestEventsToUPDATE
;
CREATE TEMP TABLE TestEventsToUPDATE AS

Select EventID
FROM TesttableEventTable
where (EventType = 300
and OwnerType <> 1)
AND EventID not in (Select EventID from TestEventsToDELETE)
GROUP BY Date
HAVING ( COUNT (Date) > 1)
ORDER BY Date;
-- END this SQL stores the IDs to be updated into a temporary testing table


-- this SQL creates a second test table to run tests against and compare to first:
DROP TABLE IF EXISTS TesttableEventTable2
;
CREATE TEMP TABLE TesttableEventTable2 AS
Select *
FROM EventTable
where EventType = 300
and OwnerType <> 1
Order by Date;
-- END this SQL creates a second test table to run tests against and compare to first


DROP table if exists TestEventsChangeData;

CREATE TEMP TABLE TestEventsChangeData
AS
select TETU.EventID,
TTET.OwnerID,
F.FamilyID
from TestEventsToUPDATE TETU, TestTableEventTable TTET, FamilyTable F
where TETU.EventID = TTET.EventID
And ((F.FatherID = TTET.OwnerID) OR (F.MotherID = TTET.OwnerID))
;


-- These three steps, run in order, solve the problem:


BEGIN TRANSACTION;

-- set OwnerID in EventTable to Family ID from FamilyTable
UPDATE EventTable

SET
OwnerID = (SELECT TestEventsChangeData.FamilyID
FROM TestEventsChangeData
WHERE TestEventsChangeData.EventID = EventTable.EventID)
WHERE
EXISTS (
SELECT *
FROM TestEventsChangeData
WHERE TestEventsChangeData.EventID = EventTable.EventID
)
;


-- set OwnerType in EventTable to 1 instead of 0
UPDATE EventTable
SET
OwnerType = (SELECT 1
FROM TestEventsChangeData
WHERE TestEventsChangeData.EventID = EventTable.EventID)
WHERE
EXISTS (
SELECT *
FROM TestEventsChangeData
WHERE TestEventsChangeData.EventID = EventTable.EventID
)
;

-- Delete remaining invalid records
Delete from EventTable
where EventType = 300
and OwnerType <> 1
;

COMMIT;

Trob2

Sources merged in error

Trob2 04 July 2018 19:55:27

I have accidentally merged two similar sources and now regret it!

This relates to a parish baptism, marriage and burial data for my local area. I set up separate free-form Sources for each of the three elements. Unfortunately I now have only two sources as I somehow managed to merge the baptism and burial templates.

For obvious reasons I only used the baptism Source for Birth or Baptism events and the burial Source for Death or Burial events.

It is a very simple job to recreate the two original source templates and as stated above it's quite simple to identify which source should be used for each event.

Would it be possible to create a query which would find instances of the merged source and rewrite them (using the relevant event as key) using the correct Source template - without losing any of the individual detail data?

Thanks in advance for any help you can offer.I'm using RM version 7.5.7.0 BTW.

ve3meo
ve3meo 05 July 2018 02:58:12

The regret stems from not having a backup just prior to the merge or you've done a lot of changes since then before realising the error of your ways... Familiar story!

You'll start by looking up the merged source Name in the SourceTable and noting the value of its SourceID. We'll call this variable "SourceIDold" in the SQL statements that follow; you will substitute the value where the name is written.

Likewise for the Name of the new Birth-Baptism source to sub for "SourceIDnew". (You don't need a new source for the Death-Burial source - just edit the merged source.

From the FactTypeTable, the Birth and Baptism types have FactTypeID of 1 and 7 respectively.

The CitationTable points each citation to a Master Source via SourceID and to a record in the EventTable via OwnerID when OwnerType=2.

The EventTable points to a record in the FactTypeTable via its EventType value.

So a list of all Citation records of the merged source for Birth-Baptism events can be found by this query:

DROP VIEW IF EXISTS BirthCitations
;
CREATE TEMP VIEW BirthCitations AS
SELECT CitationID FROM CitationTable C
JOIN EventTable E ON C.OwnerID = E.EventID AND C.OwnerType = 2
WHERE C.SourceID=SourceIDold --plug in your value for the merged source
AND E.EventType IN (1,7)
;

--Look under Views to see the resulting list.
--Then transfer the SourceID for this list to that of your new Birth-Bapt source with this statement.

UPDATE CitationTable
SET SourceID = SourceIDnew --plug in your value
WHERE CitationID IN (SELECT CitationID FROM BirthCitations)
;

That should have repointed the Birth-Bapt citations to the new Birth-Bapt source. Now you can edit the merged source in RM to be the Death-Burial source. You'll be editing only the Master Source (yellow) fields.

CAUTION - you will make a backup or a copy of your database first. I have tested these statements for syntax errors but not for actual results.

Good luck!

Tom

www.000webhost.com