0

Sorting a Table View by Dynamic Choice

I have two tables - Projects and Tasks. The Tasks table contains a field named Project which is a Dynamic Choice field and the choices are populated based on the Projects table. This works fine and I understand how to sort those choices in the combo box control by using (select Projects) order by ProjectName. That works fine and that's not my issue. My issue is as follows...

I have various table views that I use to review my tasks and I'd like to be able to sort those tasks by the ProjectName they are associated with, however when attempting to sort by this field alphabetically in a table view, it always sorts by the record ID number of the Project rather than the text it contains. The Dynamic value set on this field is ProjectName. I've also tried using text(ProjectName) to fetch the field contents but the results do not change. Just out of curiosity I tried using ProjectName + " " + ID and sure enough, it shows that the results are always sorted by the record ID from the Project table.

Does anyone have ideas on how to sort a table view by the text contents of a Dynamic Choice field?

Thanks

4 replies

null
    • Fred
    • 8 mths ago
    • Reported - view

    I have so many questions that it might be easier to see what is going on if you posted a sample DB.

      • activelifejss
      • 8 mths ago
      • Reported - view

       I'm currently working from the cloud version so I'm not sure if I can export a sample DB from here but I can share some screenshots if that helps.

      The tables in question...

       

      Projects is a very basic table, nothing special here, just a list of Projects with some fields for tracking them. It happens to be sorted by ProjectName in this screenshot but I also displayed the ID for reference because I will refer to it further down in my comments...

       

      This is a snapshot of the Tasks table, in a view named "by Project". This view is grouped by Project which is a Dynamic Choice field based on Project.ProjectName.

       

      Here is a snapshot of the settings for the Project field which is a Dynamic Choice field. Dynamic values are set to (select Projects) order by ProjectName which produces a nice alphabetical list of choices in for the user in the combo box field - this works fine. Notice the Dynamic value name is ProjectName. I've also tried using text(ProjectName) but I get the same results. While it DISPLAYS the text, it only appears to STORE the ID of the corresponding ProjectName in the ProjectTable. Why do I think this? See further down where I try to sort my view based on this field and it refuses to sort in any way other than by the ID of the corresponding ProjectName in Project table. It will not sort by the text content of this field no matter what I have tried. Scroll down for example...

       

      When sorted ascending order, S appears before I (among other examples)...

       

      So I go into troubleshooting mode and display the ID number along with the text to see what it may be doing. I notice that it's actually sorting by the corresponding ID number of the matching record in ..

       

      And now I see why it's sorted in this order, because it's sorting by the record ID of the corresponding choice in the Project table...

       

      In case you think this is just one instance and not what is happening, I can show that this logic continues all the way down the table as I can show in a few other examples below.

       

      T before E?

       

      S before C?

       

      Does this help you to see my issue?

       

      Thanks for your help.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 8 mths ago
      • Reported - view

       

      Hi. Maybe something like this. ?

      Multiple Choice Dynamic and View table group by Activities and the other by Staff each one with the Graph.

    • Fred
    • 8 mths ago
    • Reported - view
     said:
    I'm currently working from the cloud version so I'm not sure if I can export a sample DB from here

     If you have the starter version then you are correct there is no way to download a copy of your DB. I don't know why but you can't. If you are on MacOS you can download the app and then you can download a copy of your DB.

    You are correct that dynamic choice fields are always referenced by their record Id. That is because they are dynamic so Ninox always needs to root the selection on something that it can refer to.

    One way around is to create a new formula field that has:

    text(Project)

    Then you can show this field in the table view and then you can sort properly.

Login to reply

Content aside

  • 8 mths agoLast active
  • 4Replies
  • 118Views
  • 3 Following