Display dynamic number of fields

Question

I'm new to progress and I'm trying to learn dynamic queries. As a Task I gave to myself I want to read a csv file and create a table based on the contents of said file. So far everything works but I can't really seem to find a way to display the contents properly.

I have a temp-table created based on a csv input with the first line being the columns or fields of the table and everything after being the records.

Field1,Field2,Field3,Field4,Fieldn...
Value1.1,Value2.1,Value3.1,Value4.1,Valuen.1...
Value1.2,Value2.2,Value3.2,Value4.2,Valuen.1...
Value1.3,Value2.3,Value3.3,Value4.3,Valuen.1...
Value1.4,Value2.4,Value3.4,Value4.4,Valuen.1...
etc...

How can I display a dynamic number of fields and their names properly?
The following things are unknown:

• Number of fields
• Name of fields
• Values of records

The following works and shows the data in the desired format (but it's hard coded):

DO WHILE qMyTable:GET-NEXT():    
    DISPLAY 
        bMyTable:BUFFER-FIELD(1):BUFFER-VALUE LABEL 'PK'
        bMyTable:BUFFER-FIELD(2):BUFFER-VALUE LABEL 'Field1'
        bMyTable:BUFFER-FIELD(3):BUFFER-VALUE LABEL 'Field2'
        bMyTable:BUFFER-FIELD(4):BUFFER-VALUE LABEL 'Field3'
        bMyTable:BUFFER-FIELD(5):BUFFER-VALUE LABEL 'Field4'
        WITH FRAME f DOWN.
        DOWN WITH FRAME f. 
END.

I'm trying to loop over the buffer fields but I can't find a way to do it without redefining the DISPLAY command every iteration. Also I don't know how to display the labels of the fields in as a header row.

I'm looking for something like this :

/*
This doesn't work
*/
DO WHILE qMyTable:GET-NEXT():    
    DO i = 1 to iNumFields:
        DISPLAY bMyTable:BUFFER-FIELD(i):BUFFER-VALUE LABEL cTitlerow[i].
    END. 
END.

This would be the full code:

/*
Variables
*/
DEF VAR i AS INTEGER INITIAL 0 NO-UNDO. //Counter
DEF VAR iEntry AS INTEGER INITIAL 0 NO-UNDO. //Counter2
DEF VAR cTitleRow AS CHARACTER NO-UNDO. //Fields csv
DEF VAR cDataRow AS CHARACTER NO-UNDO. //Entries csv
DEF VAR cFieldName AS CHARACTER NO-UNDO. //Field
DEF VAR iNumFields AS INTEGER NO-UNDO. //Amount of Fields
DEF VAR iNumLines AS INTEGER NO-UNDO. //Amount of records
DEF VAR cTitleArray AS CHARACTER EXTENT NO-UNDO. //Fields Array
/*
Handles
*/
DEF VAR ttMyTable AS HANDLE NO-UNDO. //Temp table
DEF VAR bMyTable AS HANDLE NO-UNDO. //Buffer
DEF VAR qMyTable AS HANDLE NO-UNDO. //Query


INPUT FROM 'C:\Path\To\CSV\mycsv.csv'.

/*
Get first row for fields and field names
*/
IMPORT UNFORMATTED cTitleRow.
iNumFields = NUM-ENTRIES(cTitleRow) + 1. //Additional field for PK
EXTENT(cTitleArray) = iNumFields.



/* 
Dynamic table creation
*/
CREATE TEMP-TABLE ttMyTable.
ttMyTable:ADD-NEW-FIELD('PK', 'integer').
cTitleArray[1] = 'PK'.
DO i = 2 to iNumFields:
    iEntry = i - 1.
    cFieldName = ENTRY(iEntry,cTitleRow).
    ttMyTable:ADD-NEW-FIELD(cFieldName, 'character').
    cTitleArray[i] = cFieldName.
END.

/*
Adding and defining indexes
*/
ttMyTable:ADD-NEW-INDEX('idx', TRUE, TRUE).
ttMyTable:ADD-INDEX-FIELD('idx', 'PK', 'asc').
ttMyTable:TEMP-TABLE-PREPARE('myTable').

/*
Creating buffer
*/
bMyTable = ttMyTable:DEFAULT-BUFFER-HANDLE.

/*
Populating data
*/
REPEAT:
    IMPORT UNFORMATTED cDataRow.
    bMyTable:BUFFER-CREATE.
    bMyTable::pk = iNumLines.
    DO i = 2 to iNumFields:
        iEntry = i - 1.
        bMyTable:BUFFER-FIELD(i):BUFFER-VALUE = ENTRY(iEntry,cDataRow).
        bMyTable:BUFFER-FIELD(i):COLUMN-LABEL = cTitleArray[i].
        bMyTable:BUFFER-FIELD(i):LABEL = cTitleArray[i].
    END.

    iNumLines = iNumLines + 1.
END.

/* 
Creating query
*/
CREATE QUERY qMyTable.
qMyTable:SET-BUFFERS(bMyTable).
qMyTable:QUERY-PREPARE('for each myTable').
qMyTable:QUERY-OPEN().

/*
/*
This doesn't work
*/
DO WHILE qMyTable:GET-NEXT():      
    DO i = 1 to iNumFields:
        DISPLAY bMyTable:BUFFER-FIELD(i):BUFFER-VALUE.
    END.  
END.
*/


DO WHILE qMyTable:GET-NEXT():       
    DISPLAY 
        bMyTable:BUFFER-FIELD(1):BUFFER-VALUE LABEL 'PK'
        bMyTable:BUFFER-FIELD(2):BUFFER-VALUE LABEL 'Field1'
        bMyTable:BUFFER-FIELD(3):BUFFER-VALUE LABEL 'Field2'
        bMyTable:BUFFER-FIELD(4):BUFFER-VALUE LABEL 'Field3'
        bMyTable:BUFFER-FIELD(5):BUFFER-VALUE LABEL 'Field4'
        WITH FRAME f DOWN.
        DOWN WITH FRAME f. 
END.

qMyTable:QUERY-CLOSE().

DELETE OBJECT qMyTable.

Answer 1

This should get you started on creating a frame and a "text" widgets dynamically:

define variable f as handle no-undo.
define variable t as handle no-undo.

define variable r as integer no-undo initial 1.
define variable c as integer no-undo initial 1.

create frame f.
assign
  f:row           = 4
  f:column        = 1 
  f:width-chars   = 132
  f:box           = no
  f:top-only      = false
  f:overlay       = true
  f:name          = "something"
no-error.

create text t.
assign
  t:frame        = f
  t:name         = "text1"
  t:format       = substitute( "x(&1)", max( 1, 20, length( t:name )))
  t:row          = r
  t:col          = c
  t:screen-value = "value1"
  f:height-chars = max( r, f:height-chars )
.

f:visible = yes.

In your case you would probably want to create the frame just once at the top and then create 2 text widgets for each field - one for the label and one for the data values.

Answer 2

I'd do it this way. It would show the field value next to its name, and one record at a time. I hope this helps:

DO WHILE qMyTable:GET-NEXT():    
   DO i = 1 to iNumFields:
      DISPLAY bMyTable:BUFFER-FIELD(i):NAME
              bMyTable:BUFFER-FIELD(i):BUFFER-VALUE LABEL cTitlerow[i] WITH FRAME f DOWN.
      DOWN WITH FRAME f.
   END. 
   CLEAR FRAME f ALL.
END.

Answer 3

I think you only might have some frame issues. Your code should basically work.

This minor change with display your data but for it will display them all in one column.

DO WHILE qMyTable:GET-NEXT():      
    DO i = 1 to iNumFields:
        DISPLAY bMyTable:BUFFER-FIELD(i):BUFFER-VALUE WITH FRAME f2 DOWN TITLE "Dynamic" .
        DOWN  WITH FRAME f2 .
    END.  
END.

So output will basically be

row1column1
row1column2
row1column3
...
row1columnN
row2column1
row2column2
row2column3
...
row2columnN
etc

Instead of

row1column1    row1column2    row1column3    ...    row1columnN
row2column1    row2column2    row2column3    ...    row2columnN

One idea to get the same result is to create a frame widget dynamically as well...