Grid Data Sort using QueryEntity

Hi,

I have a use case where I am fetching data from queryProcessAnalytics and a column inside that is having an primary key id, I want to sort the grid using that column. But the values are not in order in the DB for this table. So sorting happens for primary key. The report is also not supporting the queryEntity rule to fetch the correct value in the report itself, neither I am able to give the queryEntity in the field parameter of the gridTextColumn.

FYI- The id is of reference data added in DB.

Please propose a solution for the same.

 

Thanks

Ekansh Jain

  Discussion posts and replies are publicly visible

Parents Reply Children
  • Yes, if those ID values are present in the data subset you can give it the field parameter of gridTextColumn (the GridTextColumn can be any other field like name date anything , sorting will be based on the field parameter given). The field parameter and the data value need not be same.
  • 0
    Certified Lead Developer
    in reply to ekanshj

    Correct me if I'm wrong, but I think what you want to do is retrieve some data using a!queryprocessanalytics(), use an ID in a column of that data to retrieve the relevant text value from a reference table, and then sort the whole dataset using the text value that has been retrieved for each reference ID.

    The only/best way of doing this is to:

    • Retrieve all relevant data using a!queryprocessanalytics()
    • Retrieve the appropriate reference data for all unique reference IDs in that data
    • Combine that data to produce a dictionary on which you can then use todatasubset() in order to page /sort correctly

    So... first retrieve all relevant process data into a local variable. Then, populate a second local variable that contains the reference data for all unique reference IDs within the process data, using union() to produce a list of unique reference IDs as part of a query entity of the reference table on ID.

    Once you have those two datasets, use a!forEach() to loop over the process data, producing a dictionary equivalent to the process data (ie containing the appropriate fields, though maybe with more helpful names), and use displayvalue() to add an extra field to the dictionary that contains the text value for the reference ID. Note that i'm specifially saying use displayvalue here so that we DO NOT loop a queryEntity as has been done in your original expression - this is a very bad idea as it can cause performance issues.

    Anyway, once you have the dictionary, use todatasubset() to produce a datasubet you can map to the grid, using an appropriate paging info with a sort on the extra text field to order the values as you require.

    Hopefully that makes sense! :)