SortBy column for lookup field

[NurNina]

Hi I have a table with SortBy columns formula. One of the field,"crc82_ItemIDprodmaster" contains delegation warning. It is a lookup field. The data for that lookup field isn't shown inside the table. How to solve this? 

[WarrenBelz]

Hi @NurNina ,

I did a bit of testing here and confirmed that you cannot Sort by a Lookup column. Will this work for you?

Sort(
   ShowColumns(
      Filter(
         'Sales Order Lines', 
         ’Sales Id' = SalesOrderlD
      ),
      "crc82_itemid",
      "crc82_ItemIDprodmaster", 
      "crc82_inventoryuom",
      "crc82_salesprice”,
      "crc82_salesqty",
      "crc82_discount"
   ),
   "crc82_lineamountnew",
   Ascending
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[yashag2255]

Hi @NurNina 

Can you try out the below formula to directly filter on a LookUp field:

SortByColumns(AddColumns(Filter('Sales Order Lines', ’Sales Id' = SalesOrderlD),"LookUpColText",crc82_ItemIDprodmaster.ColumnToFetch),"crc82_itemid",LookUpColText, "crc82_inventoryuom","crc82_salesprice”,"crc82_salesqty","crc82_discount","crc82_lineamountnew",Ascending)

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[WarrenBelz]

@NurNina ,

@yashag2255 's solution is more suited to your needs than mine (and a very nice way of changing column spec with AddColumns - I have made a note of it).

Parsed below - please accept his solution if this works for you.

SortByColumns(
   AddColumns(
      Filter(
         'Sales Order Lines', 
         ’Sales Id' = SalesOrderlD
      ),
      "LookUpColText",
      crc82_ItemIDprodmaster.ColumnToFetch
   ),
   "crc82_itemid",
   LookUpColText, 
   "crc82_inventoryuom",
   "crc82_salesprice”,
   "crc82_salesqty",
   "crc82_discount",
   "crc82_lineamountnew",
   Ascending
)

[NurNina]

@yashag2255 I have tested this formula but Im not sure what could be the problem because the data in the lookup field is still not shown in the table. 

[v-xida-msft · Accepted solution]

Hi @NurNina ,

Firstly, based on the SortByColumns formula that you mentioned, I think there is something wrong with it. The syntax of SortByColumns function as below:

SortByColumns(Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, ... ] )

Then the"crc82_ItemIDprodmaster" field is a LookUp type field, which is essentially a Record value. You could not specify complex type column as Sort columns within the SortByColumns function.

As an fixed solution, please consider modify your formula as below:

Sort(
   SortByColumns(
              Filter('Sales Oder Lines', 'Sales Id' = SalesOrderID),
              "crc82_itemid", Ascending,
              "crc82_inventoryuom", Ascending,
              "crc82_salesprice", Ascending,
              "crc82_salesqty", Ascending,
              "crc82_discount", Ascending,
              "crc82_lineamountnew", Ascending
   ),
   ItemIDprodmaster.'Primary Name',
   Ascending
)

Note: The 'Primary Name' represents the field in your CDS Entity which the crc82_ItemIDprodmaster" LookUp field references values from. Please replace it with actual field name from your referenced Entity.

Best regards,

[NurNina]

@v-xida-msft hi, thank you. I have tested the formula but data for the lookup field is still not showing inside the table.

[v-xida-msft]

Hi @NurNina ,

Do you want the LookUp field value to be displayed within the Data Table control?

If you want to display the LookUp field value within the Data Table, please select the LookUp column within the Data Table, then set the Text property of the column to following:

ThisItem.ItemIDprodmaster.'Primary Name'

Note: The ItemIDprodmaster represents the LookUp field in your CDS Entity

If you want to display the LookUp field value within a Gallery, same formula logic should be applied to the Label inside the Gallery.

Best regards,

[NurNina]

@v-xida-msft im not sure why but my table version, the column doesnt have any Text property.

[v-xida-msft]

Hi @NurNina ,

Have you enabled "Enable improved data table control selection and Value property" option within Advanced settings of your App settings of your app?

Please consider turn on the "Enable improved data table control selection and Value property" option within Advanced settings of your App settings of your app, then re-insert a new Data Table in your app screen, then try above solution I provided again, check if the issue is solved.

Best regards,