Calculate ledger balance by dimension set in X++ in AX2012/Dynamics 365 FO

There are a variety of ways users can view balances in the general ledger. Some of the most common options are:

1. Trial balance

2. Financial reports

3. Voucher transactions

4. Ledger reports

The same can be achieved by some jobs like this to calculate ledger balance by dimension set which is derived from the class DimensionserviceProvider.

static void CalculateBalanceByFocusSet(Args _args)
{
    List                                    dimensionValuesList;
    DimensionSetContract                    dimensionSet = new DimensionSetContract();
    DimensionAttributeValueContract         dimensionAttributeValueContract;
    DimensionAttributeValueContract         dimensionAttributeValueContract2;
    DimensionAttributeValueContract         dimensionAttributeValueContract3;
    RecId                                   ledgerDimensionId;
    RecId                                   ledgerId;
    DimensionAttributeValueCombination      dimAttrValueCombo;
    LedgerBalanceDimAttrValueComboAmounts   ledgerBalance;
    DimensionServiceProvider                serviceProvider = new DimensionServiceProvider();
    DimensionSetBalanceContract             result = new DimensionSetBalanceContract();
    DimensionSetCombinationContract         dimensionSetCombinationContract;
    
    dimensionValuesList = new List(Types::Class); 
        
    dimensionAttributeValueContract = new DimensionAttributeValueContract();
    dimensionAttributeValueContract.parmName("Mainaccount");
    dimensionAttributeValueContract.parmValue("111");
    /*
    dimensionAttributeValueContract2 = new DimensionAttributeValueContract();
    dimensionAttributeValueContract2.parmName("CostCenter");
    dimensionAttributeValueContract2.parmValue("1111");
    
    dimensionAttributeValueContract3 = new DimensionAttributeValueContract();
    dimensionAttributeValueContract3.parmName("Activity");
    dimensionAttributeValueContract3.parmValue("1111");
    */
    dimensionValuesList.addEnd(dimensionAttributeValueContract);
    //dimensionValuesList.addEnd(dimensionAttributeValueContract2);
    //dimensionValuesList.addEnd(dimensionAttributeValueContract3);
    
    
    dimensionSetCombinationContract = new DimensionSetCombinationContract();
    dimensionSetCombinationContract.parmValues(dimensionValuesList);
    dimensionSetCombinationContract.parmDimensionSetName("MA"); // Based on the input dimensions we need to set the name, ex : MA+CC+AC
    
    dimensionSet.parmAccountingDateFrom(1\5\2019);
    dimensionSet.parmAccountingDateTo(30\4\2020);
    dimensionSet.parmCombination(dimensionSetCombinationContract);
    
    // Validate DimensionSetContract
    DimensionServiceProvider::validateDimensionSet(dimensionSet);

    // Setup LedgerBalanceDimAttrValueComboAmounts
    ledgerBalance = LedgerBalanceDimAttrValueComboAmounts::construct();
    ledgerBalance.parmAccountingDateRange(dimensionSet.parmAccountingDateFrom(), dimensionSet.parmAccountingDateTo());
    ledgerBalance.parmIncludeOpeningPeriod(true);//_dimensionSet.parmIncludeOpeningFiscalPeriod());
    ledgerBalance.parmIncludeRegularPeriod(true);///_dimensionSet.parmIncludeOperatingFiscalPeriod());

    if (dimensionSet.parmIsSystemGeneratedUltimo())
    {
        ledgerBalance.parmIncludeClosingPeriodBySystem(dimensionSet.parmIsSystemGeneratedUltimo());
    }
    else
    {
        ledgerBalance.parmIncludeClosingPeriod(dimensionSet.parmIncludeClosingFiscalPeriod());
    }

    ledgerBalance.parmLedgerRecId(Ledger::current());

    ledgerDimensionId = serviceProvider.buildDimStorageForDimSetCombo(dimensionSet.parmCombination());
    Debug::assert(ledgerDimensionId);

    dimAttrValueCombo = DimensionAttributeValueCombination::find(ledgerDimensionId);

    ledgerBalance.calculateBalance(dimAttrValueCombo);

    info(strFmt("%1", ledgerBalance.getAccountingCurrencyBalance()));

}

