Sorting the order of #RM9 #Citations

#1 · 2024-01-20, 1:55 pm

Richard Otter reported an intriguing discovery in this topic in the independent RootsMagic-Users forum: the SortOrder column in the CitationLinkTable introduced in RM9 is currently supported in v9.1.3 at least in affecting the order in which Citations are listed in the Edit Person window. He wants to know if there is interest in a utility that would give users control over the order.

Of course, I had to play with the idea. I was curious to see how RM's sorting would work with different types of values in the SortOrder column which is typed as Integer. However, SQLite itself is loose about type and text and decimal numbers can easily be entered.

RM has more fully developed UI for spouse order and child order and here we see only integers {0,1,2,3...} for each set (couple/family, children thereof). I wondered if simply using the value of the key field from the SourceTable or the CitationTable would work. After all, the set of Citations for a given Fact or General or Couple would be small and the integers, although potentially large, could still be sorted. That works!

Likewise, could the SortOrder column simply be populated with text, such as the Source Name. That works, too!

So does Citation Quality, a 3-char encoded text field from the CitationLinkTable, because the encoding was designed for sorting from the strongest evidence to the weakest.

Decimal numbers, e.g., UTCModDate, also work.

So there are several ways that Citations can be globally ordered, easily and quickly. The attached sql file illustrates the above with each type of sorting in a comment block.

The one that stands out for me is sorting on Quality+Source Name. If one has not set Quality, the order is by Name. Once one has set Quality for a citation in a given fact, the strongest quality is at the top of the list. That would be the first useful output for this long undeveloped feature.

Uploaded files:

CitationSortTests.sql

#2 · 2024-01-20, 3:16 pm

Had a look at Footnotes for the Individual Summary and one Narrative report and see that the SortOrder field in CitationLinkTable has no effect.

Should Richard Otter develop what he has in mind, a utility to allow manual ordering of citations, or should RM Inc do so for some future release, it may be desirable that any outboard global sorting tool should have the option of protecting or replacing a manual order. That might be aided by prefacing any global values for SortOrder with a symbol to distinguish them from the low-value Integers expected from a manual ordering. Maybe ▼.

#3 · 2024-01-21, 12:43 pm

Here is a slightly different script that accomplishes the same goal of ordering the list of citations for a person or fact in the RM9's Edit Person screen. It sets the CitationLinkTable.SortOrder field to an integer in the range of 1 to n for each unique combination of CitationLinkTable.OwnerType and CitationLinkTable.OwnerID.

#4 · 2024-01-21, 12:44 pm

Well, forgot the file. Here it is.

Uploaded files:

update_citation_order.sql

Richard Otter has reacted to this post.

#5 · 2024-01-21, 11:25 pm

Now that is 'elegant'!

I've built on my first tests with my crude, brute force approach to create this interactive script (requires support for run-time variables in the sqlite manager) which provides a choice of 18 sort orders. It puts only non-integer values in the SortOrder column and also avoids by default overwriting integer values; my thinking being that if RM ever exploits the column, it will do so with integers. Thus, a manual sorting using RM's future controls could be left untouched by this global sort.

I tried breaking up the UPDATE statement with CASE in ways that the code would be less repetitious but kept getting blocked by syntax errors.

Uploaded files:

CitationSortOrders.sql

#6 · 2024-01-22, 2:52 pm

Quote from Tom Holden on 2024-01-21, 11:25 pm
...tried breaking up the UPDATE statement with CASE in ways that the code would be less repetitious but kept getting blocked by syntax errors.

Solved. Here's the more compact script. My sqlite skills rust rapidly in the layoff between problem-solving sessions!

Uploaded files:

CitationSortOrders.sql

Richard Otter and kevync have reacted to this post.

#7 · 2024-01-28, 9:46 pm

This is cool. Thanks for sharing -- I knew the sort keys were little more complex than appeared because of the way it did thing -- but never realized the way it worked behind the scenes

#8 · 2024-01-29, 11:45 pm

I released ver 1.0 of the manual citation reorder utility.
This release only deals with citation lists attached to persons, names and facts.

Navigating to the correct list is a bit tedious. If there is interest, I may try to improve the navigation functions.

It took a bit of effort since the utility has to display RM dates. I've wanted to write that code for awhile anyway. (The utility also won't work if your fact has a quaker date. To be addressed later...)

I've updated my web page at-
https://richardotter.github.io/

Or you can go straight to the release package at:
https://github.com/ricko2001/Genealogy-scripts/releases/tag/CitationSortOrder_v1.0.0.0

Tom's idea of using the global sort order update first and then allowing manual update of specific lists is great. It's just hard to know how much effort it's worth putting into this, as we don't know RM Inc's plans.