* Including long text field in query result

[docdunning]

I've just discovered that text fields such as notes are truncated at or around 500 characters, when I include them in a query. I need the whole field because it's being copied into an Excel document for use elsewhere.

Other forum posts led me to the =GetLabelledText() function, but that's designed to extract a portion of the text rather than to output the whole of the content. I see there's also a =GetParagraph() function, but that's going to need a paragraph number as a parameter, and I can't predict how many paras there will be in any one note.

Is there a simple way to get the whole of a field into a query results column, without truncation? Hope so!

[tatewise]

The only technique is to use multiple =GetParagraph(...) functions to accommodate the maximum number of paragraphs.
e.g.
=Text( GetParagraph(%INDI.NOTE2%,1) . " " . GetParagraph(%INDI.NOTE2%,2) . " " . GetParagraph(%INDI.NOTE2%,3) )

It does not matter if there are too many as the excess will just generate empty text strings.

Note that the newline characters are not included and are not allowed in Query Column expressions.
That is why a " " single space is inserted between each paragraph. Otherwise, there will be no gap at all.
Alternatively, put each =GetParagraph(...) function in a separate Query Column, then the target system can insert the newline characters if that is required.

The only other option is to write a plugin to create the Result Set or possibly use some form of Report.

See FHUG Knowledge Base Display long text such as Notes.

[docdunning]

Thanks, Mike.

I'll try the paragraph-based approach. Meantime, I think it's time I had a go with plugins. I've been a software developer in my time so I'm hoping that LUA won't be too much of a curve to climb.

Martin

[Mark1834]

Martin,

The plugin help is a fairly good starting point, along with FHUG KB linked to at the top of the forum. There is far less introductory material online than for comparable languages such as Python, but there are a couple of good tutorials linked from the KB.

Take it a step at a time and you’ll soon get into it. It helps provide focus if you have a particular outcome in mind from your own data. I would advise against trying to use store plugins as learning cribs, as most are written to do a particular job rather than as teaching aids, so can be rather opaque to the novice.

[BillH]

Should this be reported to CP?

Sorry if this has already been mentioned and I missed it.

Bill

[docdunning]

Thanks for the help, all.

I've made some progress with a plugin, thanks to the help guides mentioned by Mark. Here's what I have, which does output the note text in full. Maybe not the most elegant code, but by building up from the examples in the guide, it wasn't too tricky.

Code: Select all

tblNotes = {}
tblIds = {}
tblNames = {}

pi = fhNewItemPtr()
pi:MoveToFirstRecord('INDI')
while not pi:IsNull() do
	iRecordId = fhGetRecordId(pi)
	strName = fhIndGetName(pi,true,false)
	ptrNote = fhGetItemPtr(pi, '~.NOTE2')
	strNote = fhGetValueAsText(ptrNote)
	if string.len(strNote) > 0 then
		table.insert(tblNotes, strNote)
		table.insert(tblIds, iRecordId)
		table.insert(tblNames, strName)
	end	
	pi:MoveNext()
end
fhOutputResultSetTitles('Individual Notes')
fhOutputResultSetColumn('Id', 'integer', tblIds, #tblIds, 50, 'align_right', 1, true)
fhOutputResultSetColumn('Name', 'text', tblNames, #tblNames, 200, 'align_left', 2, true)
fhOutputResultSetColumn('Note', 'text', tblNotes, #tblNotes, 400, 'align_left', 0, true)

[tatewise]

Martin, you are now a fully-fledged Plugin author. We will expect great things

Bill, I cannot find a CP problem report.

I have deleted my misleading advice!