[SOLVED] I have a question about sorting and...

[Cubaine]

[SOLVED]

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.

[Cubaine]

@tony Awesome! thanks a lot. As you said, I managed to do it by entering the following formula in the secondary category field in my contacts table:

ORDERBY([First Category:].[Related Categories], [Second Category:])

[Cubaine]

@tony thanks for responding.

I’ve entered the following formula:

ORDERBY(REF_REL_Second_Category[First Category:].REF_REL_Second_Category[Second Category:],REF_REL_Second_Category[Second Category:])

However I receive the following error: ORDERBY has invalid inputs

[tony1]

@Guillermo_Perez You could try using this formula, which should preserve the filtering:

ORDERBY([Primary Category Choice].[Related Secondary Rows], [Secondary Row Name])

How this formula works: - [Primary Category Choice] is a ref to the primary category that the user has chosen - [Related Secondary Rows] is a column that contains the related secondary categories for each primary category

You’ll need to replace those with the actual column names.

[tony1]

@Guillermo_Perez I think your syntax is not correct. In the formula I posted, the first argument to ORDERBY is in the form [column1].[column2].

column1 should be the name of the first dropdown that the user selects

column2 should be the name of the column in your “primary categories” table that has the list of related rows. It is probably something like “Related XXXX By YYYY”.

The second argument to ORDERBY should be the column you want to sort by (probably the name or row label).

[tony1]

@Guillermo_Perez Glad to hear it!