Suggested Value sorted by timestamp

[Quentinlebg]

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!

[Steve]

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]

[Quentinlebg]

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.

[Steve]

Quentinlebg:

Can the new column be a virtual column?

Yep.

Quentinlebg:

I put the formula in the virtual column,

Please post a screenshot of the column’s configuration.

[Quentinlebg]

[Steve]

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.

[Quentinlebg]

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.

[Steve]

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:

[Quentinlebg]

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…

[Steve]

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).

[Quentinlebg]

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

[Steve]

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

[Quentinlebg]

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

[Steve]

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

[Quentinlebg]

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

[Steve · 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(""))

[Quentinlebg]

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

Thank you very much!!