AX 7 Development introduction exam available now

Exam MB6-890

https://www.microsoft.com/en-us/learning/exam-mb6-890.aspx

Sales quotation GST through X++

X++ code to achieve this;

static void
SalesTax_Per_SalesQuotation(Args _args)

{

   
SalesQuotationTable    
salesQuotationTable;

   
SalesQuotationTotals   
salesQuotationTotals;

    container               cont;

    

   
salesQuotationTotals = SalesQuotationTotals::construct(SalesQuotationTable::find("QUOT000022"));

   
salesQuotationTotals.calc();

    cont         =
salesQuotationTotals.displayFieldsCurrency(salesQuotationTotals.currencyCode());

    info(strFmt("Sales
quotation total GST: %1", conpeek(cont,
TradeTotals::posTaxTotal())));

}

GST/TAX at sales order and sales line level through X++

Sales order GST:

Total GST of Sales order can be seen from following screen in AX 2012


X++ code to achieve this;

static void SalesTax_Per_SalesOrder(Args _args)

{

    TaxTmpWorkTransForm     taxTmpWorkTransForm;

    SalesTotals             salesTotals;

    TaxSales                taxSales;

    SalesLine               salesLine;

    container               cont;

    salesTotals = SalesTotals::construct(SalesTable::find("SAOR000770"));

    salesTotals.calc();

    cont         = salesTotals.displayFieldsCurrency(salesTotals.currencyCode());

    info(strFmt("Sales order total GST: %1", conpeek(cont, TradeTotals::posTaxTotal())));

}

X++ code to achieve this;
This code will print GST against each sales line for selected/passed sales order

static void SalesTax_Per_SalesLine(Args _args)

{

    TaxTmpWorkTransForm     taxTmpWorkTransForm;

    SalesTotals             salesTotals;

    TaxSales                taxSales;

    SalesLine               salesLine;

    salesTotals = SalesTotals::construct(SalesTable::find("SAOR000770"));

    salesTotals.calc();

    taxSales = salesTotals.tax();



    taxTmpWorkTransForm = TaxTmpWorkTransForm::construct();

    taxTmpWorkTransForm.parmTaxObject(taxSales);

    While select SalesLine where salesLine.SalesId == 'SAOR000770'

    {

        taxTmpWorkTransForm.updateTaxShowTaxesSourceSingleLine(tableNum(SalesLine), salesLine.RecId, true);

        info(strFmt('%1',taxTmpWorkTransForm.parmTaxAmountCurTotal()));

    }

}

How to get Next RecId through X++

Below piece of code can be used to retrieve next record Id from the table.

static void getNextRecId(Args _args)

{

    //Table that stores record ids details for tables

    SystemSequences systemSequences;

    //Class that handles Record id generation

    SystemSequence systemSequence = new SystemSequence();

    ;

    info(strFmt("%1", tableNum(SalesTable)));  

   

    systemSequence.suspendRecIds(tableNum(SalesTable));

    info(strFmt("Next record id: %1", systemSequence.reserveValues(1,tableNum(SalesTable))));

    systemSequence.removeRecIdSuspension(tableNum(SalesTable));

}

Lifecycle issue search directly from AX 2012 R3

AX 2012 R3 comes with new feature of searching any issue, if there is any, against each AOT object.

Let's take example of SalesTable table and try to lookup issues reported for this object in LCS.

It will redirect you LCS site, and requires Partner or Customer source Id to log in.

It lists the total number of issues against SalesTable table along with all hotfixes information.

PostLoad() method in AX tables

Today I found an interesting thing with regards to AX tables and this post is all about this new experience which is the PostLoad() method in AX tables.

When we create a new table in AOT, Morphx automatically creates a series of methods for it. We cannot see those methods but those can be overriden on each table as per requirements. PostLoad() is one of these system methods. List of all system methods can be seen from

here https://msdn.microsoft.com/en-us/library/aa625830.aspx

Lets create a new table, I named it PostLoadTable with two fields Name and Value.

I added 5 records in the table manually (open table and added).

Tables in Microsoft Dynamics AX have a number of system-defined methods, such as insert, validateField,validateWrite. For a list of these methods, see the xRecord system class. The xRecord class can be seen as the base class for the Common table. The Common table can be seen as the base table for all tables.

