Please or Register to create posts and topics.

CitationSort.sql giving an error when run..,

Hi Folks:

When i used FTM 2019 in the past I liked how sorted the citations.  I'm using RM 8.2 and thought that the CitationSort.sql would do the same thing (sort the citations to be in alphabetical order).  Here is the sql version:

-- CitationSort.sql      2016-01-08 Tom Holden ve3meo

Here is the error code the Sqlite Expert is giving me:

I'm assuming there might have been a change between RM 7 and RM 8?

Thank you for your help 🙂


Yes, that script cannot work with RM8 because of the structural change in support of reusable citations. Off the top of my head, it may be a simpler process to effect the same ordering in RM8 because only the CitationLinkTable would be affected.

Try this script for RM8.

Uploaded files:

thank you Tom, that worked great.

I appreciate your help 🙂


I might have been premature.   The sorted results are certainly better than before (all the census' are in order which is a big help) but it seems for some strange reason a few are out of order?   I checked to make sure there wasn't any blank spaces before those sources.  Any idea what might be causeing it?  (even if it can't be fixed it is much better than before):

Puzzling. Did you REINDEX with DB Browser before executing the script and then, in RM8, REBUILD INDEXES before looking at what should be the better sorted screen?

Hi Tom.... It's working now....

So in DB Browser I didn't see a menu command for reindexing so I had clicked on Integrity check, then compact database, then optimize. Then when I opened in RM 8 I had clicked on Reindex, then compact, then for good measure reindex again. Everything sorted ok except those two west virginia sources (which stayed at the top in all the ancestors that had them).

I was looking at SQLiteSpeed (because I wanted to use the TitleCase for my captions column to make the first initial cap in the title) so I thought, let me try that program. I clicked on the "Reshape all indices" (says it will recreated all Indices) and then I clicked on "Rebuild Database File" (Rebuild and compact file) and of course I rean re-index and compact in RM 8 and it all sorted just perfect.    It was probably just user-error on my part, but it is all good now 🙂

Thank you for your help 🙂


--that's a SQL command that you can type into your SQL editor and execute
It may be the exceptional SQLite manager that provides a control to also issue it.

It seems likely that not having REINDEXed before executing the script resulted in the incorrect sorting. I sometimes include the command in the script and finish with a statement reminding one to Rebuild Indexes in RM but I've not been rigorously consistent in doing so.

Curious about the TitleCase function you mention and found it's likely unique to SQLiteSpeed ( I'd be interested if you come across an extension that includes it and that could be loaded by other SQLite managers.

Thanks, I'll make a note of the REINDEX; command for next time 🙂

Yes, from what little I've researched it seems unique to SQLiteSpeed.  When I did a google search for upper case on each word in a sentence, I found the same article you did so downloaded SQLiteSpeed to test it out.

There are some things I seem to like about each IDE and other things I don't like. If I want to edit text (like the citation text) SQLiteSpeed only gives a small one line to work with, while DB Browser can automatically display the text in an editor when the cell is clicked on making it really easy to see all the text and edit it.  I had almost purchased a subscription to SQLite Expert Professional edition, but after emailing their support twice with no response to either question I decided it probably is not worth  buying.

So one of the reasons I was looking at buying a license is because their website says the Expert personal edition does not have the 'File Repair' and other functionality, but now I'm wondering if I couldn't just run those commands in sql (like the REINDEX; command) without having it built in... hmm I'll have to try that).   In your experience is that necessary since it can be done in RM 8, or do you just use a different IDE like SQLitespy when needed?

Anyways,  I had renamed my files that had been automatically named by FTM 2019 and used a different program to make the file name the Caption name. The problem was there were underscores in the caption and some words cap while others not.  After running a few queries today I removed all the underscores, made all the first letters in the caption capitals, and standardized some words  (like making all 'obit'   to obituary, etc.  Being able to run sql queries on the RM database is very helpful  (saved me many hours of repetitive work).

Thanks again for your help Tom 🙂