Nesting a Document Last Note field in a task level report

[OmahaOmaha]

I want to create a column in a task level report that shows the last note on a document that is in the document section of the task. However, I need the note to display with a reference to who posted the note and which document it is associated with (we usually have multiple documents in each task).  Any advice is appreciated.

[NathanJo1 · Accepted solution]

Hi @OmahaOmaha ,

This is absolutely doable, though it is fairly tricky. The main difficulty is that there can be many documents on a task, so you will need to reference documents as a collection. Once we reference the document object, you will need to use a valueexpression to concatenate the document name, last note text, and last note owner. Here is an example of how you can do this in text mode:

displayname=Document Last Note
listdelimiter=<p>
listmethod=nested(documents).lists
textmode=true
type=iterate
valueexpression=CONCAT({name}," - ",{lastNote}.{owner}.{name}," - ",{lastNote}.{noteText})
valueformat=HTML

You can update that valueexpression line to meet your needs. Also, fair warning, this pulls in the last note for every single document on the task, so if you have a lot of documents on tasks, you may be overwhelmed by the data that populates. 

If you need any additional help with this, we recommend working with our Professional Services Consulting team so that they can make adjustments to this to meet your specific needs.

- Nathan

[OmahaOmaha]

Hi Nathan, this works great.  Is there away to introduce skip logic in the formula?  So if there isn't a note on a document, it will simply skip that document and not display the document name and 2 dashes if there is no note. Currently shows:

document name - - 

[NathanJo1]

Hi @OmahaOmaha ,

That is possible and just requires an IF statement to be built into the expression. Normally, IF statements need 3 parts - an argument, a true result, and a false result. I have found, though, that if you are dealing with a list, it is best to leave off the false result so that it skips that entry in the list completely. Like this:

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

Hope that helps!

- Nathan