Re: TRIMPORT imported retention schedules not listed in numbering order (269 Views)
Reply
Frequent Advisor
PerthRM8
Posts: 83
Registered: ‎02-02-2014
Message 1 of 5 (288 Views)

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

 

 

 

 

Please use plain text.
HP Expert
Greg Fraser_1
Posts: 724
Registered: ‎01-02-2002
Message 2 of 5 (269 Views)

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**
Please use plain text.
Frequent Advisor
PerthRM8
Posts: 83
Registered: ‎02-02-2014
Message 3 of 5 (261 Views)

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

 

Please use plain text.
Frequent Advisor
Damitha
Posts: 67
Registered: ‎05-05-2010
Message 4 of 5 (229 Views)

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
Please use plain text.
Frequent Advisor
PerthRM8
Posts: 83
Registered: ‎02-02-2014
Message 5 of 5 (208 Views)

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

 

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation