02-26-2014 07:18 PM - edited 02-26-2014 07:21 PM
(HP TRIM 7.3.3. 5645. Win 7, MS Office 2010, SQL 2008-R2)
I just used TRIMPORT to import the new retention schedules (From MS Excel, then converted to txt file, around 500 of them) . All went OK except that the schedule numbers are not listed in the order (although being in order in the MS Excel)
Now in (testing) TRIM I have this:
GDA ABC 10.7
GDA ABC 10.8
GDA ABC 10.9
then it jumps to
GDA ABC 100.1
GDA ABC 100.2
GDA ABC 100.3
then continues as
GDA ABC 11.1
GDA ABC 11.2
GDA ABC 11.3
so obviously not in the order as per spread-sheet or how the existing schedules are in order..
A sorting does not help
Why is this happening, is it fixable and how?
Anyone had the similar issue?
02-26-2014 07:48 PM
I wonder if it is because you are sorting 3 digit and 2 digit numbers. I think that TRIM reads the first number, then the second, third and so on e.g..
The numbers might need to be all 3 digit numbers for them to sort correctly
Is there a particular reason that you need them sorted?
02-26-2014 07:58 PM
Well, leading zero 0 could help, I am noticing that all our existing schedules in TRIM are with a leading zero and also even in the Retension Schedule manual, manifest or whatever is called. .
However , this new one does not have these leading zeros....
I will ask some State Records agencies if we can place the zero in front of schedule number.
The reasons why we need them in numerical order are various but mainly for Records Officers to chose the proper retention when creating new files (and all of them should be there grouped by subject and numbers.
02-27-2014 03:21 AM
You can use Excel to add leading zeros for you.
Assuming B column has Schedule numbers and first row contains column headings, add a new column to the spread sheet with the following formula.
=CONCATENATE(LEFT(B2,8),TEXT(RIGHT(LEFT(B2, FIND(".",B2)-1),FIND(".",B2)-8),"000"), RIGHT(B2, LEN(B2)-FIND(".",B2)+1))
You might have to refine it for your requirements. This new column now becomes your schedule number column.
Talent Consulting Services