Is there a way to sort a form-list by an encrypted field?

Technical Questions
1

Use case: provide a screen of employees ordered by their SSNs, ID, or name

Party Identification, has an encrypted field for idValue.

In a form-list, a default-order-by just orders them by their encrypted value, not their display value. I realize it is fairly inefficient to decode them all and then sort by them.

I tried creating a view with a concatenated blank on the end to see if I could coax it out of its encryptedness, but that just gives me the encrypted value back…which indeed is what it is sorted by.

I could grab the entire list, as an entity-find will indeed decode them all, and sort them, but then pagination is shot.

Anyway, have others run into it or come up with another kind of solution?

    <entity entity-name="PartyIdentification" package="mantle.party">
        <field name="partyId" type="id" is-pk="true"/>
        <field name="partyIdTypeEnumId" type="id" is-pk="true"/>
        <field name="idValue" type="text-medium" encrypt="true"/>```
1 Like
2

An encrypted field can be matched by equals but that’s it… the encryption is reliably repeatable to allow for this (encrypt the value to compare with and pass that to the DB).

Options are either sort in memory, don’t encrypt the field, or add another field that can be used as a proxy for sorting. For that 3rd option with a sort proxy field… there may be some sort of hashing algo that hides the SSN/etc but preserves a sort order, but not that I’m aware of… might be interesting to research.

3

Thank you, David.

I figured that was the case.

I took the easy way out, for now, hiding the sort option on that column and giving them an Excel export they can sort to their heart’s content.

4

We have run into similar issues, and we really need to order by idValue. So we used an extend-entity that redefines those fields (idValue in PartyIdentification, mothersMaidenName in Person) to not be encrypted, as both have to be sortable in our case.
In our scenario, neither of them is considered sensitive information (no more sensitive as the first and last name) as it is considered to be a normally known (in many systems the personal ID number is even used as a primary key), and the physical national personal ID document has an additional serial number which is the part that would normally be used for authentication and thus is considered more sensitive.

5

Oh, that’s a great idea…thank you Jens.

Solomon