Tech Tip: List Boxes and exporting arrays built from an SQL query

PRODUCT: 4D | VERSION: 11.3 | PLATFORM: Mac & Win

Published On: January 7, 2009

If you designate the result of an SQL query to a List Box and have allocated sufficient columns to the List Box in the object properties, you can extract the data from the List Box for further use.

NOTE: If you have not allocated sufficient columns and 4D dynamically allocates new columns to the List Box, this technique will not work with the 4D allocated arrays. They can only be viewed in the List Box and are not accessible to the developer.

The example below demonstrates a method of exporting the contents of those columns to a tab delimited text file.

C_LONGINT($Ndx;$Cdx;$SOA;$Cols;$Type_L)
C_POINTER($Col_P)
C_TIME($Ref_H)

ARRAY TEXT($ColNames_aT;0)
ARRAY TEXT($HeaderNames_aT;0)
ARRAY POINTER($ColVars_aP;0)
ARRAY POINTER($HeaderVars_aP;0)
ARRAY BOOLEAN($Visible_aB;0)
ARRAY POINTER($Styles_aP;0)

GET LISTBOX ARRAYS(SQLResult_LB;$ColNames_aT;$HeaderNames_aT;$ColVars_aP; $HeaderVars_aP;$Visible_aB;$Styles_aP)
$SOA:=Get number of listbox rows(SQLResult_LB)
$Cols:=Count in array($Visible_aB;True)
If (($Cols>0) & ($SOA>0))
$Ref_H:=Create document("")
If (OK=1)

  For ($Ndx;1;$SOA)
   For ($Cdx;1;$Cols)
    $Col_P:=Get pointer($ColNames_aT{$Cdx})
    $Type_L:=Type($Col_P->)

    Case of
     : (($Type_L=Text array ) | ($Type_L=String array ))
       SEND PACKET($Ref_H;$Col_P->{$Ndx})
     : ($Type_L=Real array )
       SEND PACKET($Ref_H;String($$Col_P->{$$Ndx};"###,###,##0.000"))
     : (($Type_L=LongInt array ) | ($$Type_L=Integer array ))
       SEND PACKET($Ref_H;String($$Col_P->{$$Ndx};"###,###,##0"))
     : ($Type_L=Date array )
       SEND PACKET($Ref_H;String($$Col_P->{$$Ndx};Internal date short special ))
     : ($Type_L=Boolean array )
       SEND PACKET($Ref_H;String($$Col_P->{$$Ndx};"True;False"))
     : ($Type_L=Pointer array )

     : ($Type_L=Picture array )

     Else

    End case

    If ($Cdx<$Cols)
     SEND PACKET($Ref_H;"\t")
    Else
     Case of
       : (MyOS=Mac OS )
         SEND PACKET($Ref_H;"\r")
       Else
         SEND PACKET($Ref_H;"\r\n")
     End case
    End if
   End for
  End for

  CLOSE DOCUMENT($Ref_H)
End if
End if