Answered

Quick Q: Sort formula grammar

(1) Share

Report

Posted on by BP-19081954-0

I'm wondering if someone could help me with a basic question. I'm using Dataverse. Why does the following formula work when Sort, according to the official docs requires a "Formula"

Sort(
    Distinct(
        Filter(
            Projects,
            Self.SearchText in Customer
        ),
        Customer
    ),
    SortOrder.Ascending
)

Should it not require me to enter as the following:

Sort(
    Distinct(
        Filter(
            Projects,
            Self.SearchText in Customer
        ),
        Customer
    ),
    ThisItem.Customer,
    SortOrder.Ascending
)

Categories:

Using Apps Using Formulas

All responses (5)

Answers (2)

CarlosFigueira on at

Like (2)

Report

Quick Q: Sort formula grammar

Some internal details about the sorting... if you don't find it interesting, feel free to ignore :-)

The second argument to the Sort function is typically a field from the table being sorted. For example, if we have this table Contacts:

First Name	Last Name	Age
John	Doe	33
Jane	Roe	32
Joan	Coe	34
Jim	Poe	31

Then the call to Sort(Contacts, Age) would order the

First Name	Last Name	Age
Jim	Poe	31
Jane	Roe	32
Joan	Coe	33
Jim	Poe	34

But the second argument does not need to be a field from the table, it can be an arbitrary Power Fx expression. So the call to Sort(Contacts, Mod(Age, 4)) is perfectly valid (it may not be delegated, but it can be executed locally). What would happen internally would be something similar to the creation of a new table with the records and the "new sort field":

First Name	Last Name	Age	<sort field>
John	Doe	33	1
Jane	Roe	32	0
Joan	Coe	34	2
Jim	Poe	31	3

which sorted would become

First Name	Last Name	Age	<sort field>
Jane	Roe	32	0
John	Doe	33	1
Joan	Coe	34	2
Jim	Poe	31	3

and the result would be the table sorted by that new field:

First Name	Last Name	Age
Jane	Roe	32
John	Doe	33
Joan	Coe	34
Jim	Poe	31

Taking it further, since the second argument can be any Power Fx expression, it doesn't need to even reference any field from the table. A call to Sort(Contacts, Rand()) would be similar to a call to Shuffle(Contacts) - the order would be randomized. Or a call to Sort(Contacts, "hello") would generate an internal table similar to

First Name	Last Name	Age	<sort field>
John	Doe	33	"hello"
Jane	Roe	32	"hello"
Joan	Coe	34	"hello"
Jim	Poe	31	"hello"

Notice that any order of the entries returned by this function call would be technically correct - since the sort field is always "hello", then no record is "greater" or "smaller" than the other (I believe today the result is the original order, but I wouldn't rely on that).

And that's the same case as a call to Sort(Contacts, SortOrder.Ascending). The value of SortOrder.Ascending is being used as the sort field for all records (since it's being used in the second argument, not the third one), so in this case they all have the same sort order. And that's why the call doesn't produce any error. It's doing exactly what the expression asked it to do, even though it likely isn't the intent of the author. We may add some warnings in the future to prevent this from being a surprise, though.

WarrenBelz 141,157 on at

Like (1)

Report

Quick Q: Sort formula grammar

HiCarlosFigueira,

You are correct - I did some testing and the strange thing is that the code without the field does not produce an error, but does not work (it ignores the SortOrder)
CarlosFigueira on at

Like (2)

Report
Quick Q: Sort formula grammar

I see that the problem seems to have been solved, but I wanted to clarify why the original expression "works". You are correct that the Sort function requires a formula from which to sort the data - and you are passing a formula to it: SortOrder.Ascending. This is a constant expression, so the Sort function will not change the order of its first argument (the result of Distinct).

Sort( Distinct( Filter( Projects, Self.SearchText in Customer ), Customer ), SortOrder.Ascending // <<< this is a formula, will sort based on that )

It's similar to something like Sort(<a table>, 1): 1 is an expression, it doesn't refer to any records from the table, but it is an expression nonetheless. Not very useful expression, but it's valid according to the language rules.

Hope this helps!
Verified answer

WarrenBelz 141,157 on at

Like (2)

Report
Quick Q: Sort formula grammar

Hi BP-19081954-0

The actual "full" code would be
Sort( Distinct( Filter( Projects, Self.SearchText in Customer ), Customer ), Value, SortOrder.Ascending )

You can also delete the SortOrder as Ascending is the default.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee
Verified answer

ANB 6,926 on at

Like (2)

Report

Quick Q: Sort formula grammar

Hi,

May be I didnt get the exact question, but if you see the syntax of Sort:

The Distinct function evaluates a formula across each record of a table and returns a one-column table of the results with duplicate values removed. The name of the column is Value.

Because, the output of distinct function is always with one column, then it is not require to specify ThisItem

Please check https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-distinct

-----------------------------------------------------------------------------------------------------------------------------

I hope this helps.

Please click Accept as solution ✅ if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs up. 👍

Thanks,
ANB