Export data to excel file

To export data to an excel file, we can use the SysExcelWorksheetHelper.
Here an example:

static void exportExcelFile(Args _args)
{
    SysExcelApplication     xlsApplication;
    SysExcelWorkBooks       xlsWorkBookCollection;
    SysExcelWorkBook        xlsWorkBook;
    SysExcelWorkSheets      xlsWorkSheetCollection;
    SysExcelWorkSheet       xlsWorkSheet;
    SysExcelWorksheetHelper worksheetHelper;
    SysExcelHelper          sysExcelHelper;
    VendRFQJour             vendRFQJour;
    int                     row = 1;
    str                     fileName;
    str                     worksheetName;
    Date                    deliveryDate;

    fileName    = "Path\\Test.xlsx";

    vendRFQJour = VendRFQJour::find("000103");

    worksheetName = "Worksheet name";
    sysExcelHelper = SysExcelHelper::construct();
    sysExcelHelper.initialize();
    xlsWorkSheet = sysExcelHelper.addWorksheet(worksheetName);

    worksheetHelper = SysExcelWorksheetHelper::construct(xlsWorkSheet);

    worksheetHelper.addColumn(1, 'RFQ Id', Types::String);
    worksheetHelper.addColumn(2, 'Delivery date', Types::Date);

    worksheetHelper.setCellValue(1, row, 'RFQId');
    worksheetHelper.setCellValue(2, row, 'DeliveryDate');
    row++;

    worksheetHelper.setCellValue(1, row, vendRFQJour.RFQId);
    worksheetHelper.setCellValue(2, row, vendRFQJour.DeliveryDate);

    if(WinApi::fileExists(fileName))
        WinApi::deleteFile(fileName);

    sysExcelHelper.save(filename);
    sysExcelHelper.close();

    info(strFmt("Creato file: %1", fileName));
}

Contact persons created with double address

During the import of the contact person, an additional address is always created.
If, in the file, there isn’t an address, the DIEF creates an empty address.

To avoid the problem, we must to insert an additional if clause in the DMFContactPersonEntityClass, method GeneratePostalAddress:

public container GeneratePostalAddress(boolean _stagingToTarget = true)
{
    container res;

    if (_stagingToTarget && entity.Address) //additional if
        res = DMFLogisticsAddressHelper::GeneratePostalAddressGlobal(entity, target, party, partyRecId,this.getRoleSeparator(), true, _stagingToTarget);

    if (_stagingToTarget)
    {
        res = conNull();
    }

    return res;
}

Print logo depend on print destination

Goal: print the logo only if the print destination is the printer

First of all we need a parameter, and we declare it in the contract class as follow:

[DataMemberAttribute('printLogo')]
public boolean parmPrintLogo(UsePrintMgmt _printLogo = printLogo)
{
    printLogo = _printLogo;
    return printLogo;
}

Then, we must to modify the code of the report controller. The controller must extend the SrsPrintMgmtFormLetterController or a subclass of this.

The method to override is: SrsPrintMgmtController.outputReport()
This method executes the report for the print management setting that is currently loaded.

In this way, for every record in the print management, we control the print destination, and consequently we set the parameter for the logo visibility.

protected void outputReport()
{
    SalesConfirmContract salesConfirmContract = contract as SalesConfirmContract;

    switch (formLetterReport.getCurrentPrintSetting().parmPrintJobSettings().printMediumType())
    {
        case SRSPrintMediumType::Printer : salesConfirmContract.parmPrintLogo(NoYes::No);
        break;

        default : salesConfirmContract.parmPrintLogo(NoYes::Yes);
        break;
    }

    super();
}

If the logo visibility, depend on the type of the PrintCopyOriginal, the code to use is the following:

protected void outputReport()
{
    SalesConfirmContract salesConfirmContract = contract as SalesConfirmContract;
    switch (formLetterReport.getCurrentPrintSetting().parmType())
    {
        case PrintMgmtDocInstanceType::Copy :
	salesConfirmContract.parmPrintLogo(NoYes::No);
        break;

        case PrintMgmtDocInstanceType::Original :
	salesConfirmContract.parmPrintLogo(NoYes::Yes);
        break;
    }
    super();
}

Now, we have to use the new parameter in the report layout. We found the parameter here:
parameter

Finally, insert the following code in the layout, in the “Visibility” property on the image.
Aufnahme2

Use HTML in SSRS reports

Create a string that contains HTML code, for example:

(TableName).(MyFieldName) = ‘<span style=”font-weight:bold”>’ + custTable.AccountNum + ‘</span>’;

MyFieldName is the name of the field on the tmp table for the report’s  dataset.

Open the layout in Visual Studio. In the report add a textbox and add to the textbox the MyFieldName field.

Right-Click on the placeholder in the textbox, and then click on “Placeholder properties”.

