Developers

Omnis Technical Note TNJS0011Apr 2021

Popup Form for exporting data from a Data Grid

for Omnis Studio 10 or above
By Andreas Pfeiffer, Senior Omnis Consultant

Many developers use a Data Grid in Remote Forms to display data. Now it would be great if there was a way to export the data directly from the grid as a CSV file. It would be even better if the user could choose the order of the columns or which columns to export. This tech note describe how you can create a popup form to do this.

You can open a modal remote form using the $clientcommand "subformdialogshow". In this example, the user will get a list with all columns shown in the data grid, they can change the order with drag & drop and click in the column "export" to omit columns. A prerequisite is that the data grid is $userdefined and that the individual columns of the data grid are named with the name of the respective column in the $columdatacol property.

Preparing the main remote form (or its superclass)

Our main remote form containing the data grid (or its superclass) has two public methods:

$getGrid which returns the reference to the Grid object in the remote form class (not the instance):

Quit method $cinst.$class().$objs.grid

And $getList which simply returns the list from the form:

Quit method iDataList

Behind a button you can then open the modal remote form "jsExport" with the $clientcommand:

On evClick   Do $cinst.$clientcommand(     "subformdialogshow",row('jsExport',#NULL,'Export List',     classes.jsExport.$width,$classes.jsExport.$height))

Note: If the remote form has the $layouttype kLayoutTypeSingle, you can set the width and height of the instance using the $width and $height property of the class. If you are using the $layouttype kLayoutTypeResponsive, then set the height and width manually.

The popup form "jsExport" creates a list of all columns from the calling window

The $construct method of the popup form then uses $cinst.$container to access the public methods of the underlying form and creates a list of columns.

Do $cinst.$container().$getGrid Returns gridRef If gridRef   Do method makeList (gridRef) Else   Do $cinst.$showmessage('grid not valid') End If

"gridRef" is a local variable of type Item Reference.

Here is the code for the private method "makeList", and "pGridRef" is a parameter of type Item Reference:

Do iDataList.$addcols(     'title',kCharacter,kSimplechar,255,'export',kBoolean,     #NULL,#NULL,'colname',kCharacter,kSimplechar,255) For pGridRef.$currentcolumn from 1 to pGridRef.$designcols   Do iDataList.$add(      pGridRef.$columnname,not(pGridRef.$columnhidden),      pGridRef.$columndatacol) End For

The instance variable iDataList (type List) is assigned to a two-column data grid so that the user can see the columns. The second column is called "export" and is of type Boolean. This allows the user to select or hide the columns for export

Hidden columns in the grid are also shown here but not initially checked because the $columnhidden property is used to set the checkmark when building the column list.

Drag & Drop the rows

If you set $dragmode to kDragData and $dropmode to kAcceptControl in the action properties of the grid, you can add code to the $event method of the grid that allows the user to change the order of the columns with drag & drop:

On evDrop   Do iDataList.$remove(iDataList.$line)   For i from pDragValue.$linecount to 1 step -1     Do iDataList.$addbefore(pDropId).$assignrow(pDragValue.[i])   End For   Do iDataList.$line.$assign(pDropId)

Preparing the export list

A button can now be used to get the columns from the list and make a list ready for export. You need the names of the columns used in the original data grid in the first line for the export.

On evClick   Do iExportList.$define() ## clear definition   Do iExportList.$add() ## line for the column header   For iDataList.$line from 1 to iDataList.$linecount     If iDataList.export       Do iExportList.$cols.$add(         iDataList.colname,kCharacter,kSimplechar,255)       Calculate iExportList.1.[iDataList.colname] as iDataList.title ## column header     End If   End For   Do iExportList.$merge($cinst.$container().$getList(),kTrue)

The last line uses the underlying form to load the data and add it to the export list.

Preparing an Object class

For the export, you need the list in the form of a tab-delimited text variable. Here you can use a function in an object class that writes the list to a text variable in CSV format.

"pDataList" is a parameter of type List, which contains the data to export, "pDelimiter" is a parameter of type Character which contains kTab as initial value.

$getStringFromList (in object class)

Begin text block For pDataList.$line from 1 to pDataList.$linecount   For column from 1 to pDataList.$colcount     Text:[pDataList.c[column]][pDelimiter]   End For   Text: (Carriage return) End For End text block Get text block returnString Quit method returnString

Exporting the data via download

Here is the export function. It needs the file control which has assigned the instance variable "iJSFileRow" of type Row as $dataname. Furthermore a task variable "tJSFileBinData" of type Binary is used which holds the content to be exported in binary form:

Do stringObj.$getStringFromList(iExportList) Returns exportData Calculate tJSFileBinData as chartoutf8(exportData) Calculate iFileName as 'export.csv' Do iJSFileRow.$define(iJSFileName,iJSMediaType,iJSVariableName) Do iJSFileRow.$assigncols(iFileName,'application/octet-stream','tJSFileBinData') Do $cinst.$objs.fileObj.$action.$assign(kJSFileActionDownload)

"stringObj" is an object variable that uses the object class with the $getStringFromList function as a subtype. The object then converts the list to be exported and writes the result to the character variable "exportData". This content is in turn converted to UTF8 using the chartoutf8 function and written to the binary task variable. Finally the row variable iJSFileRow is prepared with the future file name for the download, the media type and the name of the task variable and the file control "fileObj" gets the instruction to start the download.

You can download a library exportFromList.zip containing the classes described in this tech note, which you may like to incorporate into your own application.