How I created the Psion version of my freight management program

I have three other versions of my freight management program programmed with the 4D relational database, Livecode and most recently, Google Sheets but I keep returning to the version which I created on my Psion pocket computer.

 

The advantages of the Psion version are:

• It is portable. I can take it with me out into the garden - it fits into my jeans pocket
  
• I can consult it and update entries in real time rather than having to go indoors and fire up the laptop and consult print-outs
• I can read the screen which, unlike my phone, gets even clearer in bright sunlight
• It was cheap. I re-discovered it in an old briefcase in the cupboard under the stairs.
• It's very easy to program. The OPL programming language is quite high level though not quite as conversational as that used by Livecode.
• It has a keyboard which I find a lot easier to use than the touch-screen on my phone

The (to me, minor) drawbacks are:

• The text is quite small - but still quite readable - and no smaller than that on my phone screen
• It has only 1Mb of RAM and so the programming has to make minimal demands on system memory. However, it does mean I have to be more efficient with my programming

I have detailed the way the program works in my previous blog post, however, since writing that, I have made a few improvements to the way it works and also made amendments to the coding as it would sometimes run out of system memory. 

The changes I have made are:

• the option for randomising the locations of wagons has been removed
• individual wagons can be relocated from the Locations List
• the locations are now identified by number rather than name in the database
• the weighting system has been streamlined and improved
• the number and range of wagon types has been changed

I never used the random wagon allocation feature. It's much easier just to deploy wagons according to where they are stored and then update their locations manually, either directly in the database or through the now modified Locations List function. Deleting it recovered some memory.

The Locations List looks almost the same as previously, but now in the first column, each wagon's position in the database is listed in addition to its wagon ID and its description.

This number is now used to enable the user to move it. Typing a number (eg "19"), tells the system that wagon number 19 needs to be moved ....

Typing the location number will then move it to that location.

1. Beeston Market
2. Beeston Castle
3. Peckforton
4. Mill Siding
5. Bulkeley
6. Copper Mine
7. Bickerton

0 indicates that a wagon is out of service (eg under repair).

Database entries therefore now look slightly different. 

Locations are now simply identified by the relevant numeral - in this case "5" shows this wagon is currently located at Bulkeley. Not only does this speed up data entry, it reduces the amount of processing needed in the program.

 

The type of wagon is identified by a single letter - "m" indicates it is for "mixed" traffic meaning it could roam almost anywhere on the system.

I drew upon my experience of programming the Google Sheets version to determine the weightings for wagon types as a list of numbers. For example, any wagon designated for fruit traffic would be most likely to be sent to Beeston Market and Bulkeley, have a lesser likelihood of visiting Bickerton, Beeston Castle and Peckforton but would never visit the Mill Siding or the Copper Mine.

This produces a weightings profile of:

8,3,4,0,8,0,6

ie

• an 8/9 chance of going to Beeston Market
• a 3/9 chance of going to Beeston Castle
• a 4/9 chance of going to Peckforton
• a 0/9 chance of going to the Mill Siding
• an 8/9 chance of going to Bulkeley
• a 0/9 chance of going to the Copper Mine
• a 6/9 chance of going to Bickerton

How this is calculated is revealed in the program below.

PROC begin:
 GLOBAL k$(1)
 GLOBAL w%(15),wn$(15,2),wd$(13,30),wi$(15,4),loc%(15),dest%(15)
 LOCAL c%
 c%=1
 DO
  w%(c%)=0 :wn$(c%)="" :wd$(c%)="" wi$(c%)="" 
  loc%(c%)=0 :dest%(c%)=0
  c%=c%+1
 UNTIL c%=16
 CLS
 PRINT
 PRINT "     FREIGHT MANAGER v4"
 PRINT
 PRINT "      S = Sort"
 PRINT "      R = Reset"
 PRINT "      L = List locations"
 PRINT "      -------------------"
 PRINT
 PRINT "    What type of train?"
 PRINT "      P = Pickup Goods"
 PRINT "      M = Mixed"
 DO
 k$=GET$
 UNTIL k$="p" OR k$="m" OR k$="s" OR k$="r" OR k$="l"
 IF k$="p" OR k$="s"
  train:
 ELSEIF k$="l"
  list:
  begin:
 ELSEIF k$="s"
  sort:
  begin:
 ELSE
  reset:
  begin:
 ENDIF
ENDP

PROC train:
 LOCAL w$(7),r$(16)
 LOCAL c%,d%,e%,g%,l$(14),t%,s%,wt%,nl%,nw%
 BUSY "Please wait ...."
 l$="BMBCPKMSByCMBn" REM The short (2 char) names for locations used in the database
 c%=1
 t%=1
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$,m$
 CLS
 FIRST
 DO
  redo::
  s%=1
  w%(c%)=1+(RND*COUNT)
  IF c%>1
   DO
    IF w%(c%)=w%(s%)
     GOTO redo
    ENDIF
    s%=s%+1
   UNTIL s%=c%
  ENDIF
  c%=c%+1
 UNTIL c%=16
 c%=1
 d%=1
 DO
  POSITION w%(d%)
  IF a.l$="0"
   d%=d%+1
   UPDATE
   CONTINUE
  ENDIF
  wn$(d%)=A.n$
  wd$(d%)=A.d$
  wi$(d%)=A.i$
  loc%(d%)=VAL(A.l$)
  wt%=0
  nl%=0
  nw%=0
  e%=1
  IF k$="p"
   g%=15
  ELSE
   g%=4
  ENDIF
  DO
   IF e%<>VAL(A.l$)
    IF A.t$="t":w$="7260404"
    ELSEIF A.t$="p":w$="6060060"
    ELSEIF A.t$="m":w$="8664616"
    ELSEIF A.t$="c":w$="9653545"
    ELSEIF A.t$="b":w$="8640404"
    ELSEIF A.t$="f":w$="9040805"
    ELSEIF A.t$="g":w$="8000080"
    ELSEIF A.t$="s":w$="8007405"
    ENDIF
    wt%=INT(RND*VAL(MID$((w$),e%,1)))
    IF wt%=nw%
     wt%=INT(RND)+wt%
    ENDIF
    IF wt%>nw%
     nw%=wt%
     dest%(d%)=e%
    ENDIF
   ENDIF
   e%=e%+1
  UNTIL e%=8
  d%=d%+1
  CLS
  AT 5,8
  PRINT"     ":REPT$("***",1+g%-d%)
 UNTIL d%=g%+1
 CLS
 IF k$="p"
  PRINT"    Pickup goods" :PRINT
 ELSE
  PRINT "   Mixed train" :PRINT
 ENDIF
 s%=1
 DO
  c%=1
  DO
   IF loc%(c%)=s%
    PRINT"*",wi$(c%),wd$(c%);REPT$(".",(46-(LEN(wd$(c%)))));MID$(l$,(s%*2)-1,2),"to".MID$(l$,dest%(c%)*2)-1,2)
   ENDIF
   IF c%=4 AND k$="m"
    c%=15
   ENDIF
   c%=c%+1
  UNTIL c%=16
  s%=s%+1
 UNTIL c%=16
 BUSY OFF
 GET
 CLOSE
 dINIT "ACCEPT TRAIN?"
 dBUTTONS "No",%N,"Yes".%Y
 IF DIALOG = %n
  train:
 ENDIF
 stations:
