Qrew Legend... 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.
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.
Qrew LegendWhy 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.