In any table class, the body of each system-defined method contains only a call to super(). When you edit the X++ code in a table method, you override the parent's implementation of that method. Usually the call to super() must remain in the methods that you edit.

PostLoad() is the method that is used to read records from database and you can perform any custom logic by overriding this method on any table.

Let’s override this method and play around it

public void postLoad()

{

    super();

    if (this.Name == 'MEL')

        this.Value = 5;

}

By opening table again it shows me different result as compared to what we entered earlier as shown above.

Value for name MEL changes from 1 to 5 and I did not require to call update method.

There are many existing implementation of postLoad method in AX tables, I am listing few of them here for ease.

HCMWorkerTable\postLoad()

VendTrans\PostLoad()

CustTrans\PostLoad()

How to check table size from SQL server

We often require to check size of the table to moniter database performance, specially working with files and storing it in database. AX does store files in database which can be setup under Document types in Document Management module.

We can use following sql command to get table size;

sp_spaceused docuValue

Retrieve worker email address through X++

Below code snipet is one of the way to retrieve employee/worker's primary email address.

private LogisticsElectronicAddressLocator getInstructorEmail(HcmWorkerRecId  _workerRecId)
{
    LogisticsElectronicAddress  logisticsElectronicAddress;
    HcmWorker                   hcmWorker;
    DirPerson                   dirPerson;
    DirPartyTable               dirPartyTable;

    select hcmWorker
        where hcmWorker.RecId == _workerRecId
    join dirPerson
        where dirPerson.RecId == hcmWorker.Person
    join dirPartyTable
        where dirPartyTable.RecId == dirPerson.RecId
    join logisticsElectronicAddress
        where dirPartyTable.PrimaryContactEmail == logisticsElectronicAddress.RecId;

    return logisticsElectronicAddress.Locator;
}

AX 2012 upgrade - Connect to source database failure

During database upgrade checklist I was failed to connect with source database. I followed the suggested steps from upgrade guide as pasted below but these weren't enough in my case for successful connection.

Configure matching user permissions

Windows integrated security is used to connect to the source database from the target system. If the administrative user who is performing the upgrade on the target system does not have access to the source system database, the source system will reject the database connection. Open Microsoft SQL Server Management Studio on the source system and perform the following steps:

1.     Grant Microsoft Dynamics AX database access to a domain user with administrative privileges on the target Microsoft Dynamics AX 2012 system.

2.     Add the user to the db_owner and public roles.

SOLUTION: 

Make sure you have assigned sysadmin role to the AOS service account from SQL management studio under this path SQLSERVERINSTANCE\Security\Logins\<AOSService Account>. Right click on AOSService account and choose Server Roles from left hand side panel and assign sysadmin role listed on right hand side.

Unit Conversion Check AX2009 Vs AX2012

AX 2009

if(!UnitConvert::canConvert(this.inventTableModuleInvent().UnitId, this.SAB_ProdPickingUOM, this.ItemId))

AX 2012

unitConvFound = UnitOfMeasureConverter::canBeConverted(UnitOfMeasure::findBySymbol(this.inventTableModuleInvent().UnitId).RecId,                                                             UnitOfMeasure::findBySymbol(this.SAB_SalesPickingUOM).RecId,

                                                                   this.Product);

Show methods in AX lookups

This sample code is used to show Customer Name in lookup, In AX 2009 we had name field in CustTable but now its moved to DirPartyTable. We still have name() in CustTable which we can use to display name in lookups.

AX 2009

client static void lookupInterCompanyItemId(FormStringControl   lookupCtrl, DataAreaId _dataAreaId = "")

{

    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(InventTable), lookupCtrl);

    Query                   query = new Query();

    QueryBuildDataSource    qbds = query.addDataSource(tablenum(InventTable));

    ;

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemId));

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemName));

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemGroupId));

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemType));

    if(_dataAreaId)

        qbds.company(_dataAreaId);

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

}

AX 2012

 client static void lookupInterCompanyItemId(FormStringControl   lookupCtrl, DataAreaId _dataAreaId = "")