ENDP 

PROC stations:
 LOCAL c%,f%,g%,t%,s%,s$(2),f$(15,4),t%(15,4),l$(14)
 CLS
 PRINT "     Which station?"
 PRINT
 PRINT " * 1 * Beeston Market"
 PRINT " * 2 * Beeston (C)astle"
 PRINT " * 3 *  (P)eckforton"
 PRINT " * 4 *  Mill (S)iding"
 PRINT " * 5 *  Bulkele(Y)"
 PRINT " * 6 *  Coppe(R) Mine"
 PRINT " * 7 *  Bickerto(N)"
 PRINT
 PRINT "*   Or SPACE to exit   *"
 DO
  s%=GET
 UNTIL s%=32 OR (s%>48 AND s%<56)
 IF s%=32
  dINIT "Are you sure?"
  dTEXT "","This will restart program",2dBUTTONS "No",%N,"Yes",%Y
  IF DIALOG =y%
   begin:
  ENDIF
  :stations
 ENDIF
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$
 l$="BMBCPKMSByCNBn"
 CLS
 s%=s%-48
 IF s%=1
  PRINT "Beeston Market"
 ELSEIF s%=2
  PRINT "Beeston Castle"
 ELSEIF s%=3
  PRINT "Peckforton"
 ELSEIF s%=4"
  PRINT "Mill Siding"
 ELSEIF s%=5
  PRINT "Bulkeley"
 ELSEIF s%=6
  PRINT "Copper Mine"
 ELSE
  PRINT "Bickerton"
 ENDIF
 c%=1
 DO
  f$(c%)=""
  c%=c%+1
 UNTIL c%=16
 again::
 c%=1
 f%=1
 t%=1
 PRINT "From",s$
 DO
  IF loc%(c%)=s%
   PRINT t%,wi$(c%),"-",wd$(c%),"to".MID$(l$,(dest%(c%)*2)-1,2)
   t%=t%+1
  ENDIF
  c%=c%+1
 UNTIL c%=5
 IF k$="p"
  DO
   IF loc%(c%)=s%
    PRINT t%,wi$(c%),"-",wd$(c%),"to".MID$(l$,(dest%(c%)*2)-1,2)
    t%=t%+1
   ENDIF
   c%=c%+1
  UNTIL c%=16
 ENDIF
 c%=1
 t%=1
 PRINT
 PRINT "To",s$,"(Letter=accept wagon * T=accept ALL)"
 c%=1
 DO
  IF dest%(c%)=s%
   IF f$(t%)="OK"
    wi$(c%)="OK"
   ENDIF
   PRINT CHR$(t%+96),wi$(c%),"-",wd$(c%),"from",MID$(l$(loc%(c%)*2)-1,2)
   t$(t%)=wi$(c%)
   t%=t%+1
  ENDIF
  IF k$="m" and c%=4
   c%=15
  ENDIF
  c%=c%+1
 UNTIL c%=16
 PRINT
 PRINT "Or press SPACE to move on"
 g%=GET
 IF g%=32
  CLOSE
  stations:
 ENDIF
 IF g%=116
  c%=1
  DO
   IF (t$(c%)<>"OK")
    FIRST
    FIND (t$(c%))
    A.l$=CHR$(s%+48)
    UPDATE
    f$(c%)="OK"
   ENDIF
   c%=c%+1
  UNTIL c%=t%
  CLS
  GOTO again
 ENDIF
 IF g%>113 OR g%<97
  BEEP 5,300
  CLS
  GOTO again
 ENDIF
 IF g%-96<t% AND g%>0
  IF f$(g%-96)<>"OK"
   FIRST
   FIND (t$(g%-96))
   A.l$=CHR$(s%+48)
   UPDATE
   f$(g%-96)="OK"
   CLS
   GOTO again
  ENDIF
 ENDIF
 CLOSE
 stations:
ENDP

PROC sort:
 LOCAL c%,d%,e%,e$(10,2),fin$(2)
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$
 BUSY "Sorting ...."
 c%=17
 DO
  FIRST
  IF c%<10
   fin$=NUM$(c%,1)
   fin$="0"+fin$
  ELSE
   fin$=NUM$(c%,2)
  ENDIF
  FINDFIELD (fin$,1,1,16)
  TRAP UPDATE
  e%=ERR
  IF e%=-36
   d%=d%+1
   e$(d%)=fin$
   e%=0
  ENDIF
  c%=c%+1
 UNTIL c%=COUNT+1
 IF d%>0
  DO
   FIRST
   A.n$=e$(d%)
   UPDATE
   d%=d%-1
  UNTIL d%=0
  d%=77
 ENDIF
 CLOSE
 IF d%=77
  sort:
 ENDIF
 BUSY OFF
ENDP

PROC reset:
 LOCAL c%
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$
 BUSY "Resetting ....."
 c%=1
 DO
  FIRST
  IF A.l$<>"0"
   A.l$="1"
   UPDATE
  ELSE
   UPDATE
  ENDIF
  c%=c%+1
 UNTIL c%=c%+1
 CLOSE
 BUSY OFF
ENDP

PROC list:
 LOCAL g%,c%,l$(1)
 GLOBAL k%
 CLS
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$
 c%=1
 DO
  IF c%=1
   PRINT "1 - Beeston Market"
   l$="1"
  ELSEIF c%=2
   PRINT "2 - Beeston Castle"
   l$="2"
  ELSEIF c%=3
   PRINT "3 - Peckforton"
   l$="3"
  ELSEIF c%=4
   PRINT "4 - Mill Siding"
   l$="4"
  ELSEIF c%=5
   PRINT "5 - Bulkeley"
   l$="5"
  ELSEIF c%=6
   PRINT "6 - Copper Mine"
   l$="6"
  ELSEIF c%=7
   PRINT "7 - Bickerton"
   l$="7"
   ELSEIF c%=0
   PRINT "0 - Out of Service"
   l$="0"
  ENDIF
  g%=1
  FIRST
  DO
   FINDFIELD (l$,5,1,1)
   PRINT A.n$,"-",A$.i$,A.d$
   g%=g%+1
   IF g%=15 OR g%=30 OR g%=45
    BUSY "More ...."
    k%=GET
    IF k%>47 AND k%<58
     movewag:
    ENDIF
    BUSY OFF
   ENDIF
   NEXT
  UNTIL EOF
  PRINT "-----------------------"
  k%=GET
  IF k%>47 AND k%<58
   movewag:
  ENDIF
  c%=c%+1
 UNTIL c%=9
 CLOSE
 begin:
