Moving person notes to a new citation and linking to an event

#1 · 2025-05-30, 10:58 am

Hi

FTM24 is having trouble linking notes between it and Ancestry. So I want to create a citation rather than use notes. I love RM as I can query/change from SQL, but my SQL skills aren't top knotch: I'm learning though...

Searching on this site hasn't given me the exact SQL I require.

I have found this script

select personid, note from PersonTable
WHERE Note LIKE '%*%';

So that it pulls up all the relevant person notes.

I am thinking I should create a new source/citation in RM itself so that it appears in SQLite easily.

I have found this script

SELECT * FROM CitationLinkTable AS CL
JOIN EventTable AS E
ON CL.OwnerID=E.EventID
WHERE CL.OwnerType=2 --events;

To give me event citations. I would add in

and e.eventtype = the new number

Which would initially be blank.

So, now I need to move the results from my 1st SQL into the details of the eventtable.

I understand that it starts with

insert into eventtable ('EventType','OwnerType','OwnerID','FamilyID','PlaceID','SiteID','SortDate','IsPrimary','IsPrivate','Proof','Status','Details')

values

But how can I link the notes from SQL1 into the 2nd one without having to copy and paste the results into the value section?

I understand that personID =OwnerID, so I can re-use that. I am thinking that a temp table would be required, or a case option...

Any help would be greatly appreciated.

kevync has reacted to this post.

#2 · 2025-05-30, 9:58 pm

I'm not sure I understand your intended workflow, but here's my guess. I think you want to copy the note that shows up as a research note for a citation into the fact note for a fact to which the citation is linked. If that's the case, then you would need to copy CitationTable.ActualText to EventTable.Note using SQLite.

So could you maybe describe your intended workflow in terms of the RM user interface? What data from which RM screen needs to be copied to which other data in which RM screen? Armed with that information, I could probably produce a prototype script for you very quickly. You could use the prototype as a place to get started.

#3 · 2025-05-31, 8:29 am

Sorry for not being clear - it's clear in my head !!

Create a new source (probably called Heritage or something similar)
Note attached to a person with * in (I use the * to denote where in the list of ancestors that person is)
If YES, then move that person note into a resource note for a new citation under the source of Heritage.

Is that clearer?

#4 · 2025-05-31, 9:53 am

Quote from Ali Christie-Upton on 2025-05-31, 8:29 am
If YES, then move that person note into a resource note for a new citation under the source of Heritage.

And that new Citation would be linked to the Person? Or to a new Event for that Person (as seemed to be what you described in your original post)?

#5 · 2025-05-31, 10:11 am

I'm piggybacking on Tom's question. So it looks like the new note would be a person note rather than an event note. So it would be found in PersonTable.Note rather than in EventTable.Note. Is that correct?

Then, the new note would be copied to the research note for a citation for the person? I thought your original message mentioned a citation for an event? Either way, the field is CitationTable.ActualText. But then there is a link in the CitationLinkTable that can link to a person or to an event or to both.

Screenshots would be helpful.

#6 · 2025-05-31, 8:56 pm

Quote from thejerrybryan on 2025-05-31, 10:11 am
Screenshots would be helpful.

Uploaded images.

1 - shows where the person notes are

2 - shows the fact "Heritage" for that person

3 - details the master source

4 - shows the notes now copied into the notes section of this new citation

I'm hoping to

create the fact and attach to the relevant person
have the notes from the person ID moved (or copied then deleteed)
Have a citation created which is attached to the fact "Heritage" with the notes in the citation notes

Uploaded files: