Forum Discussion

RyanWilliams2's avatar
RyanWilliams2
Qrew Member
2 months ago

Text - Multiple Choice - input from another field sorting

Is there a way to have your multiple choice text field sort in more ways than alphabetical or "Order shown here"? Or put another way, is there a way to select what "Order shown here" refers to?

I have a source table that is a list of active and inactive funding sources. I am using this as a global pick list for multiple tables in my app. I want it to display active funding sources first, with inactive below. i.e. sort active first and inactive second. Ideally it would also be alphbetical after the active/inactive sorting. I tried setting the default report to have the proper formatting, when you open the table it displays how I want it, but the field list in the other tables are either based on when the data was entered (order shown here) or just alphabetically.

It appears that "order shown here" is based on the row number.

Any advice would be greatly appreciated.



------------------------------
Ryan Williams
------------------------------

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    2 months ago

    ... so the issue I believe is that you are sort of taking the expedient way out to use a table as a source for a multiple choice field.  You should ensure that the key field of that source table is set to the be the field which holds the text values of the choices, and then make a "proper relationshi"p where One source table record has many "other table" records.  When you make the relationship do not Quickbase make a new field  - instead select the drop down choice for your current multiple choice field.  The Multiple choice field will turn into the Reference field for the child side of the relationship.

    Then you can make your perfect report to sort properly (you may need to add a sort field on the parent choices table, and this perfect report can also be filtered, as in other use cases you may want to only offer Active record as choices.

    Then go back to your details records table and on the form where the choices field is, set the form to use your perfect report to drive the drop down list.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • RyanWilliams2's avatar
      RyanWilliams2
      Qrew Member
      2 months ago

      I have table to table relationships set up for many other elements in the app. Adding grants as another table to table relationship will make it incredible complicated. 

      Right now I have a table of Activity Notes that is connected to a contact card for an individual. You can have many activity notes associated with each contact card. Each activity note can have 1 funding source associated with it. If I were to use the many funding sources to 1 note connection, wouldn't I have to have a table specifically called "Funding Source for Activity"? I also have a project table that is connected in a many to one relationship with the contact card. Each project has a funding source as well. I would need to also have a "Funding Source for Project" table. It also adds an "add" step for the people using it, going from 1 click to multiple.

      In both of those cases, I just need a single field in the table to list the funding source and I want that list to be consistent throughout the entire database. Adding a dozen new tables, all with funding source in their name, and also maintaining a dozen manual pick lists is way more work. 

      I really just want to know if I can apply sort criteria to the global table, or if I need to do some manual trickery to make it work. If it is sorted based on row ID, then I just need to adjust my data.



      ------------------------------
      Ryan Williams
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        2 months ago

        Why do you feel that you need to add a dozen new tables.  You just make a relationships back to the same existing table that you have for Funding Sources. So still a single source if truth for the Funding choices.  That same table can be used as the Parent in terms of choices for all the table in your app which now rely on the shared multiple choice list.  Just make that relationship after you ensure that the Key field for Funding choices is set as the text field that holds the choices.

        I suggest that you make a copy of your app so you can do this test and you'll find it very easy to follow the steps I provided to make a relationship and then be in full control of the drop-down sort and filters.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------