ENDP

PROC movewag:
  LOCAL m$(10),f$(2),t$(1)
  BEEP 5,300
  f$=CHR$(k%)
  m$="Move "+f$
  BUSY m$
  k%=GET
  f$=f$+CHR$(k%)
  m$=m$+f$+" to"
  BUSY m$
  k%=GET
  t$=CHR$(k%)
  FINDFIELD (f$,1,1,16)
  A.l$=t$
  UPDATE
  CLOSE
  BUSY OFF
  list:
ENDP

 So, how does it work?

 

The BEGIN procedure

PROC begin:
 GLOBAL k$(1)
 GLOBAL w%(15),wn$(15,2),wd$(13,30),wi$(15,4),loc%(15),dest%(15)
 LOCAL c%
 c%=1
 DO
  w%(c%)=0 :wn$(c%)="" :wd$(c%)="" wi$(c%)="" 
  loc%(c%)=0 :dest%(c%)=0
  c%=c%+1
 UNTIL c%=16
 CLS
 PRINT
 PRINT "     FREIGHT MANAGER v4"
 PRINT
 PRINT "      S = Sort"
 PRINT "      R = Reset"
 PRINT "      L = List locations"
 PRINT "      -------------------"
 PRINT
 PRINT "    What type of train?"
 PRINT "      P = Pickup Goods"
 PRINT "      M = Mixed"
 DO
 k$=GET$
 UNTIL k$="p" OR k$="m" OR k$="s" OR k$="r" OR k$="l"
 IF k$="p" OR k$="s"
  train:
 ELSEIF k$="l"
  list:
  begin:
 ELSEIF k$="s"
  sort:
  begin:
 ELSE
  reset:
  begin:
 ENDIF
ENDP

 This initially defines a series of global variables so that data about wagons can be transferred from one procedure to another

It then presents the user with the first menu screen asking which action they want to take

It then stores the key press in the global variable k$ and calls the relevant procedure

 

 The TRAIN procedure

 This procedure firstly picks a wagon from the database at random and then calculates its destination using the weightings shown above.

PROC train:
 LOCAL w$(7),r$(16)
 LOCAL c%,d%,e%,g%,l$(14),t%,s%,wt%,nl%,nw%
 BUSY "Please wait ...."
 l$="BMBCPKMSByCMBn" REM The short (2 char) names for locations used in the database
 c%=1
 t%=1
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$,m$
 CLS
 FIRST
 DO
  redo::
  s%=1
  w%(c%)=1+(RND*COUNT)
  IF c%>1
   DO
    IF w%(c%)=w%(s%)
     GOTO redo
    ENDIF
    s%=s%+1
   UNTIL s%=c%
  ENDIF
  c%=c%+1
 UNTIL c%=16
 c%=1
 d%=1
 DO
  POSITION w%(d%)
  IF a.l$="0"
   d%=d%+1
   UPDATE
   CONTINUE
  ENDIF
  wn$(d%)=A.n$
  wd$(d%)=A.d$
  wi$(d%)=A.i$
  loc%(d%)=VAL(A.l$)
  wt%=0
  nl%=0
  nw%=0
  e%=1
  IF k$="p"
   g%=15
  ELSE
   g%=4
  ENDIF
  DO
   IF e%<>VAL(A.l$)
    IF A.t$="t":w$="7260404"
    ELSEIF A.t$="p":w$="6060060"
    ELSEIF A.t$="m":w$="8664616"
    ELSEIF A.t$="c":w$="9653545"
    ELSEIF A.t$="b":w$="8640404"
    ELSEIF A.t$="f":w$="9040805"
    ELSEIF A.t$="g":w$="8000080"
    ELSEIF A.t$="s":w$="8007405"
    ENDIF
    wt%=INT(RND*VAL(MID$((w$),e%,1)))
    IF wt%=nw%
     wt%=INT(RND)+wt%
    ENDIF
    IF wt%>nw%
     nw%=wt%
     dest%(d%)=e%
    ENDIF
   ENDIF
   e%=e%+1
  UNTIL e%=8
  d%=d%+1
  CLS
  AT 5,8
  PRINT"     ":REPT$("***",1+g%-d%)
 UNTIL d%=g%+1
 CLS
 IF k$="p"
  PRINT"    Pickup goods" :PRINT
 ELSE
  PRINT "   Mixed train" :PRINT
 ENDIF
 s%=1
 DO
  c%=1
  DO
   IF loc%(c%)=s%
    PRINT"*",wi$(c%),wd$(c%);REPT$(".",(46-(LEN(wd$(c%)))));MID$(l$,(s%*2)-1,2),"to".MID$(l$,dest%(c%)*2)-1,2)
   ENDIF
   IF c%=4 AND k$="m"
    c%=15
   ENDIF
   c%=c%+1
  UNTIL c%=16
  s%=s%+1
 UNTIL c%=16
 BUSY OFF
 GET
 CLOSE
 dINIT "ACCEPT TRAIN?"
 dBUTTONS "No",%N,"Yes".%Y
 IF DIALOG = %n
  train:
 ENDIF
 stations:
ENDP 

After defining the LOCAL variables used in the procedure, it then populates variable l$ with the short (two character) names for each location (ie BM = Beeston Market, BC = Beeston Castle, PK = Peckforton, and so on).

It then OPENs the wagons database. Note, to save memory, I have stored the wagons database on a Flash RAM card in slot A which is why the name is preceded with A:  - each field is then allocated a variable name (ie n$ for number, d$ for description, i$ for ID, etc)

The next DO loop picks 15 wagons at random. The IF loop checks whether the wagon is already in the list and, if so, picks another. NOTE: The more wagons there are in the database, the less likely there will be repeats and hence the quicker the selection process will be.

The next DO loop retrieves the data for each of the selected wagons from the database and stores it in the global variables and then uses the weightings to calculate the intended location for each wagon on the list.

The final main DO loop then presents the user with the list of wagons with intended destinations and asks if the user wants to accept the train or generate another.

 

 The STATIONS procedure

 This procedure asks users to select a location and then presents them with the train movements at that location. Wagons can then be moved to that station with a key press.

