Collections - Sorting Results

[Jason_JB]

Hi - has anyone figured out a creative workaround to the functionality limitation where results in a collection appear in a random order? (or what the underlying logic for the sort actually is)

I've been digging through old discussion threads and most seem at least a couple years (if not 6 years) old. I'm working on a report that summarizes information so each project has a single row. One of the data elements that needs included is every start date of tasks with a specific attribute (aka custom field value). I can get the dates to appear, but they are in a random order - which makes consuming the information confusing.

Thank you!

[Doug_Den_Hoed__AtAppStore]

Hi Jason,

Since collections cannot be sorted (or aggregated), one tedious, inefficient, difficult to maintain, crude (still interested?) workaround is to carefully add a separate column (left-to-right) that in each uses a textmode collection with a valueexpression=IF(....) to sift for and display only the particular task matching that particular column, then with those all (expensively) in hand, stitching (right-to-left) those columns together using sharecol=true to effectively collapse them all into a single “sorted” (sorta) cell.

Alternatively, I invite you to consider our Magic Reports solution.

Regards,

Doug

[J_Mas]

@Doug_Den_Hoed__AtAppStore Thank you for this post! It really helped me out.

I was building a project-level report that included two collection columns:

• "Current Tasks" (anything that's "In Progress" or "New" and CanStart)
• "UpComing Tasks" (anything in "New" where CanStart is False).

These collections were working well enough, but the results (of course) weren't sorted. So the Current Tasks column might show task 6 before task 4, or the Upcoming Tasks might show task 15 before task 14, which was less than ideal.

So I took your advice with "ShareCol" and updated the valueexpression in my collections. Now my first valueexpression checks if the "{taskNumber}=1" and if so, it then runs the rest of my expression. Then I have another "ShareCol" that does the same thing for "{taskNumber}=2". I did this all the way up through {taskNumber="150"} since our largest project of the year had about 140 tasks (I built in a small buffer in case we hit 150 tasks in a project).

I also added a ShareCol for a new line <p> between each of the {taskNumber} ShareCols, so my "Current Tasks" column actually ended up as a total of 299 ShareCols (150 to check the task numbers and another 149 for the new lines).

I did the same thing for "Upcoming Tasks", so the whole report ended up with 603 columns (if you're looking at text-mode) but only 6 visible columns to my end user. I used Excel to help build the syntax for all the ShareCol's, so if I need to do this again for other reports I can just drop a new "valueexpression" into my Excel file and I'll have the code I need.

The result is that my report now had a "Current Tasks" column with a sorted list (by Task Number) of the current tasks, and an "Upcoming Tasks" column with a sorted list (by Task Number) of the upcoming tasks.

The final report does take a little bit of time to load (currently about 30 seconds) but I might be able to trim that with a few more filters.

Anyway, I would've never thought about the ShareCol "hack" for sorting a collection, so THANK YOU!!!

[Jason_JB]

Thanks for the suggestion! It gave me some ideas to pursue. I ended up being blocked with how to get an if statement that would only return one result in a collection.

Leaving a note in case it helps someone in the future - I ended up creating a Fusion scenario that gathers the data into an array, sorts the array, then writes the results to a custom field. Then I've pulled that custom field into the report.

[Doug_Den_Hoed__AtAppStore]

Well done Jason: Brute Force wins the day. Again.

For those without Fusion, here are a couple of valuexpression= examples for the crude approach I'd mentioned:

IF(templateTask:name='Template Task Name XYZ',name,'')

or

IF(milestone:name='Milestone Name QRS',name,'')

or

IF(name='Task Name ABC',name,'')

Regards,

Doug