Customer account statement "Field with ID '0' does not exist in table "AccountSumMap"

You may face an error while running Customer Account Statement report with "Balance other than zero" parameter checked. 

Following error shows up upon execution.

 "Field with ID '0' does not exist in table "AccountSuMap"

To fix this issue there is a KB 3146337 as mentioned below.

Multiselect lookup AX 2012

In this post will share code used to create multiselect lookup on runbase dialog.

class MultiSelectLookupExample extends RunBaseBatch
{
    // Dialog fields, groups and controls
    DialogField                 dlgFileName;
    DialogGroup                 dlgGrpFile, dlgGrpRoles;
    FormBuildControl            fbc;
    FormBuildStringControl      fbsc;
    SysLookupMultiSelectCtrl    sysms;

    // Packed variables
    FilenameOpen                fileName;
    container                   conRoles;
    container                   selectedRoles;

    #define.CurrentVersion(1)
    #define.Version1(1)
    #localmacro.CurrentList
        fileName,
        conRoles,
        selectedRoles
    #endmacro
}

public Object dialog()
{
    DialogRunbase       dialog = super();
    #resAppl
    xSysLastValue::getLast(this);
    dialog.addImage(#ImageEmployee);

    dlgGrpFile = dialog.addGroup("@SYS4000240");
    dlgFileName = dialog.addFieldValue(extendedTypeStr(FilenameOpen), this.parmFileName());

    dlgGrpRoles = dialog.addGroup("@SYS336367");
    fbc = dialog.formBuildDesign().control(dlgGrpRoles.name());
    fbsc = fbc.addControl(FormControlType::String, "@GLS221111");
    fbsc.width(400);
    fbsc.label("@GLS221111");

    return dialog;

}

public void dialogPostRun(DialogRunbase dialog)

{

    FormRun                 fr;

    container               con;

    super(dialog);

    fr = dialog.dialogForm().formRun();

    if (fr)

    {

        sysms = SysLookupMultiSelectCtrl::construct(fr, fr.design().control(fbsc.id()), queryStr(AXPSecRoleQuery), false, this.parmRolesSelect());

        con = conIns(con, 1, this.parmRolesSelect());

        con = conIns(con, 2, this.parmSelectedRoles());

        sysms.set(con);

    }

}

public boolean getFromDialog()

{

    boolean ret;

    ret = super();

    fileName = dlgFileName.value();

    this.parmRolesSelect(sysms.get());

    this.parmSelectedRoles(sysms.getSelectedFieldValues());

    return ret;

}

public container pack()

{

    return [#CurrentVersion, #CurrentList];

}

public boolean unpack(container packedClass)

{

    Version version = RunBase::getVersion(packedClass);

;

    switch (version)

    {

        case #CurrentVersion:

            [version, #CurrentList] = packedClass;

            break;

        default:

            return false;

    }

    return true;

}

public container parmSelectedRoles(container   _selectedRoles = selectedRoles)

{

    selectedRoles = _selectedRoles;

    return selectedRoles;

}

public FilenameOpen parmFileName(FilenameOpen   _fileName = fileName)

{

    fileName = _fileName;

    return fileName;

}

public container parmRolesSelect(container _conRoles = conRoles)

{

   conRoles = _conRoles;

   return conRoles;

}

public void run()

{

    #OCCRetryCount

    

    try

    {

        // Your logic goes here

        xSysLastValue::saveLast(this);

    }

    catch (Exception::Deadlock)

    {

        retry;

    }

    catch (Exception::UpdateConflict)

    {

        if (appl.ttsLevel() == 0)

        {

            if (xSession::currentRetryCount() >= #RetryNum)

            {

                throw Exception::UpdateConflictNotRecovered;

            }

            else

            {

                retry;

            }

        }

        else

        {

            throw Exception::UpdateConflict;

        }

    }

    catch (Exception::DuplicateKeyException)

    {

        // retry in case of an duplicate key conflict

        if (appl.ttsLevel() == 0)

        {

            if (xSession::currentRetryCount() >= #RetryNum)

            {

                throw Exception::DuplicateKeyExceptionNotRecovered;

            }

            else

            {

                retry;

            }

        }

        else

        {

            throw Exception::DuplicateKeyException;

        }

    }

}

D365 Extension Concluded

As we are aware of Overlayering concept used in previous version of AX where we used to modify metadata and source code, in higher layers which may increase the cost of upgrading a solution to newer version.

In AX7 onward Microsoft introduced a concept of extensions described as below.

Extensions

You can customize an application by using extensions. An extension enables you to add functionality to existing model elements and source code. Extensions provide the following capabilities:

• Creating new model elements.

• Extending existing model elements.

• Extending source code using class extensions.

• Customizing business logic, ways to customize business logic include:

o    Creating event handlers to respond to framework events, such as data events.

o    Creating event handlers to respond to event delegates that are defined by the application.

o    Creating new plug-ins.

So, in this post I would try to cover Extension on objects and what can be achieved using extensions.

Delete model in D365 Finance and Operation

ModelUtil.exe can be used for the purpose of removing or deleting a model. It is command prompt utility.

Just adding little more details here about the usage. At command prompt:

1. Browse to: K:\AOSService\PackagesLocalDirectory\bin folder.
2. Type in ModelUtil.exe /? to get to know about parameters.
3. I did import a custom model name was "LC_DEVModel" and I did remove it using following command:

K:\AOSService\PackagesLocalDirectory\Bin>ModelUtil.exe -delete -metadatastorepath="K:\AOSService\PackagesLocalDirectory" -modelname="LC_DEVModel"

Are you sure you want to delete model LC_DEVModel? (Y/N)
y

K:\AOSService\PackagesLocalDirectory\Bin>

Creating lookup control in D365

The easiest way to do lookup on your form control in D365 can be achieved by following the steps mentioned below.

1. Go to your field where lookup needs to be created 
2. Expand the Events node of form control
3. Right click OnLookup event and select "Copy event handler method"
   
4. Create a new class and name it appropriately (Naming convention "FormName"+"EventHandler")
5. In newly created class press Ctrl + V

          [FormControlEventHandler(formControlStr(SalesTable, LC_AssetGroup_LC_AssetId), FormControlEventType::Lookup)]

    public static void LC_AssetGroup_LC_AssetId_OnLookup(FormControl sender, FormControlEventArgs e)

    {

        

    }

Do your lookup code inside the handler, as shown below

FormControlEventHandler(formControlStr(SalesTable, LC_AssetGroup_LC_AssetId), FormControlEventType::Lookup)]

public static void LC_AssetGroup_LC_AssetId_OnLookup(FormControl sender, FormControlEventArgs e)

{

    Query                   query = new Query();

    QueryBuildDataSource    queryBuildDataSource;

    SysTableLookup          sysTableLookup;

    sysTableLookup = SysTableLookup::newParameters(tableNum(AssetBook), sender);

    queryBuildDataSource = query.addDataSource(tableNum(AssetBook));

    queryBuildDataSource.addRange(fieldNum(AssetBook, Status)).value(strFmt('((Status == %1) || (Status == %2))', any2int(AssetStatus::Open), any2int(AssetStatus::Closed)));

    sysTableLookup.addLookupField(fieldNum(AssetBook, AssetId), true);

    sysTableLookup.addLookupMethod(tableMethodStr(AssetBook, assetName));

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

}

Output

More than one form was opened at once for the lookup control

After implementing lookup in D365 I encountered an error "More than one form was opened at once for the lookup control" while interacting with control as shown below.

Lookup Code

FormControlEventHandler(formControlStr(SalesTable, LC_AssetGroup_LC_AssetId), FormControlEventType::Lookup)]

public static void LC_AssetGroup_LC_AssetId_OnLookup(FormControl sender, FormControlEventArgs e)

{

    Query                   query = new Query();

    QueryBuildDataSource    queryBuildDataSource;

    SysTableLookup          sysTableLookup;

    sysTableLookup = SysTableLookup::newParameters(tableNum(AssetBook), sender);

    queryBuildDataSource = query.addDataSource(tableNum(AssetBook));

    queryBuildDataSource.addRange(fieldNum(AssetBook, Status)).value(strFmt('((Status == %1) || (Status == %2))', any2int(AssetStatus::Open), any2int(AssetStatus::Closed)));

    sysTableLookup.addLookupField(fieldNum(AssetBook, AssetId), true);

    sysTableLookup.addLookupMethod(tableMethodStr(AssetBook, assetName));

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

}

In order to fix this issue we need to make sure of following things:

• No super() call in lookup code
• FormControlCancelableSuperEventArgs must be used to cancel super()

FormControlCancelableSuperEventArgs event = e as FormControlCancelableSuperEventArgs;

event.CancelSuperCall();

Lookup Code with Fix

[FormControlEventHandler(formControlStr(SalesTable, LC_AssetGroup_LC_AssetId), FormControlEventType::Lookup)]
public static void LC_AssetGroup_LC_AssetId_OnLookup(FormControl sender, FormControlEventArgs e)
{
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource;
    SysTableLookup          sysTableLookup;

    FormControlCancelableSuperEventArgs event = e as FormControlCancelableSuperEventArgs;

    sysTableLookup = SysTableLookup::newParameters(tableNum(AssetBook), sender);
    queryBuildDataSource = query.addDataSource(tableNum(AssetBook));
    queryBuildDataSource.addRange(fieldNum(AssetBook, Status)).value(strFmt('((Status == %1) || (Status == %2))', any2int(AssetStatus::Open), any2int(AssetStatus::Closed)));
    sysTableLookup.addLookupField(fieldNum(AssetBook, AssetId), true);
    sysTableLookup.addLookupMethod(tableMethodStr(AssetBook, assetName));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

    event.CancelSuperCall();
}

Hope you find this post useful :)

How to create DSN and connect to an External Database from X++

Go to Administrative Tools  >>  Data Sources (ODBC)

In the tab User DSN >> Add >> Choose SQL Server Native Client

Config DSN to your database and server

Sample Database

X++ Code

static void DSNConnectivityTest(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("TestDatabase");

    loginProperty.setDatabase("TestDB");

    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)

    {

        sql = "SELECT * FROM Persons;";

        perm = new SqlStatementExecutePermission(sql);

        perm.assert();

        //Prepare the sql statement.

        statement = odbcConnection.createStatement();

        resultSet = statement.executeQuery(sql);

        while (resultSet.next())

        {

            //Always get fields in numerical order, such as 1 then 2 the 3

            info(strFmt('Person Id: %1 -> Person Name : %2', strLRTrim(resultSet.getString(1)), resultSet.getString(2)));

        }

        //Close the connection.

        resultSet.close();

        statement.close();

    }

    else

    {

        error("Failed to log on to the database through ODBC.");

    }

}