Help with report view text mode - aggregating notes from multiple child tasks into a single column

[JessicaEa]

Hi,

I am trying to generate a custom column in a view in a report which lists all the notes sitting under a parent task, but this is pulling nothing.

Is there a way to do this?

displayname=Last Note
listdelimiter=<br>
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF(ISBLANK({lastNoteText}), "", CONCAT({lastNoteDate}, " ", {lastNoteOwner}.{name}, " said: ", {lastNoteText}))
valueformat=HTML

[Sven-atClient]

Hi @JessicaEa 
I would counsel against this approach - due to the large number of notes you're going to iterate through on each parent task.

A parent tasks' parent would display duplicates, too. 

also lastNote doesn't give you "all the notes" - it's simple the latest Update on the object. 

[JessicaEa]

Hi @Sven-atClient 

Thank you for your advice.

Can you further explain your comment "A parent tasks' parent would display duplicates, too."?

You are correct the lastNote doesn't display all notes. Thank you as that clarifies things for me - I am actually looking to list all  last notes on any child task of the parent task listed in the report.

If you think this isn't the best approach, can you advise what might be a better way forwards?

[Sven-atClient]

Hi @JessicaEa 

OK - so only the late note = that makes sense as a report

RE duplicates: I was wrong (and edited this response) 

lastNote is the update put in at the specific task, so there's no duplication. 

You don't need to iterate but yoru textMode was off. 

use this:

valueexpression=IF(ISBLANK({lastNote}.{noteText}), "", CONCAT({lastNote}.{entryDate}, " ", {lastNote}.{enteredBy}.{name}, " said: ", {lastNote}.{noteText}))

check the API explorer where you can see that lastNote is an object a Task refers to. and lastNote in turn has attributes. 

[JessicaEa]

Hi Sven, thank you for the advice! I did need to iterate to pick up all last notes on all children of the task. This is the code that worked for me:

displayname=Last Note
listdelimiter=<p>
listmethod=nested(children).lists
textmode=true
type=iterate
valueexpression=IF(ISBLANK({lastNote}.{noteText}),"",CONCAT({lastNote}.{entryDate}," ",{lastNote}.{owner}.{name}," said: ",{lastNote}.{noteText}))
valueformat=HTML

[Heather_Kulbacki]

If you're looking at a parent task, but looking for the last note from ALL child tasks, you'd actually need a collection. A collection is a '1 to many' relationship, so 1 parent task with (potentially) many child tasks.

You can check the API explorer, but I don't believe last note text is an option for reporting in a collection.

Here's more info on reporting on collections: Reference collections in a report

[JessicaEa]

Hi @Heather_Kulbacki thank you for the steer. By looking at collections I realised I was referencing the wrong collection - it needed to be children, NOT tasks.