Get average unit cost of an item using x++ in D365 FO

Use this below code to get the average unit cost of an item using x++ by passing the relevant inventory dimensions and item

public class InventSumQuery
{    
    static void main(Args _args)
    {
        Qty                     totalQty;
        Amount                  cost;
        Query                   query;
        QueryRun                queryRun;
        QueryBuildDataSource    qbdsInventDim, qbdsInventSum;
        InventSum               inventSum;
   
        query = new query();

        qbdsInventSum = query.addDataSource(tableNum(InventSum)); 
        qbdsInventDim = qbdsInventSum.addDataSource(tableNum(InventDim));
        qbdsInventDim.relations(true);
        qbdsInventDim.joinMode(JoinMode::InnerJoin);
        qbdsInventDim.addRange(fieldNum(InventDim, InventSiteId)).value("");
        qbdsInventDim.addRange(fieldNum(InventDim, InventLocationId)).value("");
        qbdsInventDim.addRange(fieldNum(InventDim, wmsLocationId)).value("");
        qbdsInventSum.addGroupByField(fieldNum(InventSum, ItemId));

        qbdsInventSum.addSelectionField(fieldNum(InventSum, PostedQty), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, Deducted), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, Received), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, PostedValue), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, PhysicalValue), SelectionField::Sum);
    
        qbdsInventSum.addRange(fieldNum(InventSum, ItemId)).value("");

        queryrun = new QueryRun(query);

        while (queryRun.next())
        {
            queryRun.changed(tableNum(InventSum));
            {
                inventSum = queryRun.get(tableNum(InventSum));
                totalQty = inventSum.PostedQty - abs(inventSum.Deducted) + inventSum.Received;
                cost = abs(inventSum.PostedValue + inventSum.PhysicalValue) / totalQty;
                info(strFmt("Average unit cost - %1", abs(cost)));
            }

        }

    }
}

Import to target failed due to an update conflict as more than one process is trying to update the same record at the same time. Please try again in D365 FO

You may face this error when importing data through data entities and this mostly because of the index of the staging table.

The data in your file doesn't match with the index which causes violation and let system through this error.

Even this could happen when the index is not properly synchronized in the table that is used in the data entity. If so, try recreating the index.

Create delivery schedule for purchase line via x++ in ax 2012

I was supposed to create a delivery schedule for purchase lines via code and I created this job from the form, how the delivery schedule lines created in the backend.

The most important thing to handle is, the line number for the delivery lines. The logic needs to be created if you want to use custom line number else you can stick to the standard line numbering.

static void CreateDLVSchedule(Args _args)
{    
    List                                newScheduleLines = new List(Types::Container);
    PurchLine               		purchLine, purchLineNew;
    PurchDeliverySchedule   		purchDeliverySchedule;
    
    PurchTableForm_DeliverySchedule 	purchTableForm_DeliverySchedule;
    
    select purchLine
        where purchLine.PurchId == "PurchId"
        && purchLine.LineNumber == 10;
    
    purchTableForm_DeliverySchedule = PurchTableForm::construct(PurchTableFormId::DeliverySchedule, purchLine);    
    
    purchLineNew.setTmp();
    
    purchLineNew.data(purchLine.data());    
    
    purchTableForm_DeliverySchedule.purchLine_Init(purchLineNew);
    purchLineNew.LineNumber = purchLine.LineNumber + 1; // Add your logic to spcify line number
    purchTableForm_DeliverySchedule.purchLine_CreatePreSuper(purchLineNew);    
    newScheduleLines.addEnd([purchLineNew]);

    purchTableForm_DeliverySchedule.parmScheduleLines(newScheduleLines);
    purchTableForm_DeliverySchedule.parmMarkupConversionMode(DlvScheduleMarkupConversionMode::Copy);
    purchTableForm_DeliverySchedule.updateSchedule();
    
    info("Done");

}

