Sort at TPS file

questions
1

Is there a way to sort a tps file. I import data from a csv and would like to sort by state,city,name. I know this can be done in a queue but looking for a simple way.

2

If you want to sort a TPS file by something you need to declare keys on the table definition.

3

The TopSpeed driver supports dynamic indexes. The BUILD statement allows to build dynamic indexes using the list of components and filter specified at run-time. See topics for INDEX and BUILD in documentation.

4

Indexes not very inefficient (It has to build the index first) and this is not multi-user friendly (If another user chooses a different index it corrupts the 1st one)

5

A way to view the file in an Ad Hoc order is to setup a Browse but do not pick a Key. Then in the template pick Additional Sort Fields and specify them. I don’t think this allows a locator, at least not in Legacy. You can do the same in a Process or Report.

image

These all use the VIEW engine and ORDER() or Prop:Order. You can hand code your own View with Order. Easy way to do that is set it up in a Process template to see the code. You must code the View code in a specific order i.e. Open / Set, etc.

image

In ABC the code is hidden inside the process class. Legacy generates more Clarion language code.

Process:View         VIEW(Customers)
                     END

  ThisProcess.Init(Process:View, Relate:Customers, , )
  ThisProcess.AddSortOrder()
  ThisProcess.AppendOrder('+CUS:State,+CUS:City,+CUS:Company')
...
2 Likes
6

Concurrent work with the same ISAM file requires synchronization regardless uses of INDEXes.

If all required sort orders of file records are known, KEYs are enough. Otherwise, there are only 2 variants: either to use dynamic INDEXes or load entire table (or filtered records) to a queue.

1 Like
7

Thanks everyone. I am using the legacy to pull out a subset of a larger tps file as follows.

  ! Start of "Legacy: Activity for each record"
  ! [Priority 4000]
  BRO:MCNUMBER=sub(Car:MCNUMBER,3,8)
  get(BROKER,BRO:BY_MCNUMBER)
  if ~error()
     mod:record=bro:record
     append(BroMode) 
  end

    ! Start of "Legacy: End of Procedure, After Closing Files"
    ! [Priority 4000]
    build(BroMode)

Works fine except the state,city,name is no longer sorted.

Checking to see if I can put Carl’s post into play. Have not had to work with this data in years.

8

Check for Errors. The build(BroMode) needs the file to be Open so probably getting error 37 file not open.

Rather than APPEND() (that does not update Keys) instead use ADD() and STREAM / FLUSH to make it faster.

I use the below Err4Msg() function to make code to display complete error info easy e.g.

   Build(BroMode)
   IF ERRORCODE() THEN 
      Message('Xyz failed Build(BroMode)' & Err4Msg(),'Build Error')
!or
      Stop('Xyz failed Build(BroMode)' & Err4Msg())
   END

Err4Msg              FUNCTION (Byte FmtType=0)!,STRING 
  CODE 
    IF ~ERRORCODE() THEN RETURN ''.
    CASE FmtType
    OF 1            !the 1 line format for use by logging
        RETURN  ERRORCODE()&' '&CLIP(ERROR()) & |     ! {148}
            CHOOSE(~FILEERRORCODE(),'',' [Driver ' & CLIP(FILEERRORCODE())&' '&CLIP(FILEERROR()) &']' ) & |  !driver error in []   !8/30 Carl was CHOOSE(ERRORCODE()<>90 but err 47 too, assume blank
            CHOOSE(~ERRORFILE(),'',' {{' & CLIP(ERRORFILE()) & '}' )   !Just throw a line break so the file is below

    END
    RETURN  '<13,10><13,10>Error<160>Code:<160>' & ERRORCODE()&'<160>'&CLIP(ERROR()) & |     ! {148}
            CHOOSE(~FILEERRORCODE(),'','<13,10>Driver<160>Error:<160>' & CLIP(FILEERRORCODE())&'<160>'&CLIP(FILEERROR()) ) & | !8/30 Carl was CHOOSE(ERRORCODE()<>90 but err 47 too, assume blank
            CHOOSE(~ERRORFILE(),'','<13,10>File<160>Name:<160>' & CLIP(ERRORFILE()) )
9

Thanks Carl, I really appreciate the help. Have stream/flush and moved the build to before closing. Everything seems correct. This is truck data from the Department of Transportation - They changed all the data reporting so been some work to put back together. This is a subset just the 358500 companies in interstate operations from the total of around 3 million. Thanks again.

Don Harvey
www.truckingregister.com

1 Like