I am trying to make a field that holds the values of 20 other fields so that I can create a drop down list for a parameter. For example I have twenty fields which are labels (label1, label2, label3, label4, label5, etc.) I would like a new field that holds all of the values from the labels so that I have one list of all labels.

Is there a way to take the values of 20 different fields and combine them into a new field name.

Below is my attempt at a new define, however I only obtained data from BUSCODE1 and all remaining BUSCODE fields were ignored once it found data in BUSCODE1.

DEFINE FILE AUTHORIZATIONS
BUS_VALUE/A50=
IF BUSCODE1 NE '' THEN BUSCODE1 ELSE
IF BUSCODE2 NE '' THEN BUSCODE2 ELSE
IF BUSCODE3 NE '' THEN BUSCODE3 ELSE
IF BUSCODE4 NE '' THEN BUSCODE4 ELSE
IF BUSCODE5 NE '' THEN BUSCODE5 ELSE
IF BUSCODE6 NE '' THEN BUSCODE6 ELSE
IF BUSCODE7 NE '' THEN BUSCODE7 ELSE
IF BUSCODE8 NE '' THEN BUSCODE8 ELSE
IF BUSCODE9 NE '' THEN BUSCODE9 ELSE
IF BUSCODE10 NE '' THEN BUSCODE10 ELSE
IF BUSCODE11 NE '' THEN BUSCODE11 ELSE
IF BUSCODE12 NE '' THEN BUSCODE12 ELSE
IF BUSCODE13 NE '' THEN BUSCODE13 ELSE
IF BUSCODE14 NE '' THEN BUSCODE14 ELSE
IF BUSCODE15 NE '' THEN BUSCODE15 ELSE
IF BUSCODE16 NE '' THEN BUSCODE16 ELSE
IF BUSCODE17 NE '' THEN BUSCODE17 ELSE
IF BUSCODE18 NE '' THEN BUSCODE18 ELSE
IF BUSCODE19 NE '' THEN BUSCODE19 ELSE
IF BUSCODE20 NE '' THEN BUSCODE20 ELSE 'NONE';
END

LDW,

Can't test, so, here's a best guess. Other's may have idea's to assist:

 
-SET &ECHO=ALL;
-SET &CNTR = 1;
 
SET ASNAMES = ON

DEFINE FILE AUTHORIZATIONS
     PUT_ALL_FIRST/A50 = 'All';
-REPEAT DEF_COLS 20 TIMES
    BUS_CODE&CNTR/A50 = IF BUSCODE&CNTR NE '' THEN BUSCODE&CNTR ELSE ' ';
-SET &CNTR = &CNTR + 1;
-DEF_COLS
END
-SET &XCNTR1 = 1;
-SET &XCNTR2 = 1;
TABLE FILE AUTHORIZATIONS
SUM
    PUT_ALL_FIRST AS 'BUS_CODE'
-REPEAT PRT_COLS 20 TIMES
    BUS_CODE&XCNTR1 AS 'BUS_CODE' 
-SET &XCNTR1 = &XCNTR1 + 1;
-PRT_COLS
   BY PUT_ALL_FIRST AS 'BUS_CODE'
-REPEAT SORT_COLS 20 TIMES
  BY BUS_CODE&XCNTR2 AS 'BUS_CODE'
-SET &XCNTR = &XCNTR2 + 1;
-SORT_COLS
-* 
Put your WHERE test(s) here
-*
 ON TABLE HOLD AS BUSCODES FORMAT XML
END
-EXIT

You only showed 20 columns, so, I hard-coded 20.

Now, you should have 21 values that includes ALL, unless they are blank.

Hope this helps...

Tom

EDIT:
Coding ERROR:

ON TABLE HOLD AS BUSCODES FORMAT XML
S/B
ON TABLE PCHOLD FORMAT XML

OOPPSS!!

This message has been edited. Last edited by: Tom Flynn,

This sounds like another perfect application for the McGuyver Technique! See https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/3221072782

Set up the McGuyver Master and data files as Danny-SRL described, then your code will look something like this:

FILEDEF FSEQ DISK C:\ibi\apps\focalpoint\fseq.dat

JOIN BLANK WITH DEPT IN AUTHORIZATIONS
TO BLANK IN FSEQ AS M_
END

DEFINE FILE AUTHORIZATIONS
BLANK/A1 WITH BUSCODE1=' ';
BUSVALUE/A50=IF COUNTER EQ 1 THEN BUSCODE1 ELSE
IF COUNTER EQ 2 THEN BUSCODE2 ELSE
...
IF COUNTER EQ 20 THEN BUSCODE20 ELSE '';
END

TABLE FILE AUTHORIZATIONS
PRINT BUSVALUE
WHERE COUNTER LE 20
ON TABLE PCHOLD AS CODELIST FORMAT XML
END