Workflow escalation email notification doesn't have comments AX 2012

Workflow escalation email notification doesn't have comments AX 2012

I got a scenario, where I supposed to bring up the comments when an user notified as an escalation email from AX system (AX 2012).

After doing my complete analysing on the issue, I have noticied there is a discrepancy in the correlationId being sent from escalation process to find or track the comment from WorkflowTrackingCommentTable.

\Classes\SysWorkflowWorkItem\escalate -

By passing, the correct correlationId (parent correlationId) in the highlighted texts, brings up the comments in the workflow escalation notification.

We can get the parent correlationId from the wokrlfowWorkItemTable, and Active buffer is identified with the buffer name as workItemTable or from workflowContext.get_WorkflowCorrelationId.

Note : This have been corrected in Dynamics 365 and I'm sure, is there any hotfix released for the bug.

Post purchase confirmation journal with ordered quantity excluding canceled line.

I got a business requirement to post only ordered line quantity and the canceled line shouldn't be come into confirmation journal.

However, you can cancel the total quantity or any remaining quantity on an order, provided that the quantity hasn’t been received or invoiced.

The problem here is, the purchase confirmation journal doesn't inserts record into purchparmline table and the posted/confirmed purchase lines are displayed using the view PurchLineArchivedVersions.

At the time of posting the purchase order confirmation, we can restrict the canceled line by adding a condition purchLine.purchStatus != PurchStatus::Canceled to query when selecting the lines for confirmation journal posting. This condition skips processing the canceled lines on confirmation posting.

Also, the line needs to skipped while processing the total amount of the purchase order confirmation.
PurchTotals_Trans is the exact class where the query needs to added to skip the canceled line on calculating the total amount.

Also, setting up range on the query used on views PurchLineArchivedVersions hides the canceled line after the confirmation posted.

Finding MainAccountId from LedgerDimension record Id in Dynamics 365 FO.

There is a easy way to find or getting the main account number separated from LedgerDimension (RecId).

Main Account table holds the method findByLedgerDimension, by passing ledgerdimensionRecId will return the MainAccountTable. This method is available in AX 2012 as well.

You are not authorized to access table ‘Criteria’ (TmpSysQuery). Contact your system administrator. D365 operations

You are not authorized to access table ‘Criteria’ (TmpSysQuery). Contact your system administrator.

Make sure, the user have assigned the default role system user. System user role is a builtin role and it is mandatory for all users.

Looping dates and finding months between the dates

static void LoopDates(Args _args)
{
    TransDate date1 = 15\8\2017;
    TransDate date2 = 21\10\2017;  
   
    while (mthOfYr(date1) <= mthOfYr(date2) || mthOfYr(date2) <= mthOfYr(date1))
    {
        info(strfmt("%1",mthName(mthOfYr(date1))));
        date1 = dateMthFwd(date1,1);

        if (mthOfYr(date1) == mthOfYr(date2))
        {
            info(strfmt("%1",mthName(mthOfYr(date1))));
            break;
        }
    }
}

InventSum vs InventBatch relations in AX 2012

InventSum -> InventDim = InnerJoin
InventDim -> InventBatch = ExistsJoin 

static void inventSumQuery(Args _args)
{
    Query                   query;
    QueryRun                queryRun;
    QueryBuildDataSource    qbdsInventBatch,
                            qbdsInventDim,
                            qbdsInventSum;

    InventSum               inventSum;
   
    query = new query();
    //InventSum
    qbdsInventSum = query.addDataSource(tableNum(InventSum)); 
    //joins InventDim
    qbdsInventDim = qbdsInventSum.addDataSource(tableNum(InventDim));
    qbdsInventDim.relations(true);
    qbdsInventDim.joinMode(JoinMode::InnerJoin);
    //join InventBatch
    qbdsInventBatch = qbdsInventDim.addDataSource(tableNum(InventBatch));
    qbdsInventBatch.relations(true);
    qbdsInventBatch.joinMode(JoinMode::ExistsJoin);
    //item range   
    qbdsInventSum.addRange(fieldNum(InventSum, ItemId)).value("2664814");  

    queryrun = new QueryRun(query);

    while (queryRun.next())
    {
        queryRun.changed(tableNum(InventSum));
        {
            inventSum = queryRun.get(tableNum(InventSum));
            info(strFmt("ItemId - %1",inventSum.itemid));
        }
    }
}

