Collections - Sorting Results | Community
Skip to main content
Jason_JB
Jason_JB
Level 5
January 13, 2022
Question

Collections - Sorting Results

  • January 13, 2022
  • 2 replies
  • 1286 views

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!

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    2 replies

    Doug_Den_Hoed_AtAppStore
    Community Advisor
    Doug_Den_Hoed_AtAppStoreCommunity Advisor
    Community Advisor
    January 13, 2022

    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
      J_Mas
      Level 5
      December 13, 2023

      @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!!!

      V
      VynetaRy
      Level 2
      October 7, 2024

      @j_mas I am having trouble visualizing what that code would look like. Would you mind posting an example with maybe three or four tasks?

      Jason_JB
      Jason_JBAuthor
      Level 5
      January 19, 2022

      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
      Community Advisor
      Doug_Den_Hoed_AtAppStoreCommunity Advisor
      Community Advisor
      January 19, 2022

      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

        Terms & ConditionsAccessibility statement

        Loading footer...