PROC stations:
 LOCAL c%,f%,g%,t%,s%,s$(2),f$(15,4),t%(15,4),l$(14)
 CLS
 PRINT "     Which station?"
 PRINT
 PRINT " * 1 * Beeston Market"
 PRINT " * 2 * Beeston Castle"
 PRINT " * 3 * Peckforton"
 PRINT " * 4 * Mill Siding"
 PRINT " * 5 * Bulkeley"
 PRINT " * 6 * Copper Mine"
 PRINT " * 7 * Bickerton"
 PRINT
 PRINT "*   Or SPACE to exit   *"
 DO
  s%=GET
 UNTIL s%=32 OR (s%>48 AND s%<56)
 IF s%=32
  dINIT "Are you sure?"
  dTEXT "","This will restart program",2dBUTTONS "No",%N,"Yes",%Y
  IF DIALOG =y%
   begin:
  ENDIF
  :stations
 ENDIF
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$
 l$="BMBCPKMSByCNBn"
 CLS
 s%=s%-48
 IF s%=1
  PRINT "Beeston Market"
 ELSEIF s%=2
  PRINT "Beeston Castle"
 ELSEIF s%=3
  PRINT "Peckforton"
 ELSEIF s%=4"
  PRINT "Mill Siding"
 ELSEIF s%=5
  PRINT "Bulkeley"
 ELSEIF s%=6
  PRINT "Copper Mine"
 ELSE
  PRINT "Bickerton"
 ENDIF
 c%=1
 DO
  f$(c%)=""
  c%=c%+1
 UNTIL c%=16
 again::
 c%=1
 f%=1
 t%=1
 PRINT "From",s$
 DO
  IF loc%(c%)=s%
   PRINT t%,wi$(c%),"-",wd$(c%),"to".MID$(l$,(dest%(c%)*2)-1,2)
   t%=t%+1
  ENDIF
  c%=c%+1
 UNTIL c%=5
 IF k$="p"
  DO
   IF loc%(c%)=s%
    PRINT t%,wi$(c%),"-",wd$(c%),"to".MID$(l$,(dest%(c%)*2)-1,2)
    t%=t%+1
   ENDIF
   c%=c%+1
  UNTIL c%=16
 ENDIF
 c%=1
 t%=1
 PRINT
 PRINT "To",s$,"(Letter=accept wagon * T=accept ALL)"
 c%=1
 DO
  IF dest%(c%)=s%
   IF f$(t%)="OK"
    wi$(c%)="OK"
   ENDIF
   PRINT CHR$(t%+96),wi$(c%),"-",wd$(c%),"from",MID$(l$(loc%(c%)*2)-1,2)
   t$(t%)=wi$(c%)
   t%=t%+1
  ENDIF
  IF k$="m" and c%=4
   c%=15
  ENDIF
  c%=c%+1
 UNTIL c%=16
 PRINT
 PRINT "Or press SPACE to move on"
 g%=GET
 IF g%=32
  CLOSE
  stations:
 ENDIF
 IF g%=116
  c%=1
  DO
   IF (t$(c%)<>"OK")
    FIRST
    FIND (t$(c%))
    A.l$=CHR$(s%+48)
    UPDATE
    f$(c%)="OK"
   ENDIF
   c%=c%+1
  UNTIL c%=t%
  CLS
  GOTO again
 ENDIF
 IF g%>113 OR g%<97
  BEEP 5,300
  CLS
  GOTO again
 ENDIF
 IF g%-96<t% AND g%>0
  IF f$(g%-96)<>"OK"
   FIRST
   FIND (t$(g%-96))
   A.l$=CHR$(s%+48)
   UPDATE
   f$(g%-96)="OK"
   CLS
   GOTO again
  ENDIF
 ENDIF
 CLOSE
 stations:
ENDP

The first part of the procedure presents users with the list of locations and invites them to select one by typing the relevant number

The next section then presents the relevant location and shows the wagons which either need to be moved FROM that location or need to be sent TO it.

Beside each wagon needing to be sent there is a letter enabling it to be identified on the keyboard.

Pressing the relevant letter will change the wagon ID (eg T4 or TK1) or OK, indicating that the system has moved it to the new location.

 

 Pressing the T key will move all the wagons to that location in the database.

 

The SORT procedure

This simply sorts the wagons into numerical order in the database. Each time a wagon is UPDATED (ie moved to a new location) by the program, its record is moved to the end of the database and so loses its position. This makes life easier when viewing the wagons database. It's interesting that the database app in the Psion doesn't include a menu option for sorting the records.

NOTE: It assumes that the wagon number field contains sequential two digit numbers, starting with 01, 02, ..... and so on.

The TRAP UPDATE command prevents the procedure from stopping and creating an error message if it doesn't find one of the numbers. For example, if the numbering of the wagons isn't sequential or if there's a repeat of the same number.

This error is then detected (using ERR) and the offending wagon numbers are stored in the e$ array until the end of the procedure. Any wagon entries which have not been sorted will remain at the start of the database file and so these are each allocated one of the missing numbers from the list stored in the e$ array.

PROC sort:
 LOCAL c%,d%,e%,e$(10,2),fin$(2)
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$
 BUSY "Sorting ...."
 c%=17
 DO
  FIRST
  IF c%<10
   fin$=NUM$(c%,1)
   fin$="0"+fin$
  ELSE
   fin$=NUM$(c%,2)
  ENDIF
  FINDFIELD (fin$,1,1,16)
  TRAP UPDATE
  e%=ERR
  IF e%=-36
   d%=d%+1
   e$(d%)=fin$
   e%=0
  ENDIF
  c%=c%+1
 UNTIL c%=COUNT+1
 IF d%>0
  DO
   FIRST
   A.n$=e$(d%)
   UPDATE
   d%=d%-1
  UNTIL d%=0
  d%=77
  ENDIF
 CLOSE
 IF d%=77
  sort:
 ENDIF
 BUSY OFF
ENDP

The RESET procedure

This procedure simply sends all the wagons back to location number 1 (in my case Beeston Market). It ignores wagons currently Out of Service (ie Location 0)

PROC reset:
 LOCAL c%
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$
 BUSY "Resetting ....."
 c%=1
 DO
  FIRST
  IF A.l$<>"0"
   A.l$="1"
   UPDATE
  ELSE
   UPDATE
  ENDIF
  c%=c%+1
 UNTIL c%=c%+1
 CLOSE
 BUSY OFF
ENDP

The LIST procedure

