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:
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
Solved! Go to Solution.
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.
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.
Great @Aleksi , many thanks for confirming a doubt !
@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?
Iโm not sure I fully understand your question as English is not my first language, but here is my answer
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 ๐
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 ?
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.
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 ?
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.
Sorry for time to answer, very busy at the moment.
I will proceed to tests tomorrow and will let you know how it behaves !
Sure. Thank you @Aurelien
(I could not wait till tomorrow )
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.
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.
Actually it is !
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
Thanks for this open discussion and interest in solving my point !
Cheers
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |