Sorting a report on custom field not in ascending or descending order

[DavidKr9]

Hopefully someone has done this and has some text mode samples to share to accomplish.

I have a custom field on a form where the values are from a custom list.  We entered the list values on the field in the order the user should see them.  As a result, not in alpha order.

How can I have a report sort on this field (field values) by the order it is listed in the values to choose and not in alpha order?

Thanks/appreciated in advance!

David

[JustinRenteria2 · Accepted solution]

What we do in situations like this is create a separate calculated field that we then use to sort. The calculated field assigns a numerical value to each possible value the field can have.  Below is an example of the calculation. 

IF({Your field} = "Value 1", "10",
IF({Your field} = "Value 2", "20", 

IF({Your field} = "Value 3, "30", )))

Once the field is created you can insert it into the textmode code for your column.  
The key is to replace the field on the column's "querysort" line with your sorting field as shown below
querysort=DE:Your sort field

[JustinRenteria2]

Forgot to mention: make sure your calculated field is a number field!

[DavidKr9]

THANK YOU!!!!

I did some testing (took me a little longer not knowing code syntax) but think it worked!  Appreciated and posting samples below for others as reference

Sample of the code on the calculated field on the form (Phase is the original custom field)

IF({DE:Phase}="SOW","10",

IF({DE:Phase} = "Design", "20",

IF({DE:Phase} = "New","30")))

Sample of the text mode under report groupings (the calculated field I created based on the above is "Phase sorting"

Report works (S first, then D as desired)!