{

    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(InventTable), lookupCtrl);

    Query                   query = new Query();

    QueryBuildDataSource    qbds = query.addDataSource(tablenum(InventTable));

    ;

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemId));

    sysTableLookup.addLookupfield(fieldnum(InventTable, NameAlias));

    sysTableLookup.addLookupMethod(tableMethodStr(InventTable,itemGroupId));

    sysTableLookup.addLookupfield(fieldnum(InventTable, ItemType));

    if(_dataAreaId)

        qbds.company(_dataAreaId);

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

}

InventTrans table data model changes in AX 2012 vs AX 2009

The purpose of this post is to give an overview about the changes been made in the Dynamics AX 2012 data model related to inventory transactions.

For more details; how to implement code changes for inventTrans table the best resource is this white paper Implementing InventTrans refactoring for Microsoft Dynamics AX Applications AX2012

Before AX2012, the only table used for recording all the inventory transactions was InventTrans.  In AX2009, a lot of data in InventTrans is either redundant or fields are used to represent a specific type of transaction like sales, purchase, transfer etc.

In AX2012, the base is still the same i.e. every inventory still gets recorded in the InventTrans but the only difference is that the table has been more normalized now. A new table has been added called InventTransOrigin which is actually now holding the relationship between the originating tables (transaction tables) and InventTrans. The InventTransId, TransType and TransRefId have now been removed from the InventTrans and moved to the InventTransOrigin table with names InventTransId, ReferenceCategory and ReferenceId respectively.

Every transaction has now its own InventTransOrigin table. For example, in case of PurchLine the table named is InventTransOriginPurchLine which is actually referring to InventTransOrigin through the InventTransId field. Transaction tables like PurchLine, SalesLine, ProdLine etc. still contains InventTransId field but it not recommended to use/refer them. It is expected to be depreciated in future. 

AX to SQL data dictionary synchronization issues

One of my clients restored their AX database from PROD to DEV environment for AX 2009 and on database sync following error message was thrown

Cannot execute a data definition language command on ().

The SQL database has issued an error.

Problems during SQL data dictionary synchronization.

The operation failed.

Synchronize failed on 1 table(s)

This error message does not tell you which table is failing during sync, when I checked from event log I found following error logs which tells you table name but error message was totally misleading.

