Media Users List Query #media #rm8


As of RootsMagic 8 Preview 7.9.180, the traceability of where an image is used has changed little from what was described below for RM4. The need for an outboard query such as this is as high in early 2021 as it was in 2010. With some changes in tables from RM7, the Media-UsersList4.sql query does not run on RM8. Pat Jones revised it and applied a technique she describes at Common Table Expressions – The Building Blocks of SQL to streamline it:


For a version that works with databases from RM5 to RM7:


RootsMagic 4 falls short in handling media in several ways. One serious one is its inability to report fully all the ‘owners’ of the media, i.e., its terminology to describe what person, couple, event, source, citation, place or place detail to which a media item is attached, in sufficient detail that the attachment can be found. Trace-ability is essential to be able to make changes to an attachment’s properties, i.e., its caption, description, reference number, date, sort date, primary and scrapbook inclusion flags. RM4’s Report > Lists > Multimedia List does not even report usage by Sources or Source Details and fails to report the Place to which a Place Detail, having media attached, belongs. Similarly, the Media Gallery > Tools > “Show where this media is used” results are incomplete for media attached to Source Details or to Place Details, giving little or no clue to how you would navigate to the citation or Place where you could then drill down to the Media and edit the caption or other properties.

Here’s an example of what I mean:

Owner according to Media Gallery > Tools > “Show where this media is used”

Cens: 1891 Can: - FF2 (ff incomplete) (citation)

How can one possibly find the citation without at least the RIN or name of the person?

Users according to Media-UsersList query

(citation) WICKENS, Martha Jane "Jenny"-208 : RESIDENCE (FAMILY) ca 1891 : HOLDEN, Matthew Edwin-149 citing Cens: 1891 Can: - FF2 (ff incomplete)
(citation) HOLDEN, Matthew Edwin-149 : RESIDENCE (FAMILY) ca 1891 : WICKENS, Martha Jane "Jenny"-208 citing Cens: 1891 Can: - FF2 (ff incomplete)

Both RM4 and the query show that the media file is attached to a citation but the new query provides explicit guidance to the citation via not only the name and RIN of the person but also the fact name and date so that the citing fact can be quickly found and its Citation Manager opened. The source name is shown (that’s all the Media Gallery shows apart from the caption and full filename) so it can be readily selected from the Citation Manager and the Source Detail Media Album opened therefrom and the media item selected by file name for the editing of its properties. That is the only way RM4 lets you get to the item’s properties; obviously, a road map is necessary. This query shows the route.


Media-UsersList.sql – original query, easier to modify, fast on smaller databases, bogs down on large ones
Media-UsersList3.sql RMtrix_tiny_check.png – fast even on very large databases, larger sql file, harder to modify at the base SELECTs.
Media-UsersList4.sql – as ver 3 but revised for RM5 and 6; prior versions work with RM4.


With Thumbnail Images

Using SQLite Expert Personal, make sure the following setting is checked for the thumbnail image to be shown:

Tools > Options > Data:

  • Results-Display results in grid
  • Grids-Show images
SQLite Expert Personal can display the thumbnail images stored in the database. A subset of the available columns is displayed to reduce clutter. Unfortunately, column widths are not remembered on re-executing the same query. Also, sorting on column heading does not work unless the ORDER BY clause at the very end of the query is deleted.

Without Thumbnail Images

Screenshot from SQLiteSpy which has no image display capability but does remember column widths within the same session and supports sorting on column regardless of an ORDER BY in the query.

Discussions & comments from Wikispaces site



Comment: “Download”

03 September 2018 21:38:03

ve3meo Nov 14, 2011

This query can bog down on very large databases because it produces a very large set of intermediate results that must be stored in memory. It is being (slowly) revised to produce many small sets of intermediate results so that it will be able to handle large databases efficiently. This is not a trivial re-write.
ve3meo Nov 14, 2011

Re-write done: Media-UsersList3.sql. On a large database that was taking 37 to 120 seconds to display results, the time has dropped to 1-5s, depending on the SQLite manager, what else is going on, how much memory is available.The key was to structure it to minimise the size of intermediate results that are then JOINed with another result set.

2 Replies to “Media Users List Query #media #rm8

  1. Thanks for sharing — this is helpful. May also be related to the “bug” I found when Export a GEDCOM after using the merge duplicate citations and sources tool.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.