RM9 – Find Facts With\Without Media – Forum

#1 · 2023-11-02, 9:03 pm

Hello,

I am trying to find out where I need to obtain media (images of original documents) to support facts (and if possible, citations). This is useful for validating facts provided by derivative sources (I know some say images of the originals are derivative, but that's a different bucket of nuts).

Specifically, I need a query that returns a minimum of a null\not null value for media connected to each vital record fact - Birth, Marr., Death, etc. and associates that value with the primary person the fact is tied to.

Ideally, the query would return Given/Surname/Fact Type/Fact Date/Media Y-N/Media Name.

Most of my experience is with SQL, but the tables and schema in RM9 are pretty cryptic to me. I have SQLite Expert Pro and DBeaver connected to my RM9.

Based on the tables, I am making the following assumptions:

EVENTTABLE<EventID>=FACTTYPE<FactTypeID>?
WHERE:
1=Birth
2=Death
300=Marriage

PERSONTABLE<PersonID>=MEDIALINKTABLE<OwnerID>?

MEDIALINKTABLE<MediaID>=MULTIMEDIATABLE<MediaID>?

That said, I'm not seeing linkage between the facts and media, just between people and media. Not sure where joins can be made to accomplish what I need.

I was thinking I could somehow use the "Proof" values to identify the connections, but as I mentioned above, sometimes images of originals are not considered "proof." Besides, I don't see a way to generate a report on what is proven with media or just a citation. (I also don't see links between citations and media, which is a similar exercise).

I could see this requiring temp tables to manipulate the values, but I don't see any existing objects to store the results I am looking for.

If I'm chasing a unicorn, so be it, but I'm hoping someone out there may be able to help me get some queries off the ground...

Thanks!

Brian Smith

#2 · 2023-11-02, 10:32 pm

At risk of overwhelming you, have a look at the query for RM8 on Media Users List Query. It pretty much answers your need to list those items tagged with media. It could be modified to list those without media tags.

The overwhelming aspects are that it is a big query, covering all possible uses of media, uses Common Table Expressions with which you might not be acquainted, converts the encoded dates into more readable expressions, ... However, with your SQL background, you may readily grasp the relationships needed among the tables.

#3 · 2023-11-04, 10:40 am

Tom,

Thanks for pointing that query out! You're right about the complexity. I believe I can use some of those pieces to start cobbling something together. One helpful thing I noticed already is calling out where I used things like a census as a birth citation - that is where I need to focus on getting better records.

Thanks again for the help...

Brian

#4 · 2023-11-04, 11:27 pm

Quote from thejerrybryan on 2023-11-04, 11:27 pm
Tom's queries are probably a better resource for learning what you need to learn than my comments here, but let me see if I can give a bit of a high level overview.
The key data element you need to understand is probably MediaLinkTable.OwnerType. A value of 2 indicates the media file is linked to an event and a value of 4 indicates that the media file is linked to a citation.
With that understanding, a query that will find events/facts without a media file is something like the following.
SELECT E.EventID, ML.MediaID
FROM EventTable AS E
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 2
AND ML.OwnerID = E.EventID
AND ML.MediaID IS NULL
Additional JOIN's are going to be required to link the Event back to a person and to display which Event for the person is the one missing a media file. There is the additional complication that so-called family events such as Marriage and Divorce work a great deal differently than individual events such as Birth and Death. But this is the basic structure of one way to find Events without media.
Similarly, a query that will find citations without a media file is something like the following.
SELECT C.CitationID, ML.MediaID
FROM CitationTable AS C
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 4
AND ML.OwnerID = C.CitationID
AND ML.MediaID IS NULL
And again, additional JOIN's are going to be be required to link the Citation back to a person and to display which Event for the person is the one missing a media file. But this is the basic structure of one way to find Citations without media.
Did you find https://sqlitetoolsforrootsmagic.com/Database-Design-Spreadsheets/ ? That's where I always go when I can't quite remember something about RM's database schema. Some users (maybe most users) prefer a diagram (a picture) of the schema. But as a sample size of one, I do best with the spreadsheet. A browser's Find (Ctrl-F) is my friend to search through the spreadsheet. For example, after you get to the spreadsheet you can just do a browser level Find on MediaLinkTable and quickly see what you need to know, including the possible values of MediaLinkTable.OwnerType.

Tom's queries are probably a better resource for learning what you need to learn than my comments here, but let me see if I can give a bit of a high level overview.

The key data element you need to understand is probably MediaLinkTable.OwnerType. A value of 2 indicates the media file is linked to an event and a value of 4 indicates that the media file is linked to a citation.

With that understanding, a query that will find events/facts without a media file is something like the following.

SELECT E.EventID, ML.MediaID
FROM EventTable AS E
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 2
AND ML.OwnerID = E.EventID
AND ML.MediaID IS NULL

Additional JOIN's are going to be required to link the Event back to a person and to display which Event for the person is the one missing a media file. There is the additional complication that so-called family events such as Marriage and Divorce work a great deal differently than individual events such as Birth and Death. But this is the basic structure of one way to find Events without media.

Similarly, a query that will find citations without a media file is something like the following.

SELECT C.CitationID, ML.MediaID
FROM CitationTable AS C
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 4
AND ML.OwnerID = C.CitationID
AND ML.MediaID IS NULL

And again, additional JOIN's are going to be be required to link the Citation back to a person and to display which Event for the person is the one missing a media file. But this is the basic structure of one way to find Citations without media.

Did you find https://sqlitetoolsforrootsmagic.com/Database-Design-Spreadsheets/ ? That's where I always go when I can't quite remember something about RM's database schema. Some users (maybe most users) prefer a diagram (a picture) of the schema. But as a sample size of one, I do best with the spreadsheet. A browser's Find (Ctrl-F) is my friend to search through the spreadsheet. For example, after you get to the spreadsheet you can just do a browser level Find on MediaLinkTable and quickly see what you need to know, including the possible values of MediaLinkTable.OwnerType.

kevync has reacted to this post.

RM9 - Find Facts With\Without Media