Error getting when totals clicked from PurchTableListpage in AX 2012 R3

Getting the below error while clicking the Totals button in the PurchTableListpage form
Navigation
USMF/Accounts payable/Common/Purchase orders/All purchase orders/View/Totals

The error occurs when we disable the fact-boxes (may be some performance reasons).
USMF/System administration/Area page/Setup/System/Client Performances option

This error related to the part list object when it try to get initialize when the fact-boxes are disabled in AX system.

This can be resolved by changing the if condition as

if(partList.getPartById(counter) && partList.getPartById(counter).name() == formStr(PurchTotalsSummaryPart))

The same fix was resolved by MS in CU 10 release.

Caching property against table groups in Ax 2012

Uses the following caching property for the table groups,

Table Group	Cache Lookup
Parameter	EntireTable
Group	Found
Main	Found
Transaction	NotInTTS
WorksheetHeader	NotInTTS
WorksheetLine	NotInTTS
Framework	N/A
Reference	Found
Worksheet	NotInTTS
TransactionHeader	NotInTTS
TransactionLine	NotInTTS

By default table group property for the custom table Miscellaneous,  As per the standard, Custom tables should not use this group.

Reference link is attached

table caching property

Note : Wrong caching leads to unnecessary database calls.

Microsoft Dynamics AX 2012 R3 with cumulative update 10

Microsoft has recently released the latest update cumulative update 10 for AX 2012 R3 which comes with the build number of 6.3.3000.110.

What's new in Microsoft Dynamics AX 2012 R3 - CU10 is found below the link.

MicrosoftDynamicsAX2012R3-CU10

Microsoft Dynamics AX 2015 / AX 7 development environment

Folks lets dive into new Ax development IDE.

AX 2012 components access and its permissions