Aufnahme8

Select “HTML – Interpreter HTML tags as styles

 

Item description initialization

The field  “Text”, in the Sales order line details, is initialized depend on some parameters.

Capture

The parameters to set for the initialization are the following:

Accounts receivable > Setup > Forms > Form setup > General

Capture1

  1. Include both name and description = Yes: initialize the field with both the name (product name or search name if product name does not exist) and the description inserted in the EcoResProductTranslation
  2. Include both name and description = No: initialize the field only with the description inserted in the EcoResProductTranslation
  3. External item description = Append: merge the content of the field “Text”, with the External item description of the item
  4. External item description = Overwrite: overwrite the content of the field “Text” with the External item description of the item

You can find product name and description here:

Capture2

And the external item description here:

Capture3

SSRS Extra white pages

In the report properties, depend on the “Paper size”, Visual Studio sets automatically the width of the page.

We must consider left and right margins too.

Capture

The space that we can use is:

Paper width – left margin – right margin

In our case:

8,27 – 0,7 – 0 = 7,57 in

The body width does not be greater than this size.  To control the body size:

Right click on the body layout  > Properties:

Capture1

If the body size is greater than the paper size (with margins),  the system adds extra white pages.

Create or extend Data Migration Framework

Create the Staging Table

1) In the AOT create a staging table for the entity, with the following table properties:

Property Value
SaveDataPerCompany NO
SupportInheritance NO
TableType Regular
ConfigurationKey DMF
ValidTimeStateFieldType None

2) Create fields with the following properties

Field name EDT Enum
DefinitionGroup DMFDefinitionGroupName
IsSelected DMFIsSelected NoYes
TransferStatus DMFTransferStatus
ExecutionId DMFExecutionId

DMFBEMA

IMPORTANT!
The wizard creates these four fields, but the fields “IsSelected” and “TransferStatus“ are created without EDT/Enum. They must be inserted manually!

3) Create field groups with the following properties:

Field group name Label Field
ExecutionList Execution list DefinitionGroup, IsSelected, TransferStatus, ExecutionId
Enabled Enabled OPTIONAL. Fields from the staging table which you want to make part of   template by default
<<FunctionName_Sequence>>Ex.: GeneratePostalAddress_2 <<Description offunction>> Fields from staging table which are the source for the   specified method (fields that are used in the method)
NB.: The field group “Generate” must be created even if there are no fields in them. If in the class is present the method “Generate”, the DMF table must have the associated field group (empty or not). For example, the table “DMFProductEntity” has a field group named GenerateItemGroupCompany_2”,  that is empty, but the class “DMFProductEntityClass” has a method called “GenerateItemGroupCompany”.

4)      Create a primary index for the table:

Index name Properties Field(s)
<<any name>>Ex.: Idx
AllowDuplicates NO
AlternateKey YES
DefinitionGroup, ExecutionId, fields from staging table   to define uniqueness

5)      Specify the relationship between the staging table and the target table

Create an Enum fields in the target entity

The Enum field in the target entity is represented by a string field in the staging table. You must create a new extended data type (EDT) of type string, and of appropriate length to take the enum label strings.

Capture

HcmEmployment.EmploymentType = Enum HcmEmploymentType
DMFEmployeeEntity. EmploymentType = Type String -> EDT = DMFEmploymentType

Capture

RefRecId

If the target table contains fields that are RecIds from other tables, you must convert the natural key to a RecId. There are two options:

  • Add a data source so that the referenced table can be added to the target entity query

  • Create a function

Add a datasource

For example, the target VendTable contains VendExceptionGroup, which is a RecId that comes from VendExceptionGroup Table.

Capture

In this case, the staging table must include
VendExceptionGroup. We add the table in the query, under VendTable datasource. The relationship must be specified manually.

Capture

The staging field DMFVendorEntity.VendExecptionGroup must be mapped to the target field query for DMFVendorTargetEntity . DS:VendExceptionGroup.VendExceptionGroup

Create a function

Create a method on the entity class to convert the string to a RecId. For example, the method DMFCustomerEntity.GenerateCompanyIdNAF:

public container GenerateCompanyIdNAF(boolean _stagingToTarget = true)
{
    CompanyNAFCode  companyNAFCode;
    container       res;

    if (_stagingToTarget)
    {
        select firstOnly1 RecId from companyNAFCode where companyNAFCode.CompanyIdNAF == entity.CompanyIdNAF;
        res = [companyNAFCode.RecId];
    }
    else
    {
        if (target.CompanyNAFCode)
        {
            select firstOnly1 CompanyIdNAF, RecId from companyNAFCode where companyNAFCode.RecId == target.CompanyNAFCode;
        }

        res = [companyNAFCode.CompanyIdNAF];
    }

    return res;
}

