SOLCASE TIPS - MARCH 2007

GENERATING A SEQUENTIAL BILL NUMBER

This is very simple to do using an external text file to hold the last number used.  In principal you can do it in a couple of lines of code.  The following will do it where TF300 is the file name and TF302 is the next number. The &Out then replaces the old file with a new file with the latest number in it.

[&Import filename=TF300, delimiter=","]     
   [&Assign TF302=IF01+1]        
[&EndImport]
[&Out TF300:TF302]     

However if you are sharing this across the firm or network you need to make sure that the file cannot be accessed at the same time by two people as this would mean duplicate numbers could be produced. Pretty unlikely with the speed of PCs today but nevertheless possible.

The following script uses the lockfile principle.  Before you open the text file containing the last number you check whether the" lock-file" exists. This is simply another text file in the same directory. If no lock-file exists then you create a lock-file (I have imaginively called it "lockfile.txt" ) to prevent anyone else opening your number file. You then import the number from you number.txt file. You immediately add one to the number and write that back. Then you delete the lock file.

My code goes one stage further and loops 5 times if a loc-kfile exists to give it time to clear if someone else is using it. It then displays a warning and gives the user an opportunity to cancel. Usually by the time the warning box has appeared any lock-file should have disappeared anyway.

SAMPLE CODE

** clear variables
[&Assign TF100=""]
[&Assign TF200=""]
[&Assign TF201=""]
[&Assign TF202=""]
[&Assign TF300=""]
[&Assign TF301=""]
[&Assign TF302=""]
[&Assign TF303=""]
[&Assign TF400=""]
[&Assign TF500=0]
[&Assign TF600=""] This value to be passed back to calling script

** set folder paths
[&Assign TF100="C:\Billing\"]
[&Assign TF200=TF100+"lockfile.txt"] Lockfile location
[&Assign TF300=TF100+"number.txt"]   Number file location

** check for existance of lock file
[&Assign TF201="search('"+TF200+"')":proval]
[&If TF201 <> ""]
   [&Assign TF201="LOCKED"]
[&Else]
   [&Assign TF201="UNLOCKED"]
[&EndIf]

** if locked then loop for a while then give user option to quit
[&DoWhile TF500<51 &And TF201<>"UNLOCKED"]
   [&Assign TF201="search('"+TF200+"')":proval]
   [&If TF201 <> ""]
      [&Assign TF201="LOCKED"]
   [&Else]
      [&Assign TF201="UNLOCKED"]
   [&EndIf]
   [&Assign TF500=TF500+1]
   [&If TF500=50]
      [&If 'Trouble retrieving new ID number. Try again?' (Question)="Yes"]
         [&Assign TF500=0]
      [&Else]
         [&If 'No number obtained. Is this OK ?' (Question)="Yes"]
            [&Assign TF600=""]
         [&Else]
            [&Assign TF500=0]
         [&EndIf]
      [&EndIf]
   [&EndIf]
[&EndDoWhile]

[&If TF201="UNLOCKED"]                     ** if not in LOCKED status
   [&Out TF200:"LOCKED"]                   ** Lock the lock file
   [&Import filename=TF300, delimiter=","] ** open the numbers file
   [&Assign TF301=IF01(number):whole]      ** recover last number
   [&Assign TF302=TF301+1]                 ** increment number
   [&Assign TF303=TF302:whole:text]        ** convert to whole number/text
   [&EndImport]
   [&Out TF300:TF303]                      ** replace old number with new
   [&Assign TF202= "DEL " + QUOTE + TF200 + QUOTE]
   [&OSCommand TF202]                      ** delete lockfile
   [&Assign TF600=TF303]
[&EndIf]

** re-clear variables
[&Assign TF100=""]
[&Assign TF200=""]
[&Assign TF201=""]
[&Assign TF202=""]
[&Assign TF300=""]
[&Assign TF301=""]
[&Assign TF302=""]
[&Assign TF303=""]
[&Assign TF400=""]
[&Assign TF500=""]