Page 1 of 1
Including long text field in query result
Posted: 17 Mar 2022 17:44
by 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!
Re: Including long text field in query result
Posted: 17 Mar 2022 18:04
by 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.
Re: Including long text field in query result
Posted: 18 Mar 2022 12:11
by 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
Re: Including long text field in query result
Posted: 18 Mar 2022 12:51
by 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.
Re: Including long text field in query result
Posted: 18 Mar 2022 15:48
by BillH
Should this be reported to CP?
Sorry if this has already been mentioned and I missed it.
Bill
Re: Including long text field in query result
Posted: 18 Mar 2022 16:01
by 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)
Re: Including long text field in query result
Posted: 18 Mar 2022 16:21
by 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!