Reports – New Paragraph for General Note #paragraphing #reports

RootsMagic 6.3.1.0 introduced in 2014 some very nice options for control of paragraphing of narrative reports after we had developed a number of scripts in 2011ff described in Paragraphing. There remain some issues for some people as of RM 7.2.1.0. One was recently expressed by Wiki member aefgen:

Now another problem that I see has been discussed before: the last fact to print for a person in the narrative report is followed immediately by the general note for that person, even when you choose to have a space between each fact. That last fact (which will vary depending on what other facts are included in the database for that person) runs on into the text for the note.

Reports-_New_Paragraph_General_Note.sql-_Narr_Report_Be.png
Snippet from a narrative report showing the General (or Personal) note running on in the same paragraph as the last individual event.

Here is a script that works around this limitation by pre-pending to the General Note two pairs of CR/LF control codes to force a new paragraph. It applies the codes only to non-empty notes.

Reports-_New_Paragraph_General_Note.sql-_Narr_Report_Af.png
Same report as above after running the script.
Reports-_New_Paragraph_General_Note.sql-_Note_Editor.png
The modified General Note as viewed in the RootsMagic Note Editor.

When executed on a database, the script creates two temporary views that persist as long as the SQLite manager keeps the database open:

  • vBareNote: the non-empty General Note stripped of one or two pairs of leading CR/LF in readable text,
  • vNewNote: the Note from vBareNote pre-pended with two CR/LFs in readable text, which has been applied to the database Note field in PersonTable.
Reports-_New_Paragraph_General_Note.sql-v_Bare_Note.png
vBareNote should always show the first line of text from the General Note with no leading blank lines, else there is an issue.


Reports-_New_Paragraph_General_Note.sql-v_New_Note.png
vNewNote should always show two empty lines before the text, else there is an issue.

The Note field in PersonTable is typed by RootsMagic to be BLOB (Binary Large OBject) so the script CASTs the readable text accordingly. That said, RootsMagic does not appear to preserve the type for this field converting it to Text when you next Save from the Note Editor. This change in type may be a useful indicator of edits done in RM after the last time this script was run.

Reports-_New_Paragraph_General_Note.sql-_Person_Table.png
After running the script, all the non-empty Note fields in PersonTable should be of type “BLOB” with two pairs of leading hex characters “0D 0A”, the ASCII control codes for CR/LF.

If a vBareNote cell does not display the first words of the Note, there is a problem with the original Note in PersonTable that requires manual attention either through RootsMagic (the vBareNote.PersonID is the RootsMagic RIN or Record Number) or through the SQLite manager. Re-executing the script after such manual edits will assure that the PersonTable.Note fields consistently have two leading CR/LFs.

Reports-NewParagraphGeneralNote.sql

Paragraphing #update #paragraphing #reports

Paragraph control in the narrative reports in RootsMagic 4 and 5 leaves much to be desired. A richly facted person with many notes will be described in one long paragraph, apart from any paragraphing within the body of a fact note, unless special non-intuitive measures are taken. Likewise, another long paragraph of family facts and notes follows.

Different folks have tried different strategies with varying success. Basically, there are two:

  • Use the Customize Sentence feature for Facts in the Edit Person screen and enter double Carriage Return/Line Feeds (the Enter key or Ctrl-M in the editor) at the beginning of the sentence of the fact for which you want to start a new paragraph (often after a long note from the preceding fact).
  • Add double CR/LFs at the end of notes of facts following which you want the next fact sentence to be in a new paragraph.

In both cases, one is likely going to want to do further paragraphing touch-ups in Microsoft Word on the RTF file saved from the RM Report Viewer. Also, the first creates new paragraphs even if notes are excluded from a report; that may not be wanted. The second does not survive a transfer; trailing white-space in notes is truncated on a GEDCOM export or drag’n’drop transfer between RM databases.

Until RootsMagic provides better control of paragraphing and persistence through export and transfer, it is desirable to have some batch process that can quickly provide a first cut at paragraphing reasonably. This page and its queries attempt to provide such tools.

Paragraph-strip.sql RMtrix_tiny_check.png This query strips out leading and trailing CR/LF and blank spaces from the custom fact sentences and fact notes for persons and families. Execute it repeatedly until you think you have stripped out prior paragraphing. Twice should be enough if there was never more than two pairs of CR/LF entered at the beginning of a custom sentence or at either the beginning or end of a fact note. Paragraphing within the body of a note is unaffected.

Paragraph-add.sql RMtrix_tiny_check.png This query so far addresses only paragraphing of person facts. It adds double CR/LFs at the end of each non-empty fact note and then attempts to strip them from the last note before the beginning of the family notes or children.It’s largely untested – feedback invited. If the Person facts are paragraphing as intended, then the same strategy may work with family notes leading to the spouse and child list.

(
Added by Jerry, 12/23/2011). I have done some testing of some of the items in Paragraph-strip.sql. For now, I’m focusing on the General (Individual) Note only. The SQL that I tested ran just fine. I have now run it in a small test database, in a copy of my production database, and in my production database. Using the SQL scripts saved me many, many hours of very tedious and error prone work.

The only little white space glitch I found was that in addition to blanks and CR/LF sequences, I found some leading TAB characters at the front of a few notes (CTL-I or X”09″). These were introduced into my database via GEDCOM import over a decade ago, before I really knew what I was doing in managing my database. I would have eventually found and cleaned them up anyway, but Paragraph-strip.sql greatly facilitated the process. In the meantime, here’s a very simple little query that I ran to monitor the progress of the changes I was making with SQL from Paragraph-strip.SQL. That’s how I found the TAB characters. I used an equivalent query to monitor changes on the right end of the notes.)

Jerry
)

SELECT P.PersonID,
       HEX( SUBSTR(P.Note,1,1) ) AS LeftEndH1,
       P.Note
FROM
       PersonTable AS P
       WHERE LENGTH(P.Note) > 0
 
ORDER BY LeftEndH1, P.Note

Discussions & comments from Wikispaces site


texas-nightowl

Italics in the fact notes

texas-nightowl
05 April 2016 22:19:44

paragraph-strip.sql : This is only the 2nd sql script I have attempted to run. Mostly, it worked fine. However, I had several notes for which it did not work, even after running it several times. The one thing all these notes had in common? They were italicized. So I exported to gedcom and took a look. And sure enough, the line feeds were before the ending <i>. I don’t remember whether I added the line feeds first and then italicized or whether I italicized first and then added the line feeds. But one way, or possibly both, the line feeds ended inside the italicize code and therefore the script considered the line feeds as part of the body of the note and the strip did not work. I don’t know enough to know if there is a way around that or not. Luckily, this was isolated (so far!) to about 6 people, so it wasn’t terribly hard for me to just edit the notes manually. So, just a heads up about that.