When you create a function approach, you must create a field group for the staging table, and the return fields on the target must be specified in the getReturnFields method in the entity class.

case methodStr(DMFCustomerEntityClass, GenerateCompanyIdNAF) :
     con += [fieldstrToTargetXML(fieldStr(CustTable, CompanyNAFCode))]; 
     break;

Create a class

1)      In the AOT create a class for the entity with the following properties:

  • [DMFAttribute(true)]

  • DMFClassName extends DMFEntityBase

  • Declare the object for the staging table with the name “entity”.

  • Declare the object for the main table for the target entity with the name “target”.

  • The following example shows how the DMFBEMATableEntityClass is declared.

[DMFAttribute(true)]
public class DMFBEMATableEntityClass extends DMFEntityBase
{
    DMFBEMATableEntity entity;
    BEMATable target;
}

2)      Create the new method:

  • Must take the staging table as parameters

  • The value entity should be initialized from a parameter. The following example shows the DMFBEMATableEntity new method

public void new(DMFBEMATableEntity _entity)
{
    entity = _entity;
}

3)      Create the construct method:

  • Must take the staging table as a parameter

  • Must create and return the object of the current class by using a parameter. The following example shows the DMFBEMATableEntity construct method

public static DMFBEMATableEntityClass construct(DMFBEMATableEntity _entity)
{
    DMFBEMATableEntityClass entityClass = new DMFBEMATableEntityClass(_entity);
    return entityClass;
}

4)      Create the setTargetBuffer method:

  • A target entity can have multiple data sources. One of the data sources is the main table that represents the entity. In the setTargetBuffer method, the parameter _dataSourceName represents the data sources that are present in the target entity query

  • Depending on the data source, you may need to initialize a local instance of the table for the data source. The local instance can then be used in the functions that are required for data migration. The target should be initialized by the main table that represents the target entity

public void setTargetBuffer(Common _common, Name _dataSourceName = "")
{
    switch(_common.TableId)
    {
        case tableNum(BEMATable) :   
            target = _common;
            break;
    }
}

5)      Set the RunOn property of the class to the value CalledFrom

Write functions to import and export data

You can map data from the staging to the target table in two ways:

  1. Assign fields: A field from staging is directly assigned to a field in target. In this case the data types for the staging and target fields must be same

  2. Write a function to transform the field values from staging to target. You can write X++ functions to transform and map data from staging to target

Write functions

Data import and export functions that you define must perform the following actions:

  1. Input (Source): the entire staging record is available as a local variable to the class, so there is no need to pass any parameters to this class

  2. Output (Target):  as a result of executing the function, zero or multiple fields in the target entity are set. The return type of the function is a container, which can hold zero or more values to set on the target

The sequence of values that are returned by a particular function must be defined in the getReturnFields method.

addStagingLink method

The addStagingLink method is used to define the relationship between the staging and target table when it cannot be defined by using the relations property of the staging table.

The target query and the staging record are available in the method, so the range between target and staging can be added using code.

Example: DMFEmployeeEntityClass

public static Query addStagingLink(Query query, TableId _entityTableId, Common _staging)
{
    QueryBuildDataSource    qbd;

    qbd = query.dataSourceTable(tableNum(HcmWorker));
    qbd.addRange(fieldNum(HcmWorker,PersonnelNumber)).value(_staging.(fieldNum(DMFEmployeeEntity,PersonnelNumber)));
    return query;
}

getReturnFields method

The getReturnFields method is used to specify the default output or target fields for the functions that are used for data migration.

Parameters include:

  •  _entity: entity name

  • _name: function name

The function must return a container, as well as the name of the data source in the target entity query with which the method should be executed, and the name of the data source field in the target entity query which should be initialized by executing the function.

public static container getReturnFields(Name _entity, MethodName _name)
{
    DataSourceName dataSourceName = queryDataSourceStr(DMFEmployeeTargetEntity, HcmEmploymentDetail);
    Container      con            = [dataSourceName];

    Name fieldstrToTargetXML(FieldName _fieldName)
    {
        return DMFTargetXML::findEntityTargetField(_entity,dataSourceName,_fieldName).XMLField;
    }

    switch (_name)
    {

        case methodStr(DMFEmployeeEntityClass,GenerateReasonCode) :
            con += [fieldstrToTargetXML(fieldStr(HcmEmploymentDetail, TransitionReasonCode))];
            break;

        //change datasource
        case methodStr(DMFEmployeeEntityClass,GenerateDefaultDimension) :
            dataSourceName = queryDataSourceStr(DMFEmployeeTargetEntity, HcmEmployment);
            con            = [dataSourceName];
            con += [fieldstrToTargetXML(fieldStr(HcmEmployment, DefaultDimension))];
            break;
        default :
            con = conNull();
    }

    return con;
}

This tells the migration tool which table and field, the value passed back from the generate method should go.

Capture