Description

The custom export functionality is intended for creating an export template file based on your custom properties.

Housekeeping

The section provides step-by-step instructions on how to get everything set up.

Define Custom Export Code

To define a new export code, follow the steps below:

1. Go to the Setup ribbon tab and click the General Codes button.
2. On the Structure pane, click the Custom Export code category to expand it and select Export.
3. Click the New button in the Actions group of the Manage ribbon tab. The General Code Editor (Export) dialog then appears:
   IMG
4. Within the dialog, create an export file by filling in the fields below:

   • Enter a Code (mandatory field), a unique alpha-numeric APM code.

   • In the Text field, indicate the name of an export file and its alternative name, if needed, in the Alternative field.
   • In the Sort field, define the order number of a record (where 0 means the first record in the list).
   • In the Template sector, create groups and sections. For more information, see Create Groups and Sections.
   • In the Parameters sector, define the export file parameters. For more information, see Define Parameters.
   • In the Export Format sector, select the format of the file to be exported. For more information, see Set File Format.
     
     

On completing, click OK to save the records.

Create Groups, Sections, Fields

To create a template file, you have to add groups, sections and template fields on the Template pane of the General Code Editor (Export) dialog. When opening the dialog, by default, the system shows the Export Template root node of the template hierarchical structure. By adding groups, sections and/or fields, you will then build the structure of the template file.

To add a group or a section as a child node, use one of the ways below:

• Click the Insert button located right under the Template pane and select one of the options: Group, Section. 
  

-or

• On the Template pane, right-click the Export Template root node. When the context menu appears, point to Insert and select either Group or Section:
  
  
  

  A section is a container of fields. If an SQL Statement is defined for the section, then it will appear multiple times based on the capacity of data. Otherwise, in case a SQL statement is not defined for the section, it will be printed once. A group is similar to the section, but it does not contain any fields.

When done, opens the SQL Statement dialog where you can enter a SQL statement to retrieve data from the database:

IMG

On completing, click OK to proceed.

Besides, you can add template fields on the Template pane. To do this, follow the steps below:

1. Right-click the added section. The context menu appears:
   
   
2. In the context menu, select Insert > Field > field type.

The export engine has four types of the template fields: Integer, String, Float, DateTime. Each type has a specific list of settings and a list of common properties:
IMG

Let us review the template field properties in details:

Define Parameters

On entering the SQL statement, the parameters used in the SQL statement will be automatically fetched to the Parameters sector. If a parameter is a template field with FieldType equals to ftParamField, then system also fetches the corresponding properties like a type and a caption from the template field. Otherwise, define the parameters properties by clicking the Export Template root node in the Template sector:

• Caption. Set the parameter caption to make it available at the dialog form.
• Type. Set one of the following parameter types: 
  • 
    Text allows entering any values into a field.
    Date allows entering dates into the field.
    Number allows entering only numeric values.
    DropDown gives the possibility to define a list of values. For this, set the DropDown type and click the button located in the Mask column.
    Lookup enables to define a list of values. For this, set the Lookup type and click the button located in the Mask column.

Set File Format

In the General Code Editor (Export) dialog, you have the possibility to define the format of the export file as well as export file properties:

IMG

For this, in the Export format sector, proceed as described below:

1. In the Format drop-down list, select the preferred format of the export file: 
   • ASCI Fixed Length
   • Comma-Separated Values
   • MS Excel

2. Selecting the file format displays the corresponding properties. Edit the property values based on your preferences.

   The Output extension property is basic for all of the formats. This property is used to determine the extension of an output file.

Note. You can set restriction of access for created export, for this you need to set of ‘Security Code’ of gen code. This functionality works like another places.

Picture 1.

