How to sort related records

[HCF]

I am tying to sort related records to my Contacts table on a column from a referenced table, but as the editor can’t find the column, I am stuck. I wanted to do something along these lines:
OrderBy(
select(Activities[Contacts],
in([_thisrow].[UniqueID], [Contacts])
),
[SortColumn]
)

Thanks for any help.

[khuslid]

Hi HCF.
Instead of “in([_thisrow].[UniqueID], [Contacts])”, you should try [_thisrow].[UniqueID] = [Contacts]

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[HCF]

Hi and thanks, but this is not what I try to accomplish. The [Contacts] field can contain many contacts, thus the In() function.
What I need to do, is to sort the Activities on the [SortColumn], but since this column refer to a column in another table (Activities and not Contacts), the column is not available in the expression builder. Is there a way to make an expression to sort on that column?

[khuslid]

Aha.
I asume type is REF then.
To sort a column from the REF table, simply
ORDERBY(FILTER(“Tablename_ref_table”, TRUE), [Sort_column])
You can use this either in “Valid if” or “Suggested values”.

(I hope I understood…)

[HCF]

Yes, thats right. The type is REF, and I tried to sort with OrderBy(), but the column from the other table is not a valid column.

[Aurelien]

Hi @HCF and @khuslid,

With you original expression:

OrderBy(
  select(Activities[Contacts],
     in([_thisrow].[UniqueID], [Contacts])
  ),
  [SortColumn]
)

The [SortColumn] must belong to the Activities table, this is the condition for using ORDERBY() (in my opinion).

You say:

What I need to do, is to sort the Activities on the [SortColumn], but since this column refer to a column in another table (Activities and not Contacts), the column is not available in the expression builder

I think you pointed it out correctly: you are having trouble because you refer to a column, which refers to another table.

You may want to try this workaround : please create a virtual column in your Activities Table, which is a kind of copy of the [SortColumn], this way:
name : sortColumn_copy_VC
Type : Text
Expression : [SortColumn].[labelColumn] (or any column you think is useful for your sorting)

and change your original expression with:

OrderBy(
  select(Activities[Contacts],
     in([_thisrow].[UniqueID], [Contacts])
  ),
  [sortColumn_copy_VC]
)

Does it make a change ?
EDIT : change made with the workaround

[HCF]

Thanks, I made a new sort order for the view, that solved the issue.
Looking forward to see some more options with regard to sorting - like sorting on Slice level.

[khuslid]

Glad you worked it out.

PS: You can use slices the same way as tables and sort on them as well - just so you know;)

[HCF]

Hi, thanks, I have not found a way to sort slides. How do you do that?

[khuslid]

Normally you would let a view (table or deck) be based on a slice (after filtering out what ever you’d like).
And then you sort in the view.
Is that what you meant?

[HCF]

I thought you had found a way to sort the slices itself. That would be very nice to have, since you may then set a sort order that apply for all views.

[khuslid]

But the slice is not a view. Why would you want to sort that…?
I’ve built some hundred apps now, and never had the need to sort other than views or WF templates…
It is one click in the view, and then it’s sorted.

[HCF]

It depends. I have some apps with very complex sorting and it may change from time to time - then it’s much better to have the option to sort a slice rather than to iterate through all the different views.