Sort values but the sorting isn't ascending, descending or even dependent on alphabet


The task is to show sorted values in a form but the sorting isn’t ascending or descending. Let’s say that there is a field called Group. This field can have only 3 values they are all str: A, B and C. These values must be sorted in this order: B, A, C.

Also, the second field which will be used for sorting is Date field.

And finally, if Group field has value C, then the sorting in these records should happen by field Type: Type can have only 2 values: X and Y, both of them are str. The order must be: X, Y.

What is the easiest algorithm that can be used to implement the given task in AX 2009? Obviously alphabetical orders can’t be used. I’m thinking and Union queries come to mind, any advices?

B, A, C values are not sorted, therefore you can’t achieve your requirement by sorting by this field. Consider creating a hidden numeric field used for sorting and initialize its values as needed.

I understand that it is not sorted. I just used the word “sorted” to show that user has to see the records in this order. Anyway, thank you for the idea, it seems easier than any other solution I was considering.