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.
Hi HCF.
Instead of โin([_thisrow].[UniqueID], [Contacts])โ, you should try [_thisrow].[UniqueID] = [Contacts]
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?
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โฆ)
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.
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
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.
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;)
Hi, thanks, I have not found a way to sort slides. How do you do that?
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?
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.
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.
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.
User | Count |
---|---|
39 | |
35 | |
29 | |
23 | |
18 |