Component	Additional permissions that are required to install the component
Databases	Membership in the dbcreator server role in Microsoft SQL Server Membership in the securityadmin server role in SQL Server Membership in the db_accessadmin database role in SQL Server for the Microsoft Dynamics AX database If you install the databases remotely from a computer other than the database server, you must log on to the remote computer by using an account that is an administrator on the SQL Server computer. Setup requires access to SQL Server services.
Application Object Server (AOS)	Membership in the sysadmin role on the instance of SQL Server that you want to connect to
Enterprise Portal for Microsoft Dynamics AX	Membership in the System administrator role in Microsoft Dynamics AX Membership in the Administrators group in Windows on the Web server Membership in the Farm Administrators group in Microsoft SharePoint 2010 products Membership in the dbcreator role on the instance of SQL Server that is used for SharePoint 2010 products Membership in the WSS_Content_Application_Pools database role in the SharePoint_Config database
Enterprise Search	Membership in the System administrator role in Microsoft Dynamics AX Membership in the Administrator group in Microsoft SharePoint Services Membership in the dbcreator role on the instance of SQL Server that is used for Microsoft SharePoint Services
Help server	Membership in the System administrator role in Microsoft Dynamics AX
Management Reporter (server components)	Membership in the sysadmin role on the instance of SQL Server that you want to connect to. To finish the initial configuration of Management Reporter, membership is required in the Administrator role in Management Reporter.
Microsoft SQL Server Reporting Services extensions	Membership in the System administrator role in Microsoft Dynamics AX
Microsoft SQL Server Analysis Services configuration	Membership in the System administrator role in Microsoft Dynamics AX Membership in the SQL Server securityadmin server role Membership in the SQL Server db_owner database role for the Microsoft Dynamics AX database
Client	None
Microsoft Office add-ins	None
Remote Desktop Services integration	None
Report Designer for Management Reporter (client component)	None
Debugger	None
Visual Studio Tools	None
Trace Parser	None
Web services on Internet Information Services (IIS)	Membership in the System administrator role in Microsoft Dynamics AX
.NET Business Connector	None
Synchronization proxy	Membership in the dbowner database role in the SQL Server database for Microsoft Project Server Membership in the System administrator role in Microsoft Dynamics AX
Synchronization service	Membership in the System administrator role in Microsoft Dynamics AX
Management utilities	None
Retail Headquarters	None
Retail POS	None
Commerce Data Exchange: Synch Service (Retail Store Connect)	To install Synch Service, no additional permissions are required. To configure Synch Service, membership is required in the sysadmin role on the instance of SQL Server that you want to connect to.
Commerce Data Exchange: Real-time Service (Retail Transaction Service)	None
Commerce Data Exchange: Async Server	To install Async Server, no additional permissions are required. To configure Async Server, membership is required in the sysadmin role on the instance of SQL Server that you want to connect to.
Commerce Data Exchange: Async Client	To install Async Client, no additional permissions are required. To configure Async Client, membership is required in the sysadmin role on the instance of SQL Server that you want to connect to.
Retail Channel Configuration Utility (Retail Store Database Utility)	To install the utility, no additional permissions are required. To configure databases, membership is required in the sysadmin role on the instance of SQL Server that you want to connect to.
Retail SDK (Retail POS Plug-ins)	None
Retail Online Channel	None
Retail Server	None
Retail mass deployment toolkit	None
Modern POS	None
Retail channel database	None
Retail hardware station	None
RapidStart Connector	None
Warehouse Mobile Devices Portal	None
Data Import/Export Framework	Membership in the dbdatareader and dbdatawriter roles on the instance of SQL Server that you want to connect to Membership in the System administrator role in Microsoft Dynamics AX
VSS writer	Membership in the Administrators group on the computer where Microsoft System Center 2012 Data Protection Manager (DPM) is installed
Connector for Microsoft Dynamics	Permission to query for entries in Active Directory Membership in the SQL Server dbcreator server role Membership in the SQL Server securityadmin server role Membership in the sysadmin database role on the SQL Server instance that hosts the Microsoft Dynamics Integration (MSDI) database

Copy dimension from one table to another in Ax 2012

Use the below method to copy the dimension from one table another table by passing the default dimension value.

public DimensionDefault copyDimension(
    DimensionDefault _defaultDimension,
    DimensionCopy    _dimensionCopy = DimensionCopy::newFromTable(this, CompanyInfo::findDataArea(this.company()).RecId)
    )
{
    return _dimensionCopy.copy(_defaultDimension);
}

Specific dimension display in Ax 2012

private Description DimensionbyDept()
{
    DimensionAttributeValueSetStorage dimStorage;
    Counter i;
    str 20 dim;
    int64 n;

    dimStorage = DimensionAttributeValueSetStorage::find("DefaultDimension");

        for (i=1 ; i<= dimStorage.elements() ; i++)
    {
        if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == 'Department')
        {
            n= dimStorage.getValueByIndex(i);
        }
        dim = DimensionFinancialTag::find(n).Description;

    }
    return dim;

Microsoft Dynamics AX 2012 R3 cumulative update 9

Recently Microsoft released cumulative update 9 for AX 2012 R2 and R3.

Please follow the link to get to know the features comes with latest update pack.

AX 2012 R3 cumulative update 9

The call to clear the report server caches failed in AX 2012

You'll be getting the below error when refreshing the Report server caches in
AX 2012 Development ->Tools->Caches-> Refresh report server.








Even if you try to validate the report server configuration too, will end up in error.
Try running Ax 2012 as Run as Administrator and check the same.