This lists the wagons at each location and allows individual wagons to be moved from one location to another. I find this useful at the start of a session to make sure wagons are where they are supposed to be.

PROC list:
 LOCAL g%,c%,l$(1)
 GLOBAL k%
 CLS
 OPEN "A:\dat\Wagons2.dbf",A,n$,d$,i$,t$,l$
 c%=1
 DO
  IF c%=1
   PRINT "1 - Beeston Market"
   l$="1"
  ELSEIF c%=2
   PRINT "2 - Beeston Castle"
   l$="2"
  ELSEIF c%=3
   PRINT "3 - Peckforton"
   l$="3"
  ELSEIF c%=4
   PRINT "4 - Mill Siding"
   l$="4"
  ELSEIF c%=5
   PRINT "5 - Bulkeley"
   l$="5"
  ELSEIF c%=6
   PRINT "6 - Copper Mine"
   l$="6"
  ELSEIF c%=7
   PRINT "7 - Bickerton"
   l$="7"
   ELSEIF c%=0
   PRINT "0 - Out of Service"
   l$="0"
  ENDIF
  g%=1
  FIRST
  DO
   FINDFIELD (l$,5,1,1)
   PRINT A.n$,"-",A$.i$,A.d$
   g%=g%+1
   IF g%=15 OR g%=30 OR g%=45
    BUSY "More ...."
    k%=GET
    IF k%>47 AND k%<58
     movewag:
    ENDIF
    BUSY OFF
   ENDIF
   NEXT
  UNTIL EOF
  PRINT "-----------------------"
  k%=GET
  IF k%>47 AND k%<58
   movewag:
  ENDIF
  c%=c%+1
 UNTIL c%=9
 CLOSE
 begin:
ENDP

The main DO loop works its way progressively through the locations, finding wagons in the database for each location then moving on to the next.

 

IF the number of wagons at any one location is more than 15 (ie a screen-full), then it pauses and waits for the user to press a key (using the GET statement).

IF the user presses a number key, then it calls the MOVEWAG procedure (see below) to move that particular wagon to a new location.

 Similarly, when all the wagons at a particular location have been presented, the program pauses to await a key press.

If the key press is the space bar (ie character code 32), then the program continues.

The MOVEWAG procedure

As indicated above, if a number key is pressed in the LIST procedure, this procedure asks for another number (all wagons have two digit numbers) and then asks for a location (ie between 1 and 7).

It then changes the location of the wagon in the database.

PROC movewag:
  LOCAL m$(10),f$(2),t$(1)
  BEEP 5,300
  f$=CHR$(k%)
  m$="Move "+f$
  BUSY m$
  k%=GET
  f$=f$+CHR$(k%)
  m$=m$+f$+" to"
  BUSY m$
  k%=GET
  t$=CHR$(k%)
  FINDFIELD (f$,1,1,16)
  A.l$=t$
  UPDATE
  CLOSE
  BUSY OFF
  list:
ENDP

Conclusion

Nothing in this world is perfect and this version of my freight manager program is a compromise between the ideal and what is achievable on the Psion with its limited 1Mb of working memory. However, of all the different versions, this best suits my needs. I have used it now for several operating sessions and find it very helpful in identifying the wagons which need to be moved.

Unlike the versions which are laptop based and rely on print outs if, for some reason such as the weather deteriorating, I have to abandon a running session before all the wagons in a train have been moved to their intended location, this version allows for that eventuality.

I also find it helpful to see which wagons have already been moved and which are still on the list. Occasionally, it's easy to confuse one wagon with another - one open wagon full of coal looks very much like another.

It seems to me a great pity that nothing seems to have replaced the Psion. It was (and still is) a great little computer. If the resale prices on eBay are any indicator, then there is still a market out there for a decent little computer with a proper keyboard which fits into your pocket!

How I created a version of my freight management program with Google Sheets

 

Contents

•  Introduction
• Overview of the system
• The Train Sheet
• The Locations Sheet
• The Weightings Sheet
• The Wagons Sheet
• Setting-up the spreadsheet
  
• The Locations Sheet
• The Weightings Sheet
• The Wagons Sheet
• The Train Sheet 
• Optional - creating buttons
  
• The Script
• Conclusion

 

 Introduction

You may have seen that, over the years, I have written three versions of my computerised freight management system. Initially, I used a relational database called 4D or 4th Dimension - because it came as a freebie with a magazine. I then used another free programming environment called LiveCode, because the version of 4D I was using became outdated and it was going to cost me around £600 to buy a version which would run on Windows 7. I then created another version which ran on my old resurrected Psion Pocket computer as it meant I could interact with the program out in the garden, rather than having to consult print-outs. However, my Psion was of an age where it had very limited storage capacity and so tended to run out of system memory at critical moments. 

I tried exporting an Android version of my LiveCode program to enable it to run on my phone or tablet computer but, despite a fair amount of effort and several abortive attempts, I couldn't make it run and so, I sought an alternative. It was then, I realised that underlying Google Sheets was a computer scripting language which I could use. All I had to do was teach myself how to write programs using JavaScript.

Mine is probably not the most elegant of scripts, but it does seem to work and has three advantages:

1. It's free
2. It will run on my laptop, my tablet and my phone
3. Changes made on any of those devices are synchronised with the others

At the end of the day, Google Sheets is a spreadsheet and so has some limitations. It's not easy to design an attractive user interface and also, for reasons best known to themselves, on-screen buttons won't work on the mobile app - which is a bit frustrating as large buttons are far more suited to small phone screens than trying to select from a small drop-down box in a cell. However, it is free and remarkably powerful, so who's complaining?

 

An overview of the system

The Train Worksheet

 The main screen (Worksheet) is the one which generates the trains.

 

The first task is to choose the length of train you want the system to generate. I have restricted the length of my trains from 4 wagons to 18. If you made your own version, you could set this range to whatever suits your railway.

Next, we select the 'createTrain' option from the drop-down box beside it.

 

 

 A train of the given length is then generated showing each wagon's name, a brief description, its present location and the intended destination. If I don't fancy this particular train, I can generate another and so on until one turns up which is more appealing.

The train is then marshalled and sent around the railway. Once a wagon has been shunted into its new location, I can then choose 'Move_wagon' from the drop-down box beside it to tell the system it has been transferred to its new location.

Alternatively, I could wait until I have shunted all the wagons to a particular location and select 'Move_these' from the list to transfer them all to that new location.

The 'Present location' field changes to reflect their new locations on the railway and, as a further reminder, a prompt shows up indicating which wagons have so far been 'Done'.

 Or, I could wait until I have transferred every wagon in the train to their new locations and select 'Move_all' to inform the system I have done so.

 

