ORDERBY by date - question

Hi Community,

I had a look to many posts and documentation but I don’t get why my expression does not gives the expected result.

I have a list of ID related to a table of prices, which contains [DATE] column (type Date) and I want to sort this ID list by their date.
The final purpose is to get the most recent price.

To achieve that, I use a FILTER expression so that I’m sure I will get a list of key-columns values, and use ORDERBY expression to sort this list according to the date.
The next step will be to use TOP() Expression.

Here is the “basic” expression: defaultPrice_TEST_per1

FILTER(
	"PRIX VENTE",
     AND(
		[Devise]="€",   
          [Qté Lot]="1", 
     	[ID REF]=[_THISROW].[related_ID_Object]
          
     )
)

Here is the “sorted” expression: defaultPrice_TEST_per1_orderedByDate_TRUE

ORDERBY(
  FILTER(
       "PRIX VENTE",
       AND(
            [Devise]="€",   
            [Qté Lot]="1", 
            [ID REF]=[_THISROW].[related_ID_Object]

       )
  ),
  [DATE],
  TRUE
)

And the output I got, which does not make sense to me:
image

Because of this, I didn’t try the TOP() yet because I need my previous expression to give the expected result.

Any clue on how to achieve what I want ?
Or, the list is sorted but this is just a matter of preview display ?

Many thanks in advance

The order of the list and order on the inline view are two different things. You can sort the order from the inline view or if you don’t have it, you can create a table view where the position is set as Ref.

4 Likes

Great @Aleksi , many thanks for confirming a doubt ! :smiley:

@Aurelien ,

@Aleksi 's guidance is as usual with compact and best solution

Just for discussion purpose and to make my and any other rearders’ understanding better, may I request you to mention , if it is a correct understanding that you have created the ORDRBY() expression on table “PRIX VENTE” but the expression exists in another table’s column?

1 Like

HI @Suvrutt_Gurjar

I’m not sure I fully understand your question as English is not my first language, but here is my answer :stuck_out_tongue:

Short answer
No, I used it in another table.

Long answer
This expression is in a “Quote” table.
Because I used the FILTER, I had a list of ref from “PRIX VENTE”, that I called and ordered simultaneously in the table “Quote”.

So, said differently (please apologize if I’m not clear enough :smiley: ):

  1. Table “PRIX VENTE” contains prices:
  • key-column [IDPRIX]
  • column [DATE] that embed the price creation
  • column [ID REF] that refers to item from another table (“ITEMS LIST”)
  1. Table “QUOTE” contains informations, such as:
  • [Quantity]
  • [related_ID_Object], that refers to items from Table “ITEMS LIST”
  • the column [MostRecentPrice], with the formula from previous post:
ORDERBY(
  FILTER(
       "PRIX VENTE",
       AND(
            [Currency]="€",   
            [Qté Lot]="1", 
            [ID REF]=[_THISROW].[related_ID_Object]

       )
  ),
  [DATE],
  TRUE
)

If I simplify this one for understanding purpose:

ORDERBY(
  FILTER(
       "PRIX VENTE", 
      [ID REF]=[_THISROW].[related_ID_Object]         
  ),
  [DATE],
  TRUE
)

@Suvrutt_Gurjar does these elements answer your question ?

2 Likes

Hi @Aurelien ,

Thank you very much for detailed response. I really appriciate.

Based on my understanding and testing I think, that the [Date] parameter in the ORDERBY() is not taken from “PRIX VENTE” table because the expression is in the QUOTE table. I think the expression expects the [Date] column from the QUOTE table.

I created the ORDEBY() expression in the equivalent of “PRIX VENTE” table itself and then used it in the QUOTE table. The method is a bit longish and needs two additional VCs.

So basically 1) ORDERBY() in the column in child table itself (Equivalent of your “PRIX VENTE” table)
2) Then use that date sorted list column as reverse reference column in the parent table ( Equivalent of your QUOTE table) This column needs a bit longer expression.

The main thing I wanted to share was that the ORDRBY() expression seems to expect the ORDERBY column from the same table where the expression exists.

Edit: Minor edits.

2 Likes

@Suvrutt_Gurjar

Thanks for this discussion, I appreciate it as well.
Actually there is no other [DATE] column among all my tables, so I think that would produce an error ?
Furthermore, the result I see matches my expectations.

The behavior you describe is surprising. Do you know if other peoples met a similar situation ?

2 Likes

Hi @Aurelien ,

My observation sharing is based on my testing.

You are correct that since there is no [Date] column in QUOTE it should give error. But still, I believe it expects the [Date] column from the same table, even though it does not show error in expression.
If you wanted to reference [Date] column from a parent to child table , I believe perhaps [_THISROW].[Date] would have worked.

Please test your ORDERBY() expression without any change in a virtual column in “PRIX VENTE” table itself. You will find that it sorts the records by the date and it will show as a date sorted inline view in that table itself.

1 Like

Hi @Suvrutt_Gurjar

Sorry for time to answer, very busy at the moment.
I will proceed to tests tomorrow and will let you know how it behaves !

1 Like

Sure. Thank you @Aurelien

Hi @Suvrutt_Gurjar

(I could not wait till tomorrow :stuck_out_tongue: )

I just read your message again, attentively this time, and I think that will give the same result, wherever I use this formula.
Because of this post from Steve that highlights the equivalence between FILTER and SELECT:

and given the fact that I deeply trust the SELECT mechanism, I think this expression would work in any table.

You will find that it sorts the records by the date and it will show as a date sorted inline view in that table itself.

You are right, and when I use the TOP() expression on it, I get the expected result : the most recent.

2 Likes

Sure @Aurelien. Please do use the expression that you trust and works best.

My entire discussion was centered around why [Date] was not sorting in ORDERBY()

I really appreciate you patiently discussing and updating in this thread. Thank you very much.

All the best.

2 Likes

Hi @Suvrutt_Gurjar

Actually it is ! :rofl:
As @Aleksi highlighted it, this was just a matter of list displaying/UX, but in terms of pure data, the list was sorted.
I went to check with the “View Data” button, and the result was sorted as I expected it :slight_smile:

Thanks for this open discussion and interest in solving my point !
Cheers

2 Likes