Question re getting from citation to event or fact in #RM8
This was posted by Charles Banks as a comment on RM8 Data Definitions Spreadsheet #database #datadefinitions
Another issue I have is citations that show they are for one person attached to another. I want to find all events FROM citations. I can enter a event ID and trace to TO citations. How do I enter a citationID and trace it to events using SQLite?
I would suggest you look at
Thanks for moving this to the correct place, I'm new to all of this. I did try running your scripts and reviewing the results. First issue, I am receiving the following error when trying to run the FindAlmostEverywhere-Build_FTS_table.sql - Result: no such table: ResearchTable.
Second, issue is you dropped the most important piece from my post. My main objective is to determine if I have any orphaned citations. Looking at sources I can immediately see a count of citations connected to it; even 0. I want to be able to look at a citation and see all events and people.
Which version of RM8 database are you using? ResearchTable is pre-RM8 so it must be a RM8 database. Build_FTS_table.sql predates RM8 and has not been updated. Sorry.
That said, RM8_WaymarksViews.sql should give you a useful view. After running it, then:
SELECT * FROM CitationWay ;
I split your other piece off to another Forum post.
You may find AllCitations-RM8.sql to be more useful. It works really well with SQLiteSpy (as do the Wayfinding views) which quickly sorts any column of the results. It attempts to display all results while other programs limit it to the first 1000 records or other value and column sorting is only within the displayed results.
I am running 8.1.8. My entire post is important intact as you are not understanding my issue. I had a lot of duplicate facts. I wrote SQL which allowed me to take one (one) event id, trace it back to its source, then to the citation. EventID = 187; SourceID = 82; CitationID = 12. I can easily tell (at least 3 ways) that SourceId 82 is connected to 30 citations. Inside RM8 I can drill down from a source to each of the 30 citations and see events. I want to have an SQL which I can enter CitationID=12 and show me all 30; opposite direction from which I have working.
In addition, I would like to see if there are citations that have no sources or events. Everything you have suggested makes it necessary to wade through a lot of data.
I'm not familiar with SQLiteSpy.
I appreciate the responses and what you have done.
Perhaps you should give RM8_WaymarksViews.sql a try with SQLiteSpy if you are on Windows. I think it gives a very effective answer to your wish to see all the uses of a given citation. But so should your current SQLite manager.
Attached is a screenshot of the CitationWay View created by the script and sorted on CitationID by clicking on that column. Note that you can see the list of all the Views created by the script in the left sidebar. Double-click on CitationWay to display the View's results. Click on a column heading to sort by the column. You then see all the uses of that Citation clustered together, e.g., 6 uses of CitationID=812. Click on a cell to see its contents expanded in the bottom area. The Waymarks tell you how to navigate in RM to that particular use of the citation. In this screenshot, the cell selected shows George Meikle Wason with RIN=337 and a symbol indicating that there are more lines in the cell, i.e., that its for a Birth event dated 1876 and the citation is from the source named 1901 Scotland Census. Scroll to the right to see all the fields from the CitationTable.
You can query a View just as you can query a Table. For example:
SELECT COUNT(), * FROM CitationWay GROUP BY (CitationID) ;
shows the number of times a given CitationID is used.
SELECT * FROM CitationWay WHERE CitationID = 812 ;
shows only the uses of one Citation. Is that not what you meant by "How do I enter a citationID and trace it to events using SQLite?"
I'm going to continue in the topic Question re orphan citations #rm8 to respond to your "In addition, I would like to see if there are citations that have no sources or events" because what I posted there is relevant and because the queries I've suggested in this topic do not on their own address that wish.
Uploaded files:
Thank You! I have been doing really great working with SQLite Browser so understand what RM8_WaymarksViews.sql does. What I didn't realize was the detail in the Waymarks field, which is what I was looking for. My apologies for not seeing that before.
Tom, I also did not see where you had put the other part of my post until now. I will follow that link and check it out further.
Not only is Roots Magic a great software application, but it has groups and people that go above and beyond to help others. Thanks for working through this with me.
Charles
You're welcome! It's gratifying to hear that this site is used and appreciated.
Is your SQLite Browser this: DB Browser for SQLite from https://sqlitebrowser.org/? Are you on Windows or MacOS?
I'm afraid that since v7.5 was released, I haven't been feeling as good about RM as I had been. Frustrated that so many requested enhancements have been ignored, forgotten, neglected by the focus on FamilySearch, TreeShare and MacOS.
My SQLite Browser is indeed the one your link is pointing to. Since I am a DBA it has been very easy for me to use, and even customize. I am loading the unifuzz.dll plus made some customizations. I'm using Windows 11.
I've been working with another person, Kevin, who I found on another group. He is doing extensive work with Excel Power Queries and has shared a lot of SQL Statements with me. We both are not 100% on certain areas, like Owner ID. That is what brought me here.
Because I had such a mess on my hands from FTM2019 all I've really been doing is cleanup. Everything I've seen with (and done with) RM8 appears to be better but I have not used it yet. I've been using SQL because I had over 200 duplicate facts. It is also easier for me to get a big picture of certain things.
I'm not 100% sure of a lot of things! But I've plunged ahead, at times with assumptions that proved faulty. Are you clear on how OwnerID relates to the primary key of a table that is determined by OwnerType? E.g.,
OwnerType Table.key
0 PersonTable.PersonID
1 FamilyTable.FamilyID
...