The locations worksheet

The Locations Worksheet is accessed by clicking on the locations tab at the bottom of the window

 

As you can see, there isn't much to this sheet. The locations on the railway are listed in the first column. Notice that they are not just stations, also included are locations such as the Mill Siding and the Copper Mine as they are locations which will receive and despatch freight on to the system.

I included a column showing short names for each location as I expected them to be used in the programming. In actuality they weren't needed but I have kept them in case they might be needed in future extensions of the program.

 

The weightings worksheet

At the heart of the system lies the weighting figures for each type of wagon. These are used to determine the upper limit of the random number calculation when figuring out which location each wagon could move to. The higher the weighting, the more likely it is a wagon will be sent there. However, as the calculation involves a random element, there is still a possibility that a wagon could be sent to any location unless the weighting figure is set to zero.

To get to the Weightings Worksheet, the 'weights' tab at the foot of the screen is clicked.

 The categories of wagon types can now be entered in the first column. These are used to determine the likelihood of a particular type of wagon being sent to a particular location. There is no limit to the number of 'Type' categories which can be created but for convenience I have assumed there will be no more than 15 in my version of the spreadsheet.

The weightings are then selected for each wagon type and each location using a drop-down list. 0 means there is no chance a wagon of that type will be sent to that location (eg no brewery traffic will go to the Copper Mine). A likelihood of 9 means there is a high probability that that type of wagon will be sent to that location. The weightings can be altered at any time, so if I feel that a particular location seems to be receiving very little traffic, I can adjust the weightings for that location.

 

The Wagons Worksheet

The Wagons Worksheet is accessed by clicking the 'wagons' tab at the foot of the page.

The sheet holds details of every wagon on the system. A unique identifier is used in the first column, followed by the wagon's name and then a brief description to help with its identification. The next column indicates which type of wagon it is and the last column shows where it is (or should be) currently located.

 

 

Data in the first three columns are entered free-hand, but the wagon type category is selected from a drop-down list, drawn from the data on the weightings worksheet.

 

Similarly, the wagon's present location is entered using a drop-down list drawn from the locations worksheet. It's important, therefore that the data is entered firstly on the locations sheet, then on the weightings sheet and finally on the wagons sheet.

There is no limit to the number of wagons which can be entered into the system, but I have assumed I will not have any more than 100 wagons on my railway.

 

 

 Setting up the spreadsheet

I am assuming that you are reasonably familiar with using spreadsheets, but you may not have designed one using all the features which are needed (eg drop-down lists). I created the spreadsheet on my laptop as I find it easier than working on my tablet and more particularly on my phone - my fingers are too fat!

 

The first task is to create a new spreadsheet. This is accomplished by clicking on the multi-coloured plus sign in the bottom right of the screen.

 The spreadsheet then needs to be given a name by clicking on the name field in the top left corner of the screen.

I started with the Locations worksheet. Not only is this the easiest to create but its data needs to be used in all the other worksheets.

The Locations Worksheet

Firstly, I renamed the worksheet from its default of 'Sheet1', by clicking on the arrow beside the name of the worksheet and then clicking on 'Rename' from the pop-up menu.

 

Two headings were then typed into cells A1 and B1 - Locations and Short Name. It has turned out that I haven't used the short name in this version of the program, but it might be useful for future extensions of the program.

 

The headings were enlarged to 12 point and emboldened, using the relevant buttons in the text tools palette, ......

...... and then some data was entered for test purposes. I started the name of each location with a number as it's useful for sorting the locations into the order in which they appear on my railway. 

 

Note: I started with zero for Out of Service which ensures that any out of service stock will be ignored when calculating intended destinations.

The Locations Sheet has now been set up. Next, we need to set up the Weightings Sheet as this contains the data for the wagon category types which are needed on the Wagons Sheet.

 

The Weightings Worksheet

To add a new worksheet, we click on the + sign in the bottom left corner of the screen. As you can see, this has created a worksheet called Sheet2.

 After renaming the worksheet to 'weights',.......

....... we now enter the heading 'Type' into cell A1.

 

NOTE It is important that you type in the names for the sheets exactly as shown (eg lack of capital letters) as these names are used by the script

 

The heading was then centred, emboldened and increased in size to 12 point using the buttons in the text palette above the worksheet.

 

This formula is then typed into cell B1

 

=ARRAYFORMULA(TRANSPOSE(locations!A3:A15))

 

It takes the contents of cells A3 to A15 on the locations worksheet, transposes them (ie turns them from a vertical column into a horizontal row) and pastes the contents as an array from cell B1 onwards.

 

NOTE: If you have more than 13 locations, then increase the final cell address accordingly.

 

After entering the first category of wagon type into cell A2, we now need to create a drop-down list for the weightings in cell B2.

 

Click in cell B2 to select it, then select Data validation from the Data menu.

 

We now change the Criteria to List of items and enter the weightings 

 

0,1,2,3,4,5,6,7,8,9

 

into the field beside it.

 

We put a tick beside  Show validation help text and enter the prompt

 

Choose a weighting from 0 (low) to 9 (high)

 

into the field below it.

 

 

After clicking Save, we now need to copy the settings of this cell into the other cells we will be using.

 

Click on the small blue square in the bottom right corner of the cell and with the mouse button held down, .......

 

........ drag it down as far as is necessary for the number of category types you will will be creating(plus a few more for future expansion) - in my case I dragged it down to cell B15. 

 

We now select those cells again and click the blue square in the bottom right corner ........

 

...... and drag it across to the right as far as we expect the number of locations on our system to be (plus a few more for future expansion). In my case, I dragged it to column K.

 

We can now enter more data on to the sheet, using the drop-down lists to select the required weightings.

Note: The drop-down list is accessed by clicking on the downward facing arrow in the corner of the cell

 

Now we have completed the Weightings Worksheet, we need to create the all-important Wagons Worksheet

 

 The Wagons Worksheet

 We add a new worksheet, rename it to wagons and then enter the headings as shown below.

 

If you need to widen any of the columns as I did to fit in the header for Present Location, then drag the column divider above the column to make it wider.
 

We now need to create a drop-down list of wagon types, using the data from the Weightings Worksheet.

 

After selecting cell, D2, we choose Data validation from the Data menu and then make sure that List from a range is showing the the Criteria field

 

We now click on the Select data range button on the left of the field which will contain the list of options.

 

A new pop-up message will appear asking us to Select a data range.

 

We now go to the Weightings Worksheet to select the cells from which the data will be drawn. Click on the weightings tab and then drag down from cell A2 to the bottom of the range you want to use - in my case from cell A2 to cell A15.

