Suggested Value sorted by timestamp

Quentinlebg
Participant II

Hello,

My goal is to sort my data by timestamp, but I can’t figure it out…

I tried doing that:
SELECT(Quote[Title], in([Timestamp],SORT(SELECT(Quote[Timestamp], isnotblank([Title] = [_THISROW].[Title])), false))).

I thought that using sort() like that would change the list and put it in order but it doesn’t. It is the same as if I did Quote[Title]. I was told to use ORDERBY() but even with it I can’t figure it out.

Any suggestion would be appreciated!


Solved Solved
0 16 340
1 ACCEPTED SOLUTION

Replace the entire ORDERBY() expression I provided earlier with this:

ORDERBY(
  FILTER("Quote", TRUE),
  [Timestamp],
    FALSE
)

Then use this as your Suggested values expression:

([Ordered Quotes][Title] - LIST(""))

View solution in original post

16 REPLIES 16

Steve
Participant V

To do this, you’ll need to add a column with an App formula expression like:

ORDERBY(
  FILTER(
    "Quote",
    AND(
      ISNOTBLANK([Title]),
      ([Title] = [_THISROW].[Title])
    )
  ),
  [Tiestamp],
    FALSE
)

If the column above is named Ordered Quotes, you could then get the ordered list of quote titles with this expression:

[Ordered Quotes][Title]

Can the new column be a virtual column?

Because if it is the case, it is not working. I put the formula in the virtual column, and then did “[Ordered Quotes][Title]” in the suggested values of Title and I end up with nothing in the app.

Yep.

Please post a screenshot of the column’s configuration.

Open the App formula expression and try the Test button. Does it produce the results you’d expect? It should be a list of key column values of the rows of the Quote table that have the same Title value, in Timestamp order.

I think I didn’t explain correctly my goal. What I’m trying to do is showing the previous quotes made in title pretty much like that.

The issue I have is that it is not sorted the way I want. I want the most recent ones on top and the oldest ones on the bottom.

So you’re getting results, but they’re sorted in the wrong way? In the ORDERBY() expression I gave you previously, change FALSE to TRUE:

3X_2_1_21fd2e9c77622c3f8972ad3bc861fc3cd3bb6922.png

Weirdly, now that I put TRUE, It is not showing anything, and if I put it back to FALSE it’s not showing anything either…

I see I made a typo in my expression: [Timestamp] was misspelled as [Tiestamp]. I assume you corrected that?

Please post a screenshot of the complete ORDERBY() expression you’re now using (that isn’t working).

I feel like the problem is “[Ordered Quotes][Title]” in the suggested values that does not affect it correctly.

Please try the Test button. Is the expression producing a list of key column values of the desired rows in the desired order?

No, whether it is TRUE or FALSE, it is not showing in the desired order.

Okay. You’re trying to generate a list for Suggested values, right? For which column are these suggestions?

Yes, I want it in Title. So I want Title to have suggested values of Title ordered by Timestamp. I hope it makes sense

Replace the entire ORDERBY() expression I provided earlier with this:

ORDERBY(
  FILTER("Quote", TRUE),
  [Timestamp],
    FALSE
)

Then use this as your Suggested values expression:

([Ordered Quotes][Title] - LIST(""))

Alright! It works, I just had to change FALSE by TRUE.

Thank you very much!!

Top Labels in this Space