Update SQLDictionary

When you have a TransactionDB from one environment and ModelDB from another, you will face DBSync issues. This is a very common problem where SQLDictionary in TransDB refers to the objects from older ModelDB hence it throws the error with TableId/FieldId mismatch or Typecasting issue.
The below job is used to update the SQLDictionary in some case when you have issues in DBSync:

static void fixTableAndFieldIds(Args _args)
{
    Dictionary dictionary = new Dictionary();
    SysDictTable dictTable;
    DictField dictField;
    TableId tableId;
    FieldId fieldId;
    SqlDictionary sqlDictionaryTable;
    SqlDictionary sqlDictionaryField;

    setPrefix("Update of data dictionary IDs");
    tableId = dictionary.tableNext(0);
    ttsbegin;

    while (tableId)
    {
        dictTable = new SysDictTable(tableId);

        setPrefix(dictTable.name());

        if (!dictTable.isSystemTable() && !dictTable.isView())
        {
            //Finds table in SqlDictionary by name in AOT, if ID was changed.
            //Empty field ID represents a table.
            select sqlDictionaryTable
                where sqlDictionaryTable.name == dictTable.name()
                && sqlDictionaryTable.fieldId == 0
                && sqlDictionaryTable.tabId != dictTable.id();

            if (sqlDictionaryTable)
            {
                info(dictTable.name());
                //Updates table ID in SqlDictionary
                if (ReleaseUpdateDB::changeTableId(
                    sqlDictionaryTable.tabId,
                    dictTable.id(),
                    dictTable.name()))
                {
                    info(strFmt("Table ID changed (%1 -> %2)", sqlDictionaryTable.tabId, dictTable.id()));
                }
            }

            fieldId = dictTable.fieldNext(0);

            //For all fields in table
            while (fieldId)
            {
                dictField = dictTable.fieldObject(fieldId);

                if (!dictField.isSystem())
                {
                    //Finds fields in SqlDictionary by name and compares IDs
                    select sqlDictionaryField
                        where sqlDictionaryField.tabId == dictTable.id()
                        && sqlDictionaryField.name == dictField.name()
                        && sqlDictionaryField.fieldId != 0
                        && sqlDictionaryField.fieldId != dictField.id();

                    if (sqlDictionaryField)
                    {
                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                            dictTable.id(),
                            sqlDictionaryField.fieldId,
                            -dictField.id(),
                            dictTable.name(),
                            dictField.name()))
                        {
                            info(strFmt("Pre-update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -dictField.id()));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }

            fieldId = dictTable.fieldNext(0);

            //For all fields in table
            while (fieldId)
            {
                dictField = dictTable.fieldObject(fieldId);

                if (!dictField.isSystem())
                {
                    select sqlDictionaryField
                        where sqlDictionaryField.tabId == dictTable.id()
                        && sqlDictionaryField.name == dictField.name()
                        && sqlDictionaryField.fieldId < 0;

                    if (sqlDictionaryField)
                    {
                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                            dictTable.id(),
                            sqlDictionaryField.fieldId,
                            -sqlDictionaryField.fieldId,
                            dictTable.name(),
                            dictField.name()))
                        {
                            info(strFmt("Final update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -sqlDictionaryField.fieldId));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
        }
        tableId = dictionary.tableNext(tableId);
    }
    ttscommit;
}

Reference: http://sashanazarov.blogspot.in/2012/09/id-change-in-dynamics-ax-data-dictionary.html

Display method on InventOnHandItem form

Hi guys,

Today I faced a very typical issue while adding a new display field in InvnetOnHandItem. I had to show the sum of a quantity from a customized table based on ItemId and Warehouse. Now the problem here was the grouping between InventSum and InventDim due to which I was not getting the value of InventDimId in InventSum.

So after a lot of failed techniques, I found a solution to use the InventDim values and the code is below:

display public InventQtyReservOrdered ale_PendingVerificationQty(InventSum _inventSum)
{
    InboundWebOrderLine     inboundWebOrderLine;
    InventDim               joinDim, dimValues;
    InventDimParm           dimParm;

    dimValues.data(_inventSum.joinChild());
    dimParm.initFromInventDim(dimValues);

    select sum(QtyOrdered) from inboundWebOrderLine
        where inboundWebOrderLine.ItemId == _inventSum.ItemId
        && (inboundWebOrderLine.EcomMerchantLocation == dimValues.InventLocationId
        || inboundWebOrderLine.EcomMerchantLocation != '')
        && inboundWebOrderLine.EcomBridgeOrderItemStatus == EcomBridgeOrderItemStatus::Ordered;

    return inboundWebOrderLine.QtyOrdered;
}

This method worked like a charm and a had sigh of relief. :)

buf2buf to buf2bufByName in Ax2012

Hi Guys,

I would like to share an interesting blog post regarding buf2buf wherein you can also copy table buffer to another table in which field names are same as the same i.e table structure should be similar of both the tables.

We can create below method in the Global class:

//Modified method, copy data from one table to another table with similar structure

static void buf2BufByName(Common  _from, Common  _to)
{
    DictTable   dictTableFrom   = new DictTable(_from.TableId);
    DictTable   dictTableTo     = new DictTable(_to.TableId);
    DictField   dictFieldFrom;
    FieldId     fieldIdFrom     = dictTableFrom.fieldNext(0);
    FieldId     fieldIdTo
    ;


    while (fieldIdFrom && ! isSysId(fieldIdFrom))
    {
        dictFieldFrom   = new DictField(_from.TableId, fieldIdFrom);


        if(dictFieldFrom)
        {
            fieldIdTo = dictTableTo.fieldName2Id(dictFieldFrom.name());


            if(fieldIdTo)
                _to.(fieldIdTo) = _from.(fieldIdFrom);
        }


        fieldIdFrom = dictTableFrom.fieldNext(fieldIdFrom);
    }
}

Source: http://mybhat.blogspot.in/2012/07/dynamics-ax-buf2buf-and-buf2bufbyname.html

Redirect WMS page to Login Screen AX2012

Hi Guys,

We had a requirement with a client wherein they wanted to see the WMS login screen rather than the WMS Homepage to save that 1 extra click ;)

So to achieve this I had to make a small change in the "Index.aspx" file located at "C:\Program Files (x86)\Microsoft Dynamics AX\60\Warehouse Mobile Devices Portal\00\Views\Home".

The change was a small script to redirect the page:

<script type="text/javascript">
setTimeout('Redirect()',10);
function Redirect()
{
  location.href = 'http://wmsServerName:port/Execute/Display';
}
</script>

So this script will take you directly from 

To this screen:

Create User from another user using X++ AX2012

While searching some issue I have a very interesting blog post about a utility which creates a new user based on an existing user. It copies the Roles and User Options from the existing user and creates a new one.

This utiltiy not limited to copy user roles but can copy User Groups and user Options too.



Some important methods of this utility are;

Create User lookup for FROM USER

public void fromUserLookup(FormStringControl userLookupControl)
{
    Query                   qry = new Query();
    QueryBuildDataSource    qbd;
    QueryBuildRange         qbr;
    SysTableLookup          sysTableLookup;
    Userinfo                userInfo;


    systablelookup = SysTableLookup::newParameters(tableNum(UserInfo), userLookupControl);
    SysTableLookup.addLookupfield(fieldNum(UserInfo, ID));
    SysTableLookup.addLookupfield(fieldNum(UserInfo, NetworkAlias));
    SysTableLookup.addLookupfield(fieldNum(UserInfo, Name));

    qbd = qry.addDataSource(tableNum(userInfo));
    qbd.addRange(fieldNum(UserInfo,Company)).value(curext());
    sysTableLookup.parmQuery(qry);
    sysTableLookup.performFormLookup();
}

Create User lookup for TO USER

public void toUserLookup(FormStringControl userLookupControl)
{
    Query                   qry = new Query();
    QueryBuildDataSource    qbd;
    QueryBuildRange         qbr;
    SysTableLookup          sysTableLookup;
    Userinfo                userInfo;


    systablelookup = SysTableLookup::newParameters(tableNum(UserInfo), userLookupControl);
    SysTableLookup.addLookupfield(fieldNum(UserInfo, ID));
    SysTableLookup.addLookupfield(fieldNum(UserInfo, NetworkAlias));
    SysTableLookup.addLookupfield(fieldNum(UserInfo, Name));

    qbd = qry.addDataSource(tableNum(userInfo));
    qbd.addRange(fieldNum(UserInfo,Company)).value(curext());
    qbr = qbd.addRange(fieldNum(UserInfo, Enable));
    qbr.value('1');
    sysTableLookup.parmQuery(qry);
    sysTableLookup.performFormLookup();
}

Function to copy USER ROLES

/// <summary>
/// copy roles assigned to one user to another
/// </summary>
/// <returns>
/// true; roles are copied across. false; roles failed to copied
/// </returns>
/// <remarks>
/// this method is used to copy user roles assigned to one user to another user.
/// </remarks>
private boolean copyUserRoles()
{
    boolean                 ret = true;

    SecurityRole            securityRole;

    SecurityUserRole        securityUserRole;
    SecurityUserRole        securityUserRoleExist;
    SecurityUserRole        securityUserRoleInsert;
    OMUserRoleOrganization  userRoleOrganization, userRoleOrganization_Insert;

    List                    copiedUserRoles = new List(Types::String);

    ListEnumerator          lEnumerator;

    setPrefix(strFmt("Copy user", fromUser, toUser));

    try
    {
        select securityRole where securityRole.AotName == 'SystemUser';
        delete_from securityUserRole where securityUserRole.User == toUser && securityUserRole.SecurityRole == securityRole.RecId;
   
        while select securityUserRole
                where securityUserRole.User == fromUser
            notExists join * from securityUserRoleExist
                where securityUserRoleExist.SecurityRole    == securityUserRole.SecurityRole
                    && securityUserRoleExist.User           == toUser
        {
            select securityRole where securityRole.RecId == securityUserRole.SecurityRole;

            copiedUserRoles.addStart(securityRole.Name);

            securityUserRoleInsert.initValue();
            securityUserRoleInsert.SecurityRole = securityUserRole.SecurityRole;
            securityUserRoleInsert.User         = toUser;
            securityUserRoleInsert.insert();
            securityUserRoleInsert.clear();

            while select userRoleOrganization
                    where userRoleOrganization.User == fromUser
                        && userRoleOrganization.SecurityRole == securityUserRole.SecurityRole
            {
                userRoleOrganization_Insert.initValue();

                userRoleOrganization_Insert.OMHierarchyType             = userRoleOrganization.OMHierarchyType;
                userRoleOrganization_Insert.OMInternalOrganization      = userRoleOrganization.OMInternalOrganization;
                userRoleOrganization_Insert.SecurityRole                = userRoleOrganization.SecurityRole;
                userRoleOrganization_Insert.SecurityRoleAssignmentRule  = userRoleOrganization.SecurityRoleAssignmentRule;
                userRoleOrganization_Insert.User                        = toUser;

                userRoleOrganization_Insert.insert();
                userRoleOrganization_Insert.clear();
            }
        }
    }
    catch
    {
        ret = false;
    }

    if (ret)
    {
        lEnumerator = copiedUserRoles.getEnumerator();

        if (copiedUserRoles.empty())
            info(strFmt("User %1 and %2 have already the same user role",fromUser, toUser));

        while (lEnumerator.moveNext())
        {
            info(strFmt('%1',lEnumerator.current()));
        }
    }
    else
        error(strFmt("User Roles aborted please review list"));

    return ret;
}

Function to copy USER OPTIONS
/// <summary>
/// copy options assigned to one user to another
/// </summary>
/// <returns>
/// true; options are copied across. false; options failed to copied
/// </returns>
/// <remarks>
/// this method is used to copy user's options assigned to one user to another user.
/// </remarks>
private boolean copyUserOptions()
{
    boolean                 ret = true;

    UserInfo                userInfoSource;
    UserInfo                userInfoTarget;

    SysUserInfo             sysUserInfoSource;
    SysUserInfo             sysUserInfoTarget;

    setPrefix(strFmt("Copy user options", fromUser, toUser));

    try
    {
        select userInfoSource
            where userInfoSource.id == fromUser
        join sysUserInfoSource
            where sysUserInfoSource.Id == userInfoSource.id;

        ttsBegin;
       
            select forUpdate userInfoTarget where userInfoTarget.id == toUser;
            userInfoTarget.filterByGridOnByDefault = userInfoSource.filterByGridOnByDefault;
            userInfoTarget.statuslineInfo = userInfoSource.statuslineInfo;
            userInfoTarget.update();

       
            select forUpdate sysUserInfoTarget where sysUserInfoTarget.Id == toUser;
            sysUserInfoTarget.DefaultCountryRegion = sysUserInfoSource.DefaultCountryRegion;
            sysUserInfoTarget.update();
        ttsCommit;

    }
    catch
    {
        ret = false;
    }

    if (ret)
    {
        info(strFmt("User %1 and %2 have already the same user options ", fromUser, toUser));
    }
    else
        error(strFmt("User Options aborted please review list "));

    return ret;
}

Function to copy USER GROUPS
/// <summary>
/// copy groups assigned to one user to another
/// </summary>
/// <returns>
/// true; groups are copied across. false; groups failed to copied
/// </returns>
/// <remarks>
/// this method is used to copy user groups assigned to one user to another user.
/// </remarks>
private boolean copyUserGroups()
{
    boolean                 ret = true;

    UserGroupList           userGroupList;
    UserGroupList           userGroupListExist;
    UserGroupList           userGroupListInsert;

    List                    copiedGroups = new List(Types::String);

    ListEnumerator          lEnumerator;

    setPrefix(strFmt("Copy user groups", fromUser, toUser));

    try
    {
        while select userGroupList
                where userGroupList.userId == fromUser
            notExists join * from userGroupListExist
                where userGroupListExist.groupId == userGroupList.groupId
                    && userGroupListExist.userId == toUser
        {
            copiedGroups.addStart(userGroupList.groupId);

            userGroupListInsert.initValue();
            userGroupListInsert.groupId = userGroupList.groupId;
            userGroupListInsert.userId  = toUser;
            userGroupListInsert.insert();
            userGroupListInsert.clear();
        }
    }
    catch
    {
        ret = false;
    }

    if (ret)
    {
        lEnumerator = copiedGroups.getEnumerator();

        if (copiedGroups.empty())
            info(strFmt("User %1 and %2 have already the same user Groups ",fromUser, toUser));

        while (lEnumerator.moveNext())
        {
            info(strFmt('%1',lEnumerator.current()));
        }
    }
    else
        error(strFmt("User Groups aborted please review list "));

    return ret;
}

Credit: http://daxture.blogspot.in/2015/01/assigning-security-roles-to-new-ax-user.html

Refresh AOD, Data and Dictionary using X++ AX2012

Using the below code you can refresh AOD, Dicitonary and Data:


static void refreshAll()
{
    #Aif
    SysSQMSettings  _sqmSettings;

    ttsBegin;
    update_recordSet _sqmSettings setting GlobalGUID = str2guid(#EmptyGuidString);
    ttsCommit;

    xSession::removeAOC();
    SysTreeNode::refreshAll();
    SysFlushSystemSequence::doFlush(); SysEvent::fireEvent(SysEventType::FlushSystemSequence);

    SysFlushDictionary::doFlush(); SysEvent::fireEvent(SysEventType::FlushDictionary);

    SysFlushDatabaseLogSetup::doFlush(curext()); SysEvent::fireEvent(SysEventType::FlushDatabaseLogSetup,0,[curext()]);
    SysFlushData::doFlush(); SysEvent::fireEvent(SysEventType::FlushData);
    SysFlushAOD::doFlush();
    SysEvent::fireEvent(SysEventType::FlushAOD);
    xSession::updateAOC();
}

Change menuitem property through X++ AX2012

In a new requirement, I had to generate a URL based on the logged-in user using a new parameters table. The URL had to be opened from EP->EmployeeServices and hence it is a URLMenuItem I was not able to handle it through URLRedirection in C#.

What I decided to change the URL property of the menuitem through code. Below is the code:

private void leaveURL(Str60 encryptedUserName)
{
    Name        encryptedName;
    TreeNode    objTreeNode;
    objTreeNode = TreeNode::findNode(@"\Web\Web Menu Items\URLs\ALE_ESSLeave\");

    if (objTreeNode)
    {
        objTreeNode.AOTsetProperties("PROPERTIES\n URL  #" + 'http://' + HcmSharedParameters::find().ALE_ESS_IP + '/oasis/AX_ESS_Integration.aspx?U=' + encryptedUserName +'&C=' + HcmSharedParameters::find().ALE_LeaveEncryptedCode + "\n ENDPROPERTIES\n");
        objTreeNode.AOTsave();
    }
}