This will enter the selected range into the field on the pop-up box. Alternatively, you can type in the range - but make sure you get the name of the tab for the weightings worksheet absolutely correct, followed by an exclamation mark and then the range of cells - ie

weights!A2:A15

I then clicked on the 'Show validation help text' checkbox followed by Save.

 Returning to the Wagons Worksheet, the drop-down list should now appear when clicking on the down arrow in cell D2.

We now need to copy this format from cell D2 to the cells below it. As previously, we drag the small blue square in the bottom right corner of the cell down the column as far as is necessary - in my case it was to cell D15

We now need to populate the Present location column with drop-down lists. As before, we click on cell E2 and then select Data validation from the Data menu

 We then select the required data range (by clicking the small button at the end of the data range field), navigating to the Locations Worksheet (by clicking its tab) and then highlighting the cells containing the list of location names - in my case, cells A2 to A4.

 

 I then clicked on the Show validation help text checkbox and wrote an appropriate message in the help text field below it. I then clicked Save.

 

Returning to the Wagons Worksheet, I then checked the drop down list was working properly and copied its format from cell E2 down the column to cell E15 (by dragging the small blue square downwards in the bottom right corner of cell E2).

 

I then entered some sample wagon data on to the sheet for later testing purposes.

 

 

Incidentally, I then spent a little while creating a validation rule for column A to ensure that anything entered followed the format of a single upper case alphabetical character followed by a number. But this isn't really necessary as it turned out I didn't need to use these data in the script.

The formula needed to check the data turned out to be:

=REGEXMATCH(A3,"^[A-Z][0-9]?[0-9]$")

 

You might want to tinker with this if you decide to restrict the type of data which is entered into this column.

 

The Train Worksheet

 Another new worksheet was created, renamed 'train' and given the headers shown below.

 

 In cell F1, I decided to put a drop-down list to determine the length of train which the system will eventually generate. So, cell F1 was selected and then Data validation selected from the Data menu.

A 'List of items' ....

 

4,5,6,7,8,9,10,11,12,13,14,15

.... was typed into the validation field, and the 'Show validation help text' checkbox ticked. The Save button was then clicked.

.

In cell F2, another list of items was entered into the Data validation field .....

 

Move_wagon,Move_these,Move_all

 

 ..... and the validation help text changed and its checkbox ticked.

NOTE: If you are going to use my script, then it is essential the list is typed in exactly as show - including the capitalisation and underscore character as these are the names of the functions which will be called

 

This format was then copied down the column (by dragging the small blue square), in my case to cell F16 (as my maximum train length is 15 wagons).

In cell G1, I created a drop-down validation list comprising two items.....

 

createTrain,resetAll

This will enable us to trigger the main train creation function or to relocate all the wagons to location number 1 (or wherever your main station is located).

I'm assuming that, by now, you know how to create this drop-down validation list.

Optional buttons

The main functions are triggered by using drop-down lists as on-screen buttons don't work in the mobile app versions of Google Sheets. A great pity as large on-screen buttons are much easier to tap on a small phone screen than tiny drop-down arrows in the corner of a cell.

The buttons do work on a PC, and so I include how to create them, just in case you would prefer the option of using them on your laptop.

To create a button, we first of all need to draw it, using Google Sheet's drawing tools. From the Insert menu, select Drawing

To keep things simple, our button will simply be a text box. Click the Text box button in the drawing tools palette and then, on the drawing screen, drag out a suitably sized box. Click inside the box and type in some appropriate text.

The text can be formatted, using various tools accessed by clicking the three dots at the end of the Tool palette. I increased its size to 14 point, emboldened it and centred it in the text box.

 

 

 

I then filled the text box with a warm gold colour, using the Fill tool.

I then clicked the Save and Close button and was able to drag the text box into the position I wanted it to appear on the screen.

 

To turn it into a button, we need to create an action for it. Click on the three vertical dots in the top right corner of the text box. From the pop-up menu, select, Assign script.

We must now type in the name of the script function which the button will call. As previously, it must be typed in exactly as shown (or the name of the function changed in the script to match what is assigned to the button). Click OK when you are finished

If you click on the button or try to move or resize it now, you will be presented with the message message shown below - because, of course, we haven't yet written the script. 

To move or resize the button, firstly right-click on it. It can now be moved or resized as normal.

 The script

Underlying the operation of the Freight management system is a script, written in JavaScript. If you're anything like me, you will probably want to try out out - and then figure out how it works later. 

 

To gain access to the Script Editor, select <>Script Editor from the Tools menu

 

Then, rename the script by clicking on the title field at the top of the Script Editor window. 

You will notice that there is the beginnings of a script in the editing pane. You can delete this if you want to keep things neat, but as this function won't be called, it can sit there doing nothing and the script shown below can be copied and pasted below it.

 I have included comments within the script to explain what each section does. Many of the JavaScript statements are fairly self explanatory, but if you do decide to make changes then it is very important to exactly follow the format of the statements. Even a stray capital letter or a mistaking a comma for a semi-colon can wreak havoc! You have been warned!

 

// These two functions generate a sequence of 'n' random numbers between 'frm' and 'to'
function getRandomNumber(min, max) {
  return Math.random() * (max - min) + min;
}
function getNRandomNumbers(frm, to, n){
  var listNumbers = [];
  var nRandomNumbers = [];
  for(var i = frm; i <= to; i++) {
    listNumbers.push(i);
  }
  // This part of the function ensures none of the numbers is repeated
  for(var i = 0; i < n; i++) {
    var index = getRandomNumber(0, listNumbers.length);
    nRandomNumbers.push([listNumbers[parseInt(index)]]);
    listNumbers.splice(index, 1);
  }
  return nRandomNumbers;
} 
 function wagonRand(noOSWags) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var wagSheet = ss.getSheetByName('wagons');
  var trainSheet = ss.getSheetByName('train');
  var noWagons = wagSheet.getLastRow();
  var trainLen = trainSheet.getRange("F1").getValue();
  trainSheet.getRange(2,1,20,1).clearContent();
  var nRandomNumbers = getNRandomNumbers(2+noOSWags,noWagons,trainLen);
  trainSheet.getRange(2,1,trainLen,1).setValues(nRandomNumbers);
}

// This function responds whenever a cell is edited. It is used to check when actions are selected
// from the drop-down lists on the Train Worksheet.
// This is necessary as the Google Sheets app doesn't allow scripts to be triggered using buttons
function onEdit(evt) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var wagSheet = ss.getSheetByName('wagons');
  var trainSheet = ss.getSheetByName('train');
  // Checks what has been selected from cell G1 on the Train Worksheet and then triggers the
  // relevant function - ie either createTrain or resetAll
  if(evt.range.getA1Notation() == 'G1') {
    if(/^\w+$/.test(evt.value)) {
      this[evt.value]();
      evt.range.clear();
    }
  }
    var col = evt.range.getColumn();
    var row = evt.range.getRow();
    var trainLen = trainSheet.getRange(1,6).getValue();
    var adjacentCell = trainSheet.getRange(row, col + 1);

