I have a question about sorting and filtering references.
Let me explain. I have a contact table in which I have two columns called primary category and secondary category. These columns have a reference to my primary category table and my secondary category table.
The secondary category table also references the primary category Table.
Because of this whenever I select the primary category field in my contact table, I get a filtered view in my secondary category field of the secondary categories that match the primary categories. For example let’s say I select family in the primary category field, I will then only see secondary categories like father, mother, child etc.
This is exactly what I want happening. The problem however is, that this list is not sorted alphabetically and any new category I create is shown at the bottom of the reference list. I am aware that I can use either the SORT or the ORDERBY function in the valid_if constraint, but I lose the filtered view when I do this. When selecting a primary category in the contact table, I then get to see all the different types of secondary categories even those that do not match the primary category.
How can I sort the reference list alphabetically and still have the filtered view remain whenever I select a category in the primary category field?
Any help with this would really be appreciated.
Many thanks in advance.