SORT BY DATE

[peterson]

Hello guys, I need some help.

I have a ref is part of, where I add comments to my order table, related to the events that happen during a negotiation. So in my child table I created a virtual column that concatenates the columns "Comment", "Submitted by", "Date and time". I called this column "Message editada".

Now in my parent table I used the formula

LIST(

SUBSTITUTE(SELECT(Comentários[Messagem editada],[Pedido-ID] = [_THISROW].[ID])," , ","

  _________________

"))

This way I have the following result:

The result is a history of all comments in a chronological order. However, I want the latest messages first. I tried the ORDERBY() But it didn't work because I need to use an ID column, but I have to use the Message editada to bring my messages and not the ID

[Suvrutt_Gurjar]

In your parent table, there should be a reverse reference system  generated virtual column called something like [Related Comentários] 

1. Please add another VC called say [Descending_Comentários] in the parent table with an expression something like ORDERBY([Related Comentários] , [Date and time], TRUE)

Here [Date and time] is the name of the column in the child table that you have used in concatenation in [Message editada] column.

2. Now your expression for the [Comentários] column in the parent table can be

[Descending_Comentários][Message editada]

[peterson]

It doesn't work the way I wanted and I'll tell you why.
When I do this, it returns a sort of inline view, which is not cool for the project for I need to scroll right to see the whole information.
In this case I am using my VC as a long text column, so that I am able to read that as a planned text. I SUBSTITUTE the " , " by a line break and a line just to separate each comment, as you can see in my image above, got it?

It would work perfect if I could use orderby with a non key nor reference column.

[Suvrutt_Gurjar]

okay, I got it. Well, I believe not showing it as an inline view can be addressed with ease.

Please hide the column  [Descending_Comentários] with show_if  and please have the following expression for the  [Comentários] column as 

TEXT([Descending_Comentários][Message editada]) and please set  [Comentários] as long text column.

[peterson]

When I do this, working with references, the other Long Text VC will not return the whole text, instead it returns the ID only. The descending comments does show all as inline view. But when I refer to it, It doesn't return the column I want.

[Suvrutt_Gurjar · Accepted solution]

Well, it works perfectly for me. Please see the comments column below.  If you want I can share the app with you. Please DM me your email ID.

[Suvrutt_Gurjar]

You are welcome. 

---

@peterson wrote:

, you're using the ORDERBY() in the ID to create a list

---

Yes, you are correct. ORDERBY() expression sorts the child table's records in descending order by datetime. We then use this column of descending ordered keys to dereference comments from child table in the parent table's comments column.

---

@Suvrutt_Gurjar wrote:

TEXT([Descending_Comentários][Message editada]) and please set  [Comentários] as long text column

---

Just one change in the above suggestion is to use the following expression to put each comment in a separate line. With the above expression, there will not be line breaks in different comments.

SUBSTITUTE([Descending_Comentários][Message editada], ",", "
")

[peterson]

Now I understood the logic, you're using the ORDERBY() in the ID to create a list, and now I can use this sorted list to create my text column, Thanks!