Renumber my records

1

Hi
I developed a Clarion application a while ago using clarion templates. (TPS)
I had no autonumbering on my records. Now I am using a later application that autonumbers my records. the problem is that the autonumber field shows zeros on the new application using the old file. How can I make the zeros to increment from 1 to whatever and how do I autonumber new records.
My .dct is logos.dct, the autonumber field is log:parishno and my primary sort is by surname , log:surname. Hope you understand this message.

Many thanks

2

I’d write a standalone utility to AutoInc all of the rows where the ID = 0
I’m assuming the file is not a table in SQL
most likely a TPS

the code would look something like this
You might use OutputDebugString messages to follow along as it’s working

 PROGRAM
 MAP
 END

 INCLUDE('TheFileDeclaration.clw'),ONCE

FileAccessMode:DenyAll    EQUATE (10H)
FileAccessMode:ReadWrite  EQUATE ( 2H)

Err     LONG,AUTO
TheFile &FILE
PriKey  &KEY
IDField &LONG
NextID  LONG,AUTO
AlteredCount LONG(0)
 CODE
 TheFile &= YourFile         ! <--- please change with the actual File
 PriKey  &= ThePrimaryKey    ! <--- please change with the actual Key

 OPEN( TheFile, FileAccessMode:DenyAll + FileAccessMode:ReadWrite)
 
 IF ERRORCODE()
     Err = ERRORCODE()
     MESSAGE('Failed to Open ' & TheFile{PROP:Name}         | 
                       & '|Error [ ' & Error()       & ' ]' |
                       & '|ErrorCode[ '& ErrorCode() & ' ]' |
            )
     HALT Err
 END

 IDField &= YourFile.ID  ! <--- please change with the actual Field
 
 CLEAR( IDField, 1)
 ! CLEAR( TheFile.OtheComponentsOfTheKey , 1) ! or -1 if they are descending
 SET( PriKey, PriKey )
 
 LOOP 
   PREVIOUS( TheFile )
   IF ERRORCODE()
      Err = ERRORCODE()
      MESSAGE('Failed to find the Max ID on ' & TheFile{PROP:Name}  | 
                       & '|Error [ ' & Error()       & ' ]'         |
                       & '|ErrorCode[ '& ErrorCode() & ' ]'         |
             )
      HALT Err
   END 
   IF IDField > 0
       NextID = IDField + 1  ! The point of this loop
       BREAK 
   END 
 END

 LOGOUT( 10, TheFile ) ! for performance
 
 SET(TheFile)
 LOOP 
   NEXT(TheFile)
   IF ERRORCODE() THEN BREAK END ! assume we reached end of file
  
   IF IDField = 0 
      IDField = NextID 
      PUT(TheFile)
      IF ERRORCODE() 
         Err = ERRORCODE()
         IF MESSAGE('Failed to save new ID Value['& NextID &']' |
                ,'We hit a snag'  |
                , ICON:Exclamation |
                , '&Continue|&Halt' ) = 2 |
         THEN 
            RollBack()
            HALT Err 
         END 
      END 
    ELSE 
       AlteredCount += 1
       NextID += 1 
    END 
 END 
 
 COMMIT()
 MESSAGE('All done setting AutoIncrements in |' |
                 & TheFile{PROP:Name} |
                 & AlteredCount & ' rows given IDs' )

 CLOSE(TheFile)

Note the code above has a scrollbar

3

Create a Procedure using the Process Template. In the Take Record set the Auto Number and PUT.

You may want to run it 2 ways, have a Message() ask. First set them all = Zero so you can fix any bad attempts. Second set them to the desired value.

4

Old File with no AutoNumbering. Use a run once stand alone process to Put the autonumber in the field.

New App with AutoNumber on.
If using the Old File. Create a new record.

If there is no auto numbers in place, this should start autonumbering from 1 onwards. Edit in Topscan, and change the AutoNumber 1 to the number you want it to start from, which should be after the Old File missing autonumber range. Lets say for examples sake its 12345. The next record auto numbered (2nd auto numbered record) should be 12346 and so on. You can then run your stand alone process, in your own time.

If using the Old File. And the Old File autonumbers have been updated by the stand alone process, then the next record you add will follow on from the last one updated by the autonumber, like you would expect.

Here in the UK, Invoice numbers do NOT have to follow on sequentially.

The taxman uses this sequential number to spot tax fraud.

Likewise competitors buying from you can see how many invoices you have got through between their last two orders/invoices.

If you want privacy from this business intelligence, you can use GUID’s as an invoice number, A random but unique alphanumeric string or run an invoicing number that takes an account prefix and runs that accounts invoice numbers sequentially that way. eg SV for SoftVelocity and then autonumber like so SV0001, SV0002 and so on.

Again Tax man cant work out if you are engaged in tax fraud, which then exposes the charade of privacy with the banks, and there is no privacy with crypto! FYI.

But thats if you want privacy for your autonumbering…

Your TPS/SQL file key would be Primary or Unique Key name, AccountPrefix field, AutoNumber field. A two element key.

The way the ABC file manager class is written, is it Autonumbers on the first field in the Key that has not been primed. Most people only use one field in a key, but provided you prime the AccountPrefix field, the ABC Filemanager class will autonumber the AutoNumber field.

5

Check the file in your dct and make sure that Log:ParishNo is a LONG data type and that you have a key defined for Log:ParishNo. The key will have an attribute, or checkbox, for AutoInc - make sure that checkbox is checked. Let us know if it isn’t!

How many records are in the “Parish” file (I assume that is the name of the file?) ? If it is not too many you can just open the file with CScan.exe and manually add a value for each record. You can also open the file in the dictionary by right click on the file and choosing browse the file. (I think that is how you do it??) That will also let you edit the records (I think?)

If you have too many records to edit manually, the simplest way to add values to Log:ParishNo is just to add this code to a button - you can add a button to any window procedure that uses the Parish file and in the Event:Accepted for the button put this code…

Counter# = 0
SET(Parish)
LOOP
  NEXT(Parish)
  IF ERRORCODE()
    BREAK
  END
  Counter# += 1
  Log:ParishNo = Counter#
  PUT(Parish)
END

Check if the file now has values for Log:ParishNo.

Then, re-build your application and see if the auto-inc woks now.