- November 9, 2010 at 1:22 pm #616
I have a report which is basically a data list of fields from the form I’m reporting on. I am trying to sort the report on a field (called PLA number)which is basically in the format of PLAnnnn. eg. PLA999, PLA1000, PLA1001 etc. I’m sorting on descending order and obviously get PLA999, PLA1001,PLA1000 as it is alphanumeric. I’ve created a new hidden field called PLA# and made it a substring of the PLA number field as follows:
Substring(PLA number,3,5). Un-hiding this field on the report appears to show the correct values, ie 999,1000,1001, however trying to do a sort on descending order appears not to work or indeed do anything. I’ve even tried bringing the field unhidden to the first position in the data list.
Any help would be appreciated.November 9, 2010 at 8:44 pm #5484
Use your hidden numeric field in the data list and sort by descending order based on that field. Make that column of the data list hidden. When the report opens it will automatically be sorted by descending PLA numbers.November 10, 2010 at 9:21 am #5487
I had already tried this and it didn’t seem to work. Using the data in the original post, ie. PLA999, PLA1000, PLA1001, the substring results (hidden column) display correctly as 999, 1000, 1001. Sorting on this data in descending order gives 999, 1001, 1000.
Was wondering if the results of the substring were still alphanumeric and causing a problem.
ThanksNovember 10, 2010 at 4:17 pm #5488
I would definitely use a numeric input field. That is how I built my test that worked properly.November 10, 2010 at 4:53 pm #5489
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 challengeNovember 11, 2010 at 11:36 pm #5497
So your issue at this point is only with getting the sub text function to work like you need it to. As long as your new helper field contains the properly ordered numbers, your report will work fine.
The sub text1 function is a bit tricky. The start position will use that exact space. For example using “012345” the value for start position 1 is “1”. But the end position uses the space before that. For example using “012345” the value for end position 4 is “3”. This is actually going to get changed in a future release, but for now this is how it works.
You must be logged in to reply to this topic.