Form.List.S

A lot of people are unaware of some of the features provided by the system for manipulating multivalues. One of the classic features is the FORM-LIST command. This is a way of taking a field containing multivalues (such as maintained by a relational index) and producing from it an active select list which can then be used to query against a file. The syntax for FORM-LIST is

          FORM-LIST  file.name record.name field.name.containing.mvs

For example if you were maintaining a relational index on your invoices file to post all invoices for a customer to that customer record you could simply report on all indexes for that customer by typing

          FORM-LIST CUSTOMERS AB1234 7

          LIST INVOICES BY DATE DATE AMOUNT etc.

where AB1234 is the customer key that we are interested in, and 7 is the field containing the multivalued index list.

Whilst it has been updated to follow the functionality of the new XLATE ability to extract data by field name rather than by field number, it has not been updated to permit extraction of symbolics. The code listing following rectifies this. This can be used in many circumstances, for example if you had two fields in a record containing multivalues and wished to generate a union of the two you could just create a symbolic concatenating the two and FORM.LIST.S on it.

FORM.LIST.S follows the same syntax as FORM-LIST.

Code dissection follows -

• 0016: If the first word of the sentence is RUN then the user must have typed RUN BP FORM.LIST.S so rather than abort simply remove the RUN and the BP
• 0021: This can be achieved more quickly by the use of COL2( ) and [ ] but sacrifices some ease of maintenance
• 0024: Standard Boolean check to ensure that all parameters required have at least been filled in, even if incorrectly.
• 0028: CALCULATEX has the same functionality as the CALCULATE function but it does not require that @DICT and @RECORD be loaded. This makes it easier to use in situations where information is required from several different files for one report. The calling syntax is
  
  CALCULATEX (A,B,C,D,E)
  
  where:
  A is the field name
  B is the dictionary file variable
  C is the id of the record
  D is the record itself
  E is the MV number to return (EG @MV)
• 0030: MAKE.LIST is a system subroutine documented in the AREV 1.1 Addendum which takes a field mark delimited list and makes it into a select list in the nominated cursor.
• 0031: @SAVE.SELECT is a system variable documented in the AREV 1.1 Addendum which instructs the system to return the active select list to TCL rather than discarding it.

     0001 !         Program FORM.LIST.S designed for use at TCL
     0002 *
     0003 *         Author    AMcA
     0004 *         Purpose   To duplicate the functionality of the
     0005 *                   existing FORM.LIST processor whilst
     0006 *                   working with symbolics.
     0007 *                   Copyright Sprezzatura Ltd 1989
     0008 *                   Permission is granted for REVMEDIA
     0009 *                   subscribers to use this program for
     0010 *                   any purpose. (At your own risk!)
     0011 *
     0012
     0013 DECLARE FUNCTION CALCULATEX
     0014 DECLARE SUBROUTINE MAKE.LIST,MSG
     0015 SENT = TRIM(@SENTENCE)
     0016 IF SENT [1," "] = "RUN" THEN
     0017   SENT = FIELD(SENT," ",4,3)
     0018 END ELSE
     0019 SENT = FIELD(SENT," ",2,3)
     0020 END
     0021 FILE    = FIELD(SENT," ",1)
     0022 ID        = FIELD(SENT," ",2)
     0023 FIELD = FIELD(SENT," ",3)
     0024 IF FILE AND ID AND FIELD THEN
     0025   OPEN FILE TO DATA.FILE THEN
     0026      OPEN "DICT", FILE TO DICT.FILE THEN
     0027        READ RECORD FROM DATA.FILE ,ID THEN
     0028           LIST=CALCULATEX(FIELD,DICT.FILE,ID,RECORD,0)
     0029           CONVERT @VM TO @FM IN LIST
     0030           MAKE.LIST(0,LIST,DATA.FILE,DICT.FILE)
     0031           @SAVE.SELECT = 1
     0032        END ELSE
     0033           MSG("%1% is not on file "," "," ",ID)
     0034        END
     0035      END ELSE
     0036        MSG("Unable to open DICT %1%"," "," ",FILE)
     0037      END
     0038   END ELSE
     0039      MSG("Unable to open %1%"," "," ",FILE)
     0040   END
     0041 END ELSE
     0042   MSG("Format is FORM.LIST.S File Id Field"," "," "," ")
     0043 END

(Volume 1, Issue 3, Pages 10,11)