Client Support
Showing results for 
Search instead for 
Do you mean 

TRIMPORT imported retention schedules not listed in numbering order

Valued Contributor

TRIMPORT imported retention schedules not listed in numbering order

[ Edited ]

Hi,

(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)

 

e.g.:

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

 

etc.

 

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?

thanks

 

 

 

 

4 REPLIES
Highlighted
HPE Expert

Re: TRIMPORT imported retention schedules not listed in numbering order

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..

 

1

100

11

 

 

The numbers might need to be all 3 digit numbers for them to sort correctly

 

001

011

100

 

Is there a particular reason that you need them sorted?

**Any opinions expressed in this forum are my own personal opinion and should not be interpreted as an official statement on behalf of Hewlett Packard**
Valued Contributor

Re: TRIMPORT imported retention schedules not listed in numbering order

Thanks Greg,

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....

hm...

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.

regards

 

Frequent Advisor

Re: TRIMPORT imported retention schedules not listed in numbering order

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. 

 

 

Regards,

Damitha Bogahawatta
Principal Consultant
Talent Consulting Services
Valued Contributor

Re: TRIMPORT imported retention schedules not listed in numbering order

Thanks all,

Next week I will let you know how it all went.

I will try to use this Excel formula

Regards