Description

The custom export functionality is intended for creating an export template file with a set of dynamic and static properties. Once created, the export template is delivered to a customer encouraged to fill it out and send it for further processing.

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, Sections, Fields.
   • 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.

Anchor
Create_Groups_Sections Create_Groups_Sections

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.

IMG
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:
  
  
  

  Info
  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 supports four template fields: Integer, String, Float, DateTime. Each field has a specific list of settings and a list of common properties:
IMG

Let us review the template field properties in detail:

Anchor
Define_Parameters Define_Parameters

Define Parameters

Based on the defined field type, either ftParamField or ftDataField, you can pass dynamic or static values to the output files (see Create Groups, Sections, Fields). If a parameter is a template field where FieldType equals ftDataField, then the system fetches the parameters and their properties directly from the SQL statement.

Otherwise, in case FieldType is set to ftParamField, define the parameters properties by clicking the Export Template root node in the Template sector:

IMG
Caption. Set the parameter caption to make it available in the dialog form.
Type. Set one of the following parameter types:

• Text allows entering any values into a field. On setting the parameter type, the Mask column becomes active. In the column, you can limit the value types to be entered.
• Date allows entering dates into the field.
• Number allows entering only numeric values.
• DropDown gives the possibility to set the predefined list of values. On setting the DropDown type, click the  button in the Mask column. This opens the List editor dialog where you can add the values one by one by clicking the Add Item button:
  
  Click OK to save the records and exit the dialog.
• Lookup enables you to define the list of values based on the General Code values. On setting the Lookup type, click the button in the Mask column. This opens the List editor dialog where you can select the general code to be displayed in the output file:
  
  Click OK to save the records and exit the dialog.

Define Dynamic Parameters

The export engine has three types of parameters used in the SQL statement in the section and/or group. They are:

• inline. The parameter is used for adding some values into the SQL statement when preparing an export file.
• dataset
• inherit. The inherit parameter picks up data from the parent section only.

By using the dataset and inline parameters, a customer has the possibility to enter an additional SQL parameter without modifying the export template.

To define a parameter, first, make sure to surround it in the angle brackets <> and follow the rules below based on the parameter type in use:

• for an inline parameter, use the '@' prefix followed by a parameter name
• for a dataset parameter, use the ':' prefix followed by a parameter name
• for an inherit parameter, no prefix is required

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.

Anchor
Set_File_Format Set_File_Format

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.

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

   
   

Let us consider the properties in detail:

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:

Name

Type

Align

Length

Mask

Source

PIN

Integer

Right

6

000001

PW001P01.PIN

FIRSTNAME

String

Left

15

Leave blanks

PW001P01.FIRSTNAME

LASTNAME

String

Left

20

Leave blanks

PW001P01.LASTNAME

BIRTHDATE

Date Time

-

-

YYYYMMDD

PW001P01.RANK

CLIENT

Integer

Left

10

0000000000

PW001P01.CLIENT

CUSTOM_FIELD

String

Left

20

Leave blanks

PW001P0U.*

CODE

String

Left

10

Leave blanks

PW001P03.CODE

DATEFROM

Date Time

Left

-

DDMMYYY

PW001P03.DATEFROM

DATETO

Date Time

Left

-

DDMMYYY

PW001P03.DATETO

TODATEESTIMATED

Date Time

Left

-

DDMMYYY

PW001P03.TODATEESTIMATED

PAYSCALETABLE

String

Left

10

Leave blanks

PW001P03. PAYSCALETABLE

PAYSCALE

String

Left

10

Leave blanks

PW001P03. PAYSCALE

                 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.

Property

Description

Type

This property defines type of data source of field. Field can have following values: ftDataField, ftParamField. If ‘ftParamField’ is set for field, then user can input the value in dialog form, and this value will be as data source. Engine creates corresponding input parameters automatically at Parameters tab. Otherwise data source will be set by default from parent(s) section.

Name

Default

The value of this property is used, when value is empty from data source.

Caption

This is title of the column. This property can be used with MS Excel and Comma Separated Values output format.

Length

It is the actual length of column. If this property is set to “0” then export engine does not restrict of length column.

Max Length

It is max length of field. For example: you need to export data

Filler

?

Align

It is type of alignment value in column.  Property have following values: vaLeft, vaRight.

    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.

Save Export Template

On creating the export template, you can now export it to your local machine. To do this, follow the steps below:
 

1. Click the Template button under the Template pane. This opens the menu, where you can select the Export option: 
   Image Added

2. When the Save As system dialog appears, enter a file name and and click Save:
   Image Added

   Info
   A template is exported as an XML file only

   
   

Functionality