// This moves one wagon from its old location to its new one when this option is
// selected from the drop-down list beside the wagon in the Train Worksheet
  if (evt.value == "Move_wagon"){
    var newDest = trainSheet.getRange(row,col-1).getValue();
    trainSheet.getRange(row,4).setValue(newDest);
    var wagRow = trainSheet.getRange(row,1).getValue();
    wagSheet.getRange(wagRow,5).setValue(newDest);
    adjacentCell.setValue ("Done");
    evt.range.clear();
  }
// This moves all the wagons from their old location to the intended location
// on the Train Worksheet
  if (evt.value == "Move_all"){
    evt.range.clear();
    for (var i=2;i<=trainLen + 1;i++){
     var newDest = trainSheet.getRange(i,col-1).getValue();
     trainSheet.getRange(i,4).setValue(newDest);
     var wagRow = trainSheet.getRange(i,1).getValue();
     wagSheet.getRange(wagRow,5).setValue(newDest);
     trainSheet.getRange(i, col + 1).setValue("Done");
    }
  }
 // This moves the wagons with the same intended location from old to new
  if (evt.value == "Move_these"){
    evt.range.clear();
    var newDest = trainSheet.getRange(row,col-1).getValue();
    for (var i=2;i<=trainLen+1;i++){
     if (trainSheet.getRange(i,col-1).getValue() == newDest){
        trainSheet.getRange(i,4).setValue(newDest);
       var wagRow = trainSheet.getRange(i,1).getValue();
       wagSheet.getRange(wagRow,5).setValue(newDest);
        trainSheet.getRange(i, col + 1).setValue("Done");
     }
    }
  }
}

// This is the master function which calls the other functions when CreateTrain is chosen
function createTrain() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var wagSheet = ss.getSheetByName('wagons');
  var Avals = wagSheet.getRange("A1:A").getValues();
  var noWagons = Avals.filter(String).length;
  var range = wagSheet.getRange("A2:E"+noWagons);
  var noOSWagons = 0;
  range.sort([5]);
  for (var i = 2; i<=noWagons;i++) {
    var wagTyp = wagSheet.getRange('E'+i).getValue();
    if (wagTyp.charAt(0) == "0"){
      noOSWagons++;
    }
  }
 wagonRand(noOSWagons);
 fetchWagons();
 semiRandDestination();
}

//This function sends all the wagons back to the first station in the Locations Worksheet
function resetAll(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var wagSheet = ss.getSheetByName('wagons');
  var locSheet = ss.getSheetByName('locations');
  var Avals = wagSheet.getRange("A1:A").getValues();
  var noWagons = Avals.filter(String).length;
  // Change the co-ordinates in the getRange(3,1) statement to select a different location
  // to which the wagons are reset - ie getRange(row,column)
  var homeDest = locSheet.getRange(3,1).getValue();
  for (var i=2;i<=noWagons;i++){
    wagSheet.getRange(i,5).setValue(homeDest);
  }
}

// This function clears the previous train from the Train Worksheet
// and then copies the data for the wagons needed in the new train from the
// Wagons Worksheet to the Train Worksheet
function fetchWagons() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var wagSheet = ss.getSheetByName('wagons');
  var trainSheet = ss.getSheetByName('train');
  var trainLen = trainSheet.getRange("F1").getValue() + 1;
trainSheet.getRange("B2:E20").clear();
trainSheet.getRange("G2:G20").clear({contentsOnly: true});
for(var J=2;J<=trainLen;J++) {
var wagNo = trainSheet.getRange('A'+J).getValue();
var range1 = wagSheet.getRange('B'+wagNo+':C'+wagNo);
var range2 = wagSheet.getRange('E'+wagNo);
range1.copyTo(trainSheet.getRange('B'+J+':C'+J));
range2.copyValuesToRange(trainSheet,4,4,J,J);
}
}

//This function as the name suggests, calculates the new destination for each wagon
// in the train using the weightings from the Weightings Worksheet
function semiRandDestination() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var wagSheet = ss.getSheetByName('wagons');
  var trainSheet = ss.getSheetByName('train');
  var wtSheet = ss.getSheetByName('weights');
  var locSheet = ss. getSheetByName ('locations');
  var Avals = locSheet.getRange("A1:A").getValues();
  var noLocs = Avals.filter(String).length;
  var trainLen = trainSheet.getRange("F1").getValue() + 1;
  var Avals = wtSheet.getRange("A1:A").getValues();
  var noTypes = Avals.filter(String).length;
  for (var i=2;i<=trainLen;i++){
    var wagRow = trainSheet.getRange('A'+i).getValue();
    var wagType = wagSheet.getRange('D'+ wagRow).getValue();  
    var hiWt = 0;
    var newDest = "";
      for(var j=2;j<=noTypes;j++){
        if(wagType == wtSheet.getRange('A'+ j).getValue()){
          for(var k=2;k<=noLocs;k++){
            var wtLimit = wtSheet.getRange(j,k).getValue();
            // Adjust the value for the generated random number by changing the default value of 5
            // if you want to limit the range of random numbers generated
           var randWt = Math.floor(Math.random()*5)+wtLimit-5
            if(randWt < 0){
             randWt = 0
            }
            // This picks the highest randomised destination for the wagon from each of the randomised calculations
           if (randWt > hiWt && wtSheet.getRange(1,k).getValue() != wagSheet.getRange('E' + wagRow).getValue()){
              hiWt = randWt;
             newDest = wtSheet.getRange(1,k).getValue();
            }
          }
        }
     }
    trainSheet.getRange('E'+i).setValue(newDest);
    }
    var range = trainSheet.getRange("A2:E"+trainLen);
    range.sort([4,5]);
  }

function moveWagon(){
  var cellMove = e.range.getA1Notation()
}

 

Conclusion

This implementation of my freight handling program isn't perfect. It is a bit of a compromise between what I aspire to and what is achievable given the structure and limitations of the Google Sheets environment. However, as Google Sheets is a free resource, I am happy to live with its shortcomings.

I will probably tweak the programming and make a few adjustments to its structure when I make more extensive use of it out in the garden. In the meantime, I hope some of you will find it useful. I will be very interested in hearing about any modifications, extensions and improvements which others might make to it.