Sort behaviour for lookup fields from a virtual entity in #Dataverse and #PowerAppsTweet
I recently refactored out a large dataset from a Dataverse entity to a virtual entity, hosted in CosmosDB to save on storge costs and to scale out performance of the integration which is generating the data.
However we noticed that sorting on lookup columns resulted in some strange behaviour and seemingly sorted in no logical order
After a bit of research, if turns out that, for virtual entities, the column sorts on the underlying GUIDs values and not on the Name string mask as we typically expect from native Dataverse tables
As far as I’m aware, there’s no configuration options that allow us to control this behaviour – If you know of any, please drop a comment below!
Thankfully in my source Cosmos data I am also storing the name mask, but hadn’t been mapping it to Dataverse, given that the system automatically masks the GUID from the reference data entity. There also, thankfully, isn’t a business requirement for users to click through to the reference records. So my solution was to map the GUID to a lookup field, and also the string value to a new standard text column.
I have added the string column to system views, allowing the user to correctly sort the data, but left the real lookup on the table, and on system forms to maintain the relational integrity of the data model. Best of both worlds!
Has anyone else come up against this issue? Have you found any hidden customisation options, or better solutions? If so, please drop me a comment below! =D