SortByColumns on Choices field

Like (0) Share

Report

Posted on 17 Jun 2022 13:12:54 by trjackson77

254

I need to sort by columns on a choices field that I am adding through the Add Columns function. I have followed the steps elsewhere to pull the 'value' into a 'text' column by using multiple Add Column functions. However, when I try to wrap this in a Sort By Columns function it ignores the sort completely.

SortByColumns(
 AddColumns(
 AddColumns(
 RenameColumns(
 Filter(
 tjTransportPlanItems,
 INTDeliveryDate = varINTTransportDisplayDate,
 WarehouseCode <> "DD"
 ),
 "SalesOrderNumber",
 "tjSalesOrderNumber"
 ),
 "Vehicle",
 LookUp(
 BulkTransportPlanItems,
 tjSalesOrderNumber = SalesOrderNumber
 ).TransportVehicle,
 "Load",
 LookUp(
 BulkTransportPlanItems,
 tjSalesOrderNumber = SalesOrderNumber
 ).TransportLoadNumber,
 "LoadOrder",
 LookUp(
 BulkTransportPlanItems,
 tjSalesOrderNumber = SalesOrderNumber
 ).TransportLoadOrder
 ),
 "serVehicle",
 "Vehicle",
 "serLoad",
 "Load"
 ),
 "serVehicle"
)

Can anyone point me in the right direction, please?

Categories:

Using Apps Using Formulas

All responses (5)

Answers (1)

Verified answer

trjackson77 254 on 17 Jun 2022 at 14:48:01

Like (0)

Report
Re: SortByColumns on Choices field

OK, so I managed to fix the issue "Cannot Sort On Expression Type. The solution was to wrap the Lookup of the OptionSet value in a Text Function when Using the AddColumns Function. Example Below:
LookUp( BulkTransportPlanItems, tjSalesOrderNumber = SalesOrderNumber ).TransportVehicle,
trjackson77 254 on 17 Jun 2022 at 14:24:36

Like (0)

Report

Re: SortByColumns on Choices field

Ah ok, this revealed the problem, my code was inserting the string literal "Vehicle" in the column "serVehicle" instead of the value of the "Vehicle" column.

However, this puts me back to the first error in that I am trying to sort on a Choices column and am getting the error: Cannot Sort on the expression type
Abhilash_Swain 901 on 17 Jun 2022 at 14:12:14

Like (0)

Report

Re: SortByColumns on Choices field

Hi @trjackson77 ,
Its lil difficult for me to reproduce the same scenario right now.
Could you please store the value into a collection and share what are the values each column is getting.
By this we can find, if the columns are storing text values or records.
trjackson77 254 on 17 Jun 2022 at 14:01:45

Like (0)

Report

Re: SortByColumns on Choices field

Thanks @Abhilash_Swain ,
This was my first thought. But I get the error "Cannot Sort On The Expression Type", this is because the column I'm trying to sort on is a Choices column. My solution was to select that value in the additional Add Columns function with the "serVehicle" column. This now appears to work (at least it does not error), but I am stuck with my original problem of the results are unsorted.
Abhilash_Swain 901 on 17 Jun 2022 at 13:54:55

Like (0)

Report

Re: SortByColumns on Choices field

Hi @trjackson77 ,
I believe you have added an extra AddColumns.
Remove 1st AddColumns ( and last ,"serVehicle" ) from your formula and hope it will work.