Dynamics AX Blog - Page 19

If the follwoing error occurs, when opening or synchronizing a - mostly new created - table

Cannot select a record in DMOTable (DMOTable). The SQL database has issued an error.

the reason could be, that the table contains a field, whose name is a "reserved word" from the database, For example, you cannot use Primary as field Name.

Find below some code-examples, how to Access external databases from Dynamics AX.

The examples are based on the following entry in MSDN:
How to: Connect to an External Database from X++ Code [AX 2012]
 

Read access (SELECT)

// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("ExternalDB_32bit");
    loginProperty.setDatabase("ExternalDatabaseName");

    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        sql = "SELECT * FROM items;";

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSet.next())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print strFmt("%1 - %2", strRTrim(resultSet.getString(1)), strRTrim(resultSet.getString(2)));
        }

        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

I recently had the problem that when i synchronize the database in the form database Synchronize database errors and/or warnings have been issued, in which i was sure that these have already been resolved.

A simple solution to this behaviour is to delete the contents of the table SqlSyncInfo.

The table can be found in the AOT under System Documentation > Tables > SqlSyncInfo.

Incidentally, there is also the form SysSqlSync, which can be opened at any time and there you can view the result of the last synchronization.

Using the report CustTransList (Accounts receiveable > Reports > Transactions > Customer > Transaction) i would like to demonstrate, how it is possible to call a report in a other language, the the client is currently running in.

There are no classes available for this report, so i have to cretae my own one:

class CustTransListController extends SrsReportRunController
{
}

public static str getReportName(Args _args)
{
    str reportName = ssrsReportStr(CustTransList, Report);
    return reportName;
}

public static void main(Args _args)
{
    CustTransListController  controller = new CustTransListController();
    controller.parmReportName(CustTransListController::getReportName(_args));
    controller.parmArgs(_args);
    controller.parmReportContract().parmRdlContract().parmLanguageId('en-us');
    
    controller.startOperation();
}

Using this class now it is possible, to run the report in every language (which should be provided using the parmLanguageId()-method.

Following display-method (created in the datasource of the form custtable) shows the  financial dimension Costcenter.

public display DimensionValue showCostCenter(CustTable _custTable)
{
    DimensionAttributeValueSet dimensionAttributeValueSet;
    DimensionAttributeValueSetItem dimensionAttributeValueSetItem;
    DimensionAttributeValue dimensionAttributeValue;
    DimensionAttribute dimensionAttribute;

    #define.CostCenterDimensionName("CostCenter");
 
    if( !_custTable || !_custTable.DefaultDimension)
    {
        return '';
    } 

    dimensionAttributeValueSet = DimensionAttributeValueSet::find(_custTable.DefaultDimension); 

    select firstOnly RecId
    from dimensionAttributeValueSetItem
        where dimensionAttributeValueSetItem.DimensionAttributeValueSet == dimensionAttributeValueSet.RecId
    join DimensionAttributeValue
        where DimensionAttributeValue.RecId == dimensionAttributeValueSetItem.DimensionAttributeValue
    join RecId from dimensionAttribute
    where dimensionAttribute.RecId == DimensionAttributeValue.DimensionAttribute
       && dimensionAttribute.Name  == #CostCenterDimensionName
    

    if(dimensionAttributeValue && dimensionAttribute)
    {
        return dimensionAttributeValue.getValue();
    }

    return '';
}

Simpler version (see comments)

public display DimensionValue showCostCenter(CustTable _custTable)
{
    #define.CostCenterDimensionName("CostCenter");
 
    return ((select firstOnly DisplayValue from DefaultDimensionView
             where DefaultDimensionView.Name == #CostCenterDimensionName
                && DefaultDimensionView.DefaultDimension == _custTable.DefaultDimension).DisplayValue);
}

Sometimes you have the requirement that a certain financial dimension is to be set on the basis of an input in a form.

The following code example is something relatively easy to implement. In the example, whenever a particular field is changed - queried on the method modified() of the field of a DataSource, the financial dimension Costcenter will be filled with the value 25. If the value to be entered an invalid, nothing happens (error message or similar).

public void modified()
{
    DimensionAttribute DimensionAttribute = DimensionAttribute::findByName("Costcenter");
    DimensionValue newValue = '25';    // New dimension value
    
    super();
    
    dimensionDefaultingController.setDimensionAttributeValue(
        DimensionAttribute,
        DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute, newValue).RecId, 
        newValue);
}

In the example, an existing instance of DimensionDefaultingController used.
 

The SELECT-Statement in the example lists only active BOM items (Table BOM) on (active using the fields FromDate and ToDate). If the second parameter of the macro is empty (zero date ()), so all BOM items are listed.

static void useMacroInSelectStatement(Args _args)
{
    bom bom;
    date emptyDate;
    
    // parameters: %1 = table instance, %2 date, %3 empty date value
    #localmacro.bomDateFilter
        && ( %2 == dateNull() || (
            ((%1.FromDate <= %2) && (%1.ToDate >= %2)) ||
            ((%1.FromDate == %3) && (%1.ToDate == %3)) ||
            ((%1.FromDate <= %2) && (%1.ToDate == %3)) ||
            ((%1.FromDate == %3) && (%1.ToDate >= %2))
            ))
    #endMacro
    ;
    
    while select bom
    where bom.ItemId == '123'
    #bomDateFilter(bom, systemDateGet(), emptyDate)
    {
        info(bom.bomid);
    }
}