Project Report - New Column - Text Mode - Milestone reporting

[Monty_A]

Hi, I must be missing something very basic.  Any chance I can get one of you to go over my valueexpression and see what I am missing?

I am working on a project report.  I have a milestone process assigned, and milestones assigned to key tasks.  I want to report milestone status at project level and I only want one response, either the last completed milestone or the next milestone not yet completed.  However, as you can see below my output gives me each milestone.  I was hoping it would only return the first one that met the criteria.  Help?

Column Name	Last Completed Milestone	Next Incomplete Milestone
Output	M0 Pre-Planning: 12/10/24 M1 Kick-Off: 12/17/24 [should only show last milestone named M1]	M2 Development M3 Compliance Approval M4 Launch [should only show first milestone named M2]
Text Mode	displayname=Last Completed Milestone	displayname=Next Incomplete Milestone
	listdelimiter=<br>	listdelimiter=<br>
	listmethod=nested(tasks).lists	listmethod=nested(tasks).lists
	textmode=true	textmode=true
	type=iterate	type=iterate
	valueexpression=IF({milestone}.{name}="M5 Live" && ISBLANK({actualCompletionDate})=false,CONCAT("M5 Live: ",{actualCompletionDate}),IF({milestone}.{name}="M4 Launch" && ISBLANK({actualCompletionDate})=false,CONCAT("M4 Launch: ",{actualCompletionDate}),IF({milestone}.{name}="M3 Compliance Approval" && ISBLANK({actualCompletionDate})=false,CONCAT("M3 Compliance Approval: ",{actualCompletionDate}),IF({milestone}.{name}="M2 Development" && ISBLANK({actualCompletionDate})=false,CONCAT("M2 Development: ",{actualCompletionDate}),IF({milestone}.{name}="M1 Kick-Off" && ISBLANK({actualCompletionDate})=false,CONCAT("M1 Kick-Off: ",{actualCompletionDate}),IF({milestone}.{name}="M0 Pre-Planning" && ISBLANK({actualCompletionDate})=false,CONCAT("M0 Pre-Planning: ",{actualCompletionDate}),""))))))	valueexpression=IF({milestone}.{name}="M0 Pre-Planning" && ISBLANK({actualCompletionDate}),"M0 Pre-Planning",IF({milestone}.{name}="M1 Kick-Off" && ISBLANK({actualCompletionDate}),"M1 Kick-Off",IF({milestone}.{name}="M2 Development" && ISBLANK({actualCompletionDate}),"M2 Development",IF({milestone}.{name}="M3 Compliance Approval" && ISBLANK({actualCompletionDate}),"M3 Compliance Approval",IF({milestone}.{name}="M4 Launch" && ISBLANK({actualCompletionDate}),"M4 Launch",IF({milestone}.{name}="M5 Live" && ISBLANK({actualCompletionDate}),"M5 Live",""))))))
	valueformat=HTML	valueformat=HTML

[KellieGardner]

I don't know if I'll be able to explain this correctly, but I'll try. And there really isn't an experience league description of it, it's just what I've experienced and found.

When you are running IF statements on collections it's not just looking at a single object to validate if it's true or not true, it's looking at an entire collection and will repeatedly look at the data (iterate) through it to validate it. It's a little different than if you were looking at just regular objects. 

If you want it to only show the most recently completed task with a milestone then you'll have to filter for it. You could use something like actual completion date was last week or something a

For the next incomplete milestone you could filter for it by using something like can start (if you use predecessors), handoff date or another indicator that the task is next in line and could/should be worked on.

[kautuk_sahni]

@Monty_A just checking in! Were you able to get this resolved? If one of the replies above helped—whether it completely solved the issue or simply pointed you in the right direction—marking it as accepted can make it much easier for others with the same question to find a solution. And if you found a different way to fix it, sharing your approach would be a great contribution to the community. Your follow-up not only helps close the loop but also ensures others benefit from your experience. Thanks so much for being part of the conversation!