Versions Compared

Version Old Version 30 New Version 31
Changes made by

Viktoria Karakiko

Viktoria Karakiko

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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


Functionality

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:

  • Go to the Setup ribbon tab and click the General Codes button.
  • On the Structure pane, click the Custom Export code category to expand it and select Export.
  • Click the New button in the Actions group of the Manage ribbon tab. The General Code Editor (Export) dialog then appears:
    IMG
    • 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.

    AnchorCreate_Groups_SectionsCreate_Groups_SectionsCreate 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:
    Image Removed
    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

    Info

    You can optionally add a comment to the Tag field. The comment will then appear in the XML output file.

    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:
      Image Removed
    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:

    Property

    Description

    Date-Time FormatThe property is available for the DateTime type. Set the format which you want to display date and time. For example: yyyy/mm/dd, dd.mm.yyy.Sign Position

    The property is available for the Integer and Float field types. Specify the position of a sign (plus or minus) by selecting one of the options:

    • spBeforeNumber sets a sign (plus or minus) before a numeric value
    • spAfterNumber sets a sign (plus or minus) after a numeric value
    Visible Sign

    The property is available for the Integer and Float field types. Specify whether the defined sign (plus or minus) is to be available in the output file. For this, select one of the options:

    • vsNegative makes a sign (plus or minus) unavailable
    • vsPositive makes a sign (plus or minus) available

    Fractional Decimal

    Count

    The property is available for the Float field type. Enter the number of digits you want to be displayed to the right of the decimal point. Show Fractional Part

    The property is available for the Float field type. Choose whether you want to display the decimal part of a numeric value by selecting one of the options:

    • True displays the decimal part
    • False hides the decimal part
    Decimal SeparatorThe property is available for the Float field type. Enter a decimal separator that is used to separate the integer part from the decimal part.

    Type

    The property is available for all the fields. Define the type of the data source in a field by selecting one of the options: ftDataField, ftParamField. If ftParamField is set for the field, then a user of an export file will be able to enter a value in the dialog form, and this value will be as a data source. The engine automatically creates the corresponding input parameters in the Parameters sector (see Define Parameters). Otherwise, in case the Type property is set to ftDataField, the data source will be fetched from the SQL statement in the parent section.

    Field Name

    The property is available for all the fields. Enter the name of a new parameter. The value is fetched to the Parameters sector in case FieldType equals ftParamField. For more information, see Define Parameters.

    The property is available for all the fields. Specify the value to be used by default in case the value itself is empty in the data source. The value is fetched to the Parameters sector in case FieldType equals ftParamField. For more information, see Define Parameters.

    Caption

    The property is available for all the fields. Specify the title of the column in the output template file. The value will be fetched to the Parameters sector. This property can be used with the MS Excel and Comma Separated Values output file formats. For more information, see Set File Format.

    Length

    This section will show you how to import an export template and execute it. 



    Execute Export Template

    Once created, the export template may be distributed between customers. A customer can import the template and use it with his/her own data. To import a template, proceed as described below:

    1. Click the Template button under the Template pane. This opens the menu, where you can select the Import option: 
      Image Added
    2. When the Open system dialog appears, select the directory where the export template file is located and click Open:
      Image Added

    As a result, the export template opens in the General Code Editor (Export) dialog:
    IMG

    On processing the template on customer's real data and maybe adding the necessary modifications (see Define Dynamic Parameters), a customer can save the export file by clicking the OK button at the bottom of the the General Code Editor (Export) dialog. A new custom export code then appears in the grid:

    IMG

    You can easily access the processed export template file. For this, navigate to the Tools ribbon tab > Export > Custom export > export file name:
    Image Added

    On executing the export, the export engine generates of the Export dialog form based on the parameters in the export template:



    Info

    In the dialog, all the fields are mandatory.



    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:
      Image Added
    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.

    Image Added
    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:
      Image Added

      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:

    Image Added

    Info

    You can optionally add a comment to the Tag field. The comment will then appear in the XML output file.


    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:
      Image Added
    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:
    Image Added


    Let us review the template field properties in detail:

    Filler  symbol to be used to fill empty spaces at the end of the output string. If not specified, spaces are used as default

    Property

    Description

    Date-Time FormatThe property is available for the DateTime type. Set the format which you want to display date and time. For example: yyyy/mm/dd, dd.mm.yyy.
    Sign Position

    The property is available for the Integer and Float field types. Specify the position of a sign (plus or minus) by selecting one of the options:

    • spBeforeNumber sets a sign (plus or minus) before a numeric value
    • spAfterNumber sets a sign (plus or minus) after a numeric value
    Visible Sign

    The property is available for the Integer and Float field types. Specify whether the defined sign (plus or minus) is to be available in the output file. For this, select one of the options:

    • vsNegative makes a sign (plus or minus) unavailable
    • vsPositive makes a sign (plus or minus) available

    Fractional Decimal

    Count

    		The property is available for the Float field type. Enter the number of digits you want to be displayed to the right of the decimal point. 
    Show Fractional Part

    The property is available for the Float field type. Choose whether you want to display the decimal part of a numeric value by selecting one of the options:

    • True displays the decimal part
    • False hides the decimal part
    Decimal SeparatorThe property is available for the Float field type. Enter a decimal separator that is used to separate the integer part from the decimal part.

    Type

    The property is available for all the fields. Enter a numeric value that defines the actual length of the column. If the property is set to 0, the export engine does not restrict the column length.

    Max Length

    The property is available for all the fields. Enter a numeric value that defines the maximum length of the field.

    Define the type of the data source in a field by selecting one of the options: ftDataField, ftParamField. If ftParamField is set for the field, then a user of an export file will be able to enter a value in the dialog form, and this value will be as a data source. The engine automatically creates the corresponding input parameters in the Parameters sector (see Define Parameters). Otherwise, in case the Type property is set to ftDataField, the data source will be fetched from the SQL statement in the parent section.

    Field Name

    		The property is available for all the fields. Enter the Alignname of a new parameter. The value is fetched to the Parameters sector in case FieldType equals ftParamField. For more information, see Define Parameters.

    The property is available for all the fields. Set Specify the value alignment in a column by selecting one of the options:

    • vaLeft displays values in left-to-right format
    • vaRight display values in right-to-left format

     

    AnchorDefine_ParametersDefine_ParametersDefine 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 Image Removed 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:
      Image Removed
      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 Image Removedbutton in the Mask column. This opens the List editor dialog where you can select the general code to be displayed in the output file:
      Image Removed
      Click OK to save the records and exit the dialog.
    AnchorDefine_Dynamic_ParametersDefine_Dynamic_ParametersDefine 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.

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

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

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

    ASCI Fixed Length PropertiesDescriptionOutput extensionDefine the extension of an export output file (.txt, .ads, etc).Use UTF8 charsetSpecify the encoding to be used. For this, select one of the options:
    • True sets the encoding to UTF8 charset
    • False sets the encoding that is used on your operating system.
     Properties of
    Comma-Separated ValuesDescriptionHeader

    Select whether you want to display column headers by selecting one of the options:

    • True allows displaying column headers (fetched from the Caption field property) in the output file
    • False cancels displaying column headers in the output file 
    Quote MarkAccording to the CSV standard, text values with spaces between must be surrounded by a quotation mark.DelimiterEnter a delimiter to be used to separate values.Record delimiterEnter a delimiter to be used at the end of each row in the output export file.Output extensionDefine the extension of an export output file (.txt, .ads, etc).Use UTF8 charsetSpecify the encoding to be used. For this, select one of the options:
    • True sets the encoding to UTF8 charset
    • False sets the encoding that is used on your operating system.
    MS Excel PropertiesHeader

    Select whether you want to display column headers by selecting one of the options:

    • True allows displaying column headers (fetched from the Caption field property) in the output file
    • False cancels displaying column headers in the output file 
    Output extensionDefine the extension of an export output file (.txt, .ads, etc).Use UTF8 charsetSpecify the encoding to be used. For this, select one of the options:
    • True sets the encoding to UTF8 charset
    • False sets the encoding that is used on your operating system.

    Save Export Template

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

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

    • When the Save As system dialog appears, specify the save location, enter a file name and and click Save:
    Image Removed

    Info

    A template is exported in the XML file format only.

    1. Functionality

    Once created, the export template may be distributed between customers. A customer can import the template and use it with his/her own data. To import a template, proceed as described below:

    1. Click the Template button under the Template pane. This opens the menu, where you can select the Import option: 
      Image Removed
    2. When the Open system dialog appears, select the directory where the export template file is located and click Open:
      Image Removed

    As a result, the export template opens in the General Code Editor (Export) dialog:
    IMG

    On processing the template on customer's real data and maybe adding the necessary modifications (see Define Dynamic Parameters), a customer can save the export file by clicking the OK button at the bottom of the the General Code Editor (Export) dialog. A new custom export code then appears in the grid:

    IMG

    Besides,

    Functionality

    Once created, the export template may be distributed between customers. A customer can import the template and use it with his/her own data. To import a template, proceed as described below:

    1. Click the Template button under the Template pane. This opens the menu, where you can select the Import option: 
      Image Removed
    2. When the Open system dialog appears, select the directory where the export template file is located and click Open:
      Image Removed

    As a result, the export template opens in the General Code Editor (Export) dialog:
    IMG

    On processing the template on customer's real data and maybe adding the necessary modifications (see Define Dynamic Parameters), a customer can save the export file by clicking the OK button at the bottom of the the General Code Editor (Export) dialog. A new custom export code then appears in the grid:

    IMG

    You can easily access the processed export template file. For this, navigate to the Tools ribbon tab > Export > Custom export > export file name:
    Image Removed

    On executing the export, the export engine generates of the Export dialog form based on the parameters in the export template:

    InfoIn the dialog, all the fields are mandatory

    to be used by default in case the value itself is empty in the data source. The value is fetched to the Parameters sector in case FieldType equals ftParamField. For more information, see Define Parameters.

    Caption

    The property is available for all the fields. Specify the title of the column in the output template file. The value will be fetched to the Parameters sector. This property can be used with the MS Excel and Comma Separated Values output file formats. For more information, see Set File Format.

    Length

    The property is available for all the fields. Enter a numeric value that defines the actual length of the column. If the property is set to 0, the export engine does not restrict the column length.

    Max Length

    The property is available for all the fields. Enter a numeric value that defines the maximum length of the field.

    FillerThe property is available for all the fields. Enter the symbol to be used to fill empty spaces at the end of the output string. If not specified, spaces are used as default

    Align

    The property is available for all the fields. Set the value alignment in a column by selecting one of the options:

    • vaLeft displays values in left-to-right format
    • vaRight display values in right-to-left format

     


    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 Image Added 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:
      Image Added
      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 Image Addedbutton in the Mask column. This opens the List editor dialog where you can select the general code to be displayed in the output file:
      Image Added
      Click OK to save the records and exit the dialog.


    Anchor
    Define_Dynamic_Parameters
    Define_Dynamic_Parameters
    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:

    ASCI Fixed Length PropertiesDescription
    Output extensionDefine the extension of an export output file (.txt, .ads, etc).
    Use UTF8 charsetSpecify the encoding to be used. For this, select one of the options:
    • True sets the encoding to UTF8 charset
    • False sets the encoding that is used on your operating system.
     


    Properties of
    Comma-Separated Values    		Description
    Header

    Select whether you want to display column headers by selecting one of the options:

    • True allows displaying column headers (fetched from the Caption field property) in the output file
    • False cancels displaying column headers in the output file 
    Quote MarkAccording to the CSV standard, text values with spaces between must be surrounded by a quotation mark.
    DelimiterEnter a delimiter to be used to separate values.
    Record delimiterEnter a delimiter to be used at the end of each row in the output export file.
    Output extensionDefine the extension of an export output file (.txt, .ads, etc).
    Use UTF8 charsetSpecify the encoding to be used. For this, select one of the options:
    • True sets the encoding to UTF8 charset
    • False sets the encoding that is used on your operating system.


    MS Excel Properties
    Header

    Select whether you want to display column headers by selecting one of the options:

    • True allows displaying column headers (fetched from the Caption field property) in the output file
    • False cancels displaying column headers in the output file 
    Output extensionDefine the extension of an export output file (.txt, .ads, etc).
    Use UTF8 charsetSpecify the encoding to be used. For this, select one of the options:
    • True sets the encoding to UTF8 charset
    • False sets the encoding that is used on your operating system.




    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, specify the save location, enter a file name and and click Save:
      Image Added

      Info

      A template is exported in the XML file format only.