User attempts to sort by the entity name field and does not behave as expected

Question

I have a Tempo Task report that is showing tasks for a certain process. The Task Report has a column called Entity that corresponds to a Entity ID that is contained in a pv in the process. I am using a separate query to pull all of the relevant Entity CDTs and then using the displayvalue() function to display the Entity Name in the report instead of the Entity ID. So far so good. If a user attempts to sort by the Entity Name field it does not behave as the user expects. Since the column is actually backed by the Entity ID in the PortalReportDataSubset, the report sorts in order of Entity ID instead of alphabetically by Entity Name (the value that is being displayed to the user). Has anyone encountered any good work arounds? 
 OriginalPostID-137632 
 OriginalPostID-137632

Mike West · Accepted Answer

Dan,
I've encountered a similar issue in an entity backed grid with Appian data mixed in (like getting a last name from user() called on a username stored in a table).

What you might do is make a dictionary with all of the data to show up in the grid. Say your portal report returns a list of entityIds and a list of otherFields.
Then make a rule which builds a dictionary:
rule!makeDictionary
Inputs: ri!entity,ri!otherField
Definition: ={fieldLabel: ri!entity, otherField: ri!otherField, ...}
Now apply it over the merged lists:
apply(rule!makeDictionary, merge(local!yourListOfEntitiesYouGotFromUsingDisplayValue, local!otherFields,...))
The point is that now you can use "fieldLabel" as the 'field' in your GridTextColumn.

This has a major limitation, though, in that it only really works well if you can get all of the data in your initial report or query, and only trim things down with todatasubset. Otherwise, you'll just be sorting a single page when you should be requerying to get the first results of the newly ordered data. In this case, I don't know of a good work-around.