“Object Server 01: The database reported (session 5 (#####)): [Microsoft][SQL Native Client][SQL Server]There is already an object named '<TABLENAME>' in the database” 

"Object Server 01:  The database reported (session 23 (#####)): [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table 

'<TABLENAME>', because it does not exist or you do not have permission.. The SQL statement was: "DROP TABLE <TABLENAME>"

NOTE: In my case it was MarkupTrans table

Now, let’s play around and try to find the exact issue and resolve it.

1.  Take table properties in AOT and check ID – For me it was 30088
2. Open SQLDictionary table from SQL Management Studio and filter it with your table name, put ‘Markuptrans’ in name column. TableId column there will tell you the ID for MarkupTrans table, for my case it was 30086 which is different as shown from AOT
3. You need to change TableID value in SQLDictionary table same as we have in AOT – I changed it to 30088. You cannot change directly this TableId in SQLDictionary Table, either you can update it from SQL server management studio or make TableId field in SQLDictionary Table editable by changing its property AllowEdit = YES. You MUST have to change it back to previous state after updating tableId.
4. You can also check IDs for all fields in AOT for your table, these must be same as we have in SQLDictionary Table.

Synchronize your application again – it will be all good and will not throw such errors

Dynamics AX "7" Insights

http://www.bdosolutions.com/ca/insights/microsoft-convergence-update-part-3-dynamics-ax-roadmap-and-strategy-microsoft-dynamics-ax-7-0/

Error when validate settings in Report servers by any admin account which is not account used to install the AX reporting services extensions.

First thing you need to verify the Service account and Execution Account are properly configured under Reporting Services Configuration Manager.

After some troubleshooting, I found UAC was not turned off. I was running on MS Windows Server 2012.

UAC has to be turned off via registry by changing the DWORD "EnableLUA" from 1 to 0 in "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system". You will get a notification that a reboot is required. After the reboot, UAC is disabled.

After UAC is disabled, the issue is resolved.

social.technet.microsoft.com/.../13953.windows-server-2012-deactivating-uac.aspx

How to create Info part and use it on List page

This post is about how to create info Part in AX and use it on list page.

For this example; I am using Projects List page and will add Revenue Info Part on it.

Right click on AOT\Parts\Info Parts and create New Info Part

Assign Query to this info part I have assigned ProjTable_NoFilter query

Add New Group under Layout

Name it ProjectRevenue and set few properties as shown

Note I haven't assigned DataSource to this group, I will be using data methods to get data here.

Add new fields under ProjectRevenue group and name it RevenueAtSales and RevenueAtOrders

 

I assigned ForecastRevenueAtSalesSw data method to field RevenueAtSales, this data method is written on at \Data Dictionary\Tables\ProjTable\Methods\forecastRevenueAtSalesSw

display AmountCur forecastRevenueSw()

{

    ProjForecastOnAcc projForecastOnAcc;

    real total;

    // Sum all the prices with quantity of 1.

    select sum(SalesPrice) from projForecastOnAcc

        where projForecastOnAcc.ProjId == this.ProjId

           && projForecastOnAcc.Qty == 1;

    total = projForecastOnAcc.salesPrice;

    // Support non-unity quantities.

    while select SalesPrice, qty from projForecastOnAcc

        where projForecastOnAcc.ProjId == this.ProjId

           && projForecastOnAcc.Qty != 1

    {

        total += (projForecastOnAcc.salesPrice * projForecastOnAcc.qty);

    }

    return total;

}

Add this Info part to ProjProjectsListPage as I want to see this on Project List page.

Right click on Parts node in ProjProjectsListPage

Name it FF_ProjectRevenue

Set IsLinked Property to YES, this property will link this part to the list page based on Datasource (ProjTable)

Create new display menu item and name it FF_ProjectRevenueMenuitem

Assign this menu item as Part property in ProjProjectListPage

 

Time to see this info Part on List page, Open All Projects form and here it goes

Happy Daxture!ng

Cannot open the datafile file axapden-us.alt

Faced following error after restoring Dynamics AX databases from one AOS server to other.

Cannot open the datafile "C:\Program Files\Microsoft DynamicsAX\60\Server\DAX2012R3\bin\Application\appl\Standard\axapden-us.alt"

Error code from the openrating system: errno = 13.

Look in file 'errno.h' or in the operating system documentation for an explanation. 

Resolution:

It was the account permssion issue. 
Make sure AOS service account must have added into Dynamics AX databases with full permissions (at least db_owner). This AOS service account manages the files located at above mentioned path in error.

In my Case;

Source AOS service was running with domain user account (DOMAIN\AOSService) and Destination AOS service was running under Network Service account. When I restored AX databases from source to destination it overwrite all user accounts in destination database, which means it deleted Network Service account.  
I had two options EITHER to add Network Service account which already has all the permissions as AX was installed from this account as mentioned above OR I have to provide full permissions to the domain user account (DOMAIN\AOSService) in destination Dynamics AX databases.

Export Import Model - Power Shell & Command line statements

I myself often require following commands and worth to have these at one place to get it more quickly. 

Export a Model File
Windows PowerShell
Export-AXModel -Model <name> -File <Filename.axmodel>
Example: daxture is a model name and it exist in C drive
Export-AXModel -Model daxture -File C:\daxture.axmodel

Export-AXModel -Server SERVERDB -Database DataBaseName –Model "Model Name" -File c:\temp\xxxxx.axmodel

AXUtil Command
axutil export /model:<modelname> /file:<filename> /verbose
Example: daxture is a model name and it exist in C drive
axutil export /model:"daxture" /file:"C:\daxture.axmodel" /verbose

Import a Model File
Windows PowerShell
Install-AXModel -File <Filename.axmodel> -Details
Example: daxture is a model name and it exist in C drive
Install-AXModel -File C:\daxture.axmodel -Details

Install-AXModel -Server SERVERDB -Database DatabaseName -File c:\temp\xxxxx.axmodel -DETAILS -Conflict Overwrite

In case of conflict we can use this command; Intall -AXModel -File <Filename.axmodel> -Details -Conflict Push

AXUtil Command
axutil import /file:<filename> /verbose

Example: file exist in C drive
axutil import /file:"C:\daxture.axmodel" /verbose

AxUtil commands

Creating a model in layer axutil create /model:"daxture" /Layer:CUS

Delete a model axutil delete /model:"daxture"

Delete a layer axutil delete /layer:ISV

Delete a layer from database and AXServer axutil delete /layer:ISV /db:<database> /s:<server>