1. For example we have task to create export that should print some person`s information to file, and we may to set organization, and some customized field as data source in dialog form. Please see simple specification for this below.

Output format specification:

1. File should have ‘dat’ extension of output file.
2. File should have ASCI fixed length format file.

Structure of file:

   File should have 2 section:

1. It is common person information like PIN, FIRSTNAME, LASTNAME, BIRTHDATE, CUSTOM_FIELD, CLIENT, and we may have possibility to set of customize field in dialog form of export. It should be one record per each person.
2. Second section should have information about planned activities of this person, and have following fields: CODE, DATEFROM, DATETO, TODATEESTIMATED, PAYSCALETABLE, PAYSCALE.

Below you can see of detailed specification of output fields:

                 Below you can see dialog form:

1. Any export template have following list of basic elements. It is template fields, sections and groups.

The section is container of fields. If SQL Statement was defined for section, then section will be shown multiply times according to capacity of data, otherwise this section will be printed once (in case, if sql statement was not defined). The group is the same that section, but group can not contain any fields. Export engine have 4 type of template fields. It is Integer, String, Float, Date Time template field. Each type have a specific list of settings, and list of common properties like this: Type, Name, Default, Caption, Length, Max Length, Filler, and Align.  Please see the list of common properties below.

List of common properties.

    By default when user opens customize export form, system shows of root node of template tree view (please see Picture 2). It is ‘Export Template’ node. This node have some settings on right side of window, like ‘Parameters’ box, and ‘Export format’ tab. We have possibility to select one from available of output format of file, like this “ASCI Fixed Length”, “Comma-Separated Values”,  “MS Excel”. Each format have corresponding settings.  The “Output extension” property is basic for all of this formats. This property is used for determination extension of output file. Each output format have self  default extension, like this ‘txt’, ‘csv’, ‘xls’.

In our task we should set ‘dat’ extension for our export.

Picture 2.

    Next step is creating corresponding groups, sections and template fields. In our case, we need to add group, and this group will contain common person`s information. For this we need use ‘Insert’ button for creating of group in Export template. Export engine have 3 type of parameters , that was used in sql statement of section(group). It is inline, dataset, and inherit parameters. The inline parameter is used for injection some value into sql statement on stage of preparing export. Another words when you runs export, export engine compiles of sql statements according to this parameters. For example you need give possibility of user to select necessary some field, as data source for first field, without modification of export template. But you did not know what field is need for user. For definition of parameter, you need to declare name of your parameter into < > symbols. If it is inline you should use ‘@’ prefix before your name of parameter. For dataset parameters you need use ‘:’ prefix. Inherit parameter does not use any prefixes before. The dataset parameter is the same as inline parameter, but parameter did not inject himself into sql statement.

Inline and dataset parameters is filled from dialog box, because they have corresponding fields at dialog form. The inherit parameter pick up data from parent sections only.

In our task we need to provide possibility to select of client. For this we need to use inline, dataset parameters for group.

Picture 3.

Note. All parameters will be created automatically after modification of sql statement at the Parameters screen. If parameter is template field with FieldType equals ftParamField, then system takes corresponding properties like type, caption from template field automatically.

After this we need to define our parameters in dialog form. For this we need to select root node of export template and makes corresponding actions at Parameters section. (Please see picture 3).

First of all, we need to set caption of parameters, because without caption this parameters will not be shown at dialog form.  After this, you need to set type of parameter. The parameter have following types:  Text, Date, Number, DropDown, Lookup. The text type gives possibility to input any data in field, number type allows to input only numbers. The DropDown type gives possibility to define list of values for inputting. For this need to select DropDown type and clicks button, that is located into Mask column.

After necessary modifications, you can test your export, for this need to click ‘Preview’ button (Please see picture 3). This button is available for following output formats: “ASCI Fixed Length”, and “Comma Separated Values”.

Picture 4

In out export we need to create one group and 2 sections. The our group will contains basic person`s information (Please see picture 5).

Picture 5

     The first of section will not contain any sql statements, so it will be shown at least one for each person. Therefore data source of section`s fields will parent group. Please note that you need to have field name for template field is the same of title of column at sql statement. If you wants to pick up data from dataset. The next step is adding of necessary template fields in corresponding section. For this you need to select of corresponding section at tree view template, and clicks by Insert button. You can see possible actions In popup menu (please see picture 6).

Note: You can change order of template field in section. For this you need to use “Up”, “Down” buttons in context popup menu of selected template field.

Picture 6.

Picture 7.

   The second section will print list of planned activities to output file. For this we use inherit parameter in sql statement of section (pleasee see picture 7). Export is ready, you can see it in Tools – Export – Custom Export section. When user executes export, export engine according to export template generates of Export dialog form. All fields are mandatory for input by default. Please note that controls save self value after re-open export.

Export template can be save in external file, and it is distrubuted. For this you need use Template – Export/Import actions in general code.

Below, you can see example of external export that was discribed in this overview