“SortByColumns” on a column from a relationship among tables.

Question?

Can we sort gallery filtered values on data located in a table connected to the gallery source?
ERD table example:

Issue.

When using the gallery control to display and filter information based on a selection could be sometimes tricky. One gallery will display the information of the table “Room Class.”

The second table needs to display the materials in that room, sorted by the material name.

The filter is working without the “SortByColumns” – function.

Filter(
        'Material Templates',
        'Room Class'.demo00_name = galRoomClasses.Selected.demo00_name
    )

Adding “SortByColumns” – function, with code to sort by the column “SubName”

SortByColumns(
    Filter(
        'Material Templates',
        'Room Class'.demo00_name = galRoomClasses.Selected.demo00_name
    ),
    Materials.SubName,
    Ascending
)

Getting the following message:

The function ‘SortByColumns’ expecting a text value!

Resolution.

How do we resolve the issue, we have?
Looked at different options and avoiding delagation warnings, the best solution here is to use the “AddColumns” – function. Adding a column with the value of the material column SubName will give us the possibility to sort on that value. The table structure is not changed, I will add just the new values of the material table.

SortByColumns(
    AddColumns(
        Filter(
            'Material Templates',
            'Room Class'.demo00_name = galRoomClasses.Selected.demo00_name
        ),
       "MaterialName",
        Material.SubName
    ),
   "MaterialName",
    Ascending
)

If you like this blog. Please give me a dumbs up or share the blog.