Can you order lists by a field caption order?

1 minute read time.
In the demo database the workflow for an opportunity allows an opportunity to have its stage field set to different values.

DealLost
Demo
Lead
Negotiating
Qualified
Quoted
Sold

Imagine a requirement to sort a list of opportunities by the oppo_stage. That is easy, we can just allow the option 'sort by' to be set on the column and the list can then be sorted in alphabetical order of the values in the database. But now what if the requirement is not to sort the list on the actual data held in the opportunity table but instead they want it sorted based on the order in which the stages are shown in the oppo_stage drop down selection field.

This maybe

Lead
Qualified
Demo
Quoted
Negotiating
Sold
DealLost

The ordering in the list is a product of the capt_order field of the custom_captions. The translations for the oppo_stage field options belong to the capt_family 'oppo_stage' and the values of the different options are held in the capt_code field.

To solve the need to have a list of opportunities that can be sorted by caption order I created a new view.

My new view was based on the existing vListOpportunity view.
SELECT dbo.Custom_Captions.Capt_Code, dbo.Custom_Captions.Capt_Order AS Oppo_StageOrder, dbo.vListOpportunity.*
FROM dbo.vListOpportunity INNER JOIN
dbo.Custom_Captions ON dbo.vListOpportunity.Oppo_Stage = dbo.Custom_Captions.Capt_Code
WHERE (dbo.Custom_Captions.Capt_Family = N'oppo_stage')
The capt_order has been aliased as oppo_stageorder.

Once I created my new view, I then created the new list to display the opportunities and added the allow sort by option to the 'oppo_stageorder' column of the list.