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:

#7 · 2025-06-01, 11:18 pm

Quote from thejerrybryan on 2025-06-01, 11:18 pm
My apologies for the delay in getting back to you. This is going to be much more complicated than I thought, and I don't think I can quickly put together a prototype script for you. But let's look at the issues. And thank you for the screen captures. They were very helpful.
It appears as if all your newly created citations will be linked to the same source. That simplifies things a bit.
You will have to add a new row to CitationTable for each Person.Note. As a part of adding a row to any table, you have to define a value for each column, even the value is NULL. Your row to be added will need to set CitationTable.SourceID to be equal to SourceTable.SourceID for your Heritage source. If I am understanding your process correctly. the row to be added to CitationTable will also need to have CitationTable.Actual text to Person.Note. I would do the setting of the data values at the time of the INSERT rather than inserting with null data and then adding the data later.
You will have to add a new row to EventTable for the Heritage fact. It will need to have EventTable.EventType set to the same value as FactTable.FactTypeID for the Heritaage fact, it will need to have EventTable.EventType = 0 and it will need to have EventTable.OwnerID = PersonTable.PersonID. In addition, and I think you are also putting the note here, so it will need to set EventTable.Note = PersonTable.Note. Again, I would do the setting of the data values at the time of the INSERT rather than inserting with null data and then adding the data later.
Finally, you will need to add a new row to CitationLinkTable to link the newly added citation to the newly added fact. I think this is the trickiest part of the script. That's because you will need to set CitationLinkTable.CitationID to the value of CitationTable.CitationID for the citation you just added, and you won't know the CitationID until after you have added the new row to the CitationTable. Similarly, will need to set CitationLinkeTable.OwenerID to the value of EventTable.EventID for the row you just added to the EventTable and you will not know the value of EventTable.EventID until after you have added the row to the EventTable. Finally, you will need to set CitationLinkTable.OwnerType = 2 because the citation is for a fact.
I think if this as a pretty non-trivial script. It would take me quite a long time to develop it and to test it properly.

My apologies for the delay in getting back to you. This is going to be much more complicated than I thought, and I don't think I can quickly put together a prototype script for you. But let's look at the issues. And thank you for the screen captures. They were very helpful.

It appears as if all your newly created citations will be linked to the same source. That simplifies things a bit.

You will have to add a new row to CitationTable for each Person.Note. As a part of adding a row to any table, you have to define a value for each column, even the value is NULL. Your row to be added will need to set CitationTable.SourceID to be equal to SourceTable.SourceID for your Heritage source. If I am understanding your process correctly. the row to be added to CitationTable will also need to have CitationTable.Actual text to Person.Note. I would do the setting of the data values at the time of the INSERT rather than inserting with null data and then adding the data later.

You will have to add a new row to EventTable for the Heritage fact. It will need to have EventTable.EventType set to the same value as FactTable.FactTypeID for the Heritaage fact, it will need to have EventTable.EventType = 0 and it will need to have EventTable.OwnerID = PersonTable.PersonID. In addition, and I think you are also putting the note here, so it will need to set EventTable.Note = PersonTable.Note. Again, I would do the setting of the data values at the time of the INSERT rather than inserting with null data and then adding the data later.

Finally, you will need to add a new row to CitationLinkTable to link the newly added citation to the newly added fact. I think this is the trickiest part of the script. That's because you will need to set CitationLinkTable.CitationID to the value of CitationTable.CitationID for the citation you just added, and you won't know the CitationID until after you have added the new row to the CitationTable. Similarly, will need to set CitationLinkeTable.OwenerID to the value of EventTable.EventID for the row you just added to the EventTable and you will not know the value of EventTable.EventID until after you have added the row to the EventTable. Finally, you will need to set CitationLinkTable.OwnerType = 2 because the citation is for a fact.

I think if this as a pretty non-trivial script. It would take me quite a long time to develop it and to test it properly.

#8 · 2025-06-01, 11:23 pm

Rather than adding to my previous note that was already too long, I decided to add a second reply. All of RM's tables have a primary key that is an integer and which is an auto-increment field. Such fields are PersonTable.PersonID, EventTable.EventID, CitationTable.CitationID, etc. When you are inserting rows, you do not provide this number. Well, your INSERT statement has to include a placeholder for the column, but SQLite itself and not RM and not your script will provide the new ID for the row via the auto-increment process. I have therefore always found it tricky to link to a newly added row when I don't actually know the ID of the newly added row.