Unfortunately I don’t have the luxury of changing my form reference from an alphanumeric field to a numeric field because I have over 1,000 form instances. The issue did not arise until we reached a reference number of PLA1000, the previous one being PLA999.
As explained in the original thread I tried to create a new field in the report using the substring function to strip out the “PLA” from the reference leaving “999” or “1000” or “1001” etc. Up to 999 seems to be okay, but after that there appears to be a “space” character between the “1” and the next digit, ie 1 001. Declaring this calculation field in the report as a numeric field or general field appears to have little effect.
Basically, I need a method of stripping the numeric part out of an alphanumeric field.
Perhaps you can try creating a simple form with one text field, create a number of form instances with the following data, PLA998, PLA999, PLA1000, PLA1001, PLA1002. Then build a report showing that text field sorted in decending order. Chances are you get 999,998,1002,1001,1000.
Thanks for your interest in this challenge