Change Batch status X++ code

static void MKChangeBatchStatus(Args _args)
{
    batchJob    batchJob;
   
    while select forupdate batchJob
       where batchJob.Status  == BatchStatus::Waiting
   {
       ttsBegin;
       batchJob.Status = BatchStatus::Hold;
       batchJob.update();
     
       ttsCommit;
   }
   
}

Get default financial dimension values through X++ code

In the below example we will see how we can get the default financial dimension values from Purch line table.
Here we are using AxdDimensionUtil helper class to get the Dimension values.

Note:  we can replace PurchLine table with any table with DefaultDimension field.

static void Job5(Args _args)
{
    PurchLine   purchLine;
    Counter     i;
    container   conDim;
    //
    purchLine = PurchLine::find("WP00097201",1);   
    conDim = AxdDimensionUtil::getDimensionAttributeValueSetValue(purchLine.DefaultDimension);

    for (i=1 ; i<= conLen(conDim) ; i++)
    {
         info(conPeek(conDim,i));
    }
}

There are other ways to find default Dimensions values. The one above is one of them.

The same result can also be achieved using "DimensionAttributeValueSetStorage" class.

public DimensionValue getDimValue(int _index, DimensionDefault _defaultDim)

    {

       // #DocumationGL // List that matches the GL Export

       container       dimAttrList     = ['BPCTrialBalance', 'Customer', 'Department', 'Employee', 'IntercompanyCode', 'Project','ServiceCategory'];

        Name            dimAttribName   = conPeek(dimAttrList, _index);

        DimensionAttributeValueSet          DimensionAttributeValueSet;

        DimensionAttributeValueSetItem      DimensionAttributeValueSetItem;

        DimensionAttributeValue             DimensionAttributeValue;

        DimensionAttribute                  DimensionAttribute;

        ;

        select RecId from DimensionAttributeValueSet

            where DimensionAttributeValueSet.RecId == _defaultDim

        join DisplayValue from DimensionAttributeValueSetItem

            where DimensionAttributeValueSetItem.DimensionAttributeValueSet == DimensionAttributeValueSet.RecId

        join RecId from DimensionAttributeValue

            where DimensionAttributeValue.RecId == DimensionAttributeValueSetItem.DimensionAttributeValue

        join RecId from DimensionAttribute

            where DimensionAttribute.RecId == DimensionAttributeValue.DimensionAttribute

               && DimensionAttribute.Name == dimAttribName;

        return DimensionAttributeValueSetItem.DisplayValue;

    }

    display DimensionValue dimTrialBalance(ProjPostTransViewGL  _projPostTransView)

    {

        ProjJournalTrans    projJournalTrans;

        ProjCostTrans       projCostTrans;

        container   conDim;

        switch (_projPostTransView.ProjTransType)

        {

            case ProjTransType::Item:

            case ProjTransType::Hour:

            case ProjTransType::Revenue:

            case ProjTransType::OnAccount:

                select  DefaultDimension

                from    projJournalTrans

                where   projJournalTrans.TransId == _projPostTransView.TransId;

                

                return  this.getDimValue(1,projJournalTrans.DefaultDimension);

            case ProjTransType::Cost:

                select  DefaultDimension

                from    projCostTrans

                where   projCostTrans.TransId == _projPostTransView.TransId;

                return  this.getDimValue(1,projCostTrans.DefaultDimension);

            default :

                return '';

        }

    }

Find and Update the Default Dimension in AX 2012 R3 Using X++ Code

In this we are going to see how to find  or update default dimension value for an Item/Customer/Vendor.

Find Default Dimension value :

Display DimensionValue MKDim_Product()
{
    DimensionAttributeValueSetStorage   dimStorage;
    DimensionValue                      product;
    Counter                             i;
    #define.Product("Product")
//Note This in the below line can be replaced with InventTable /CustTable /VendTable
    dimStorage = DimensionAttributeValueSetStorage::find(this.DefaultDimension);

    for (i= 1 ; i<= dimStorage.elements() ; i++)
    {
        if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == #Product)
        {
            product = dimStorage.getDisplayValueByIndex(i);
        }
    }

    return product;
}

Update /Modify Default Dimension value:

//Note _defaultDimensino in the below line can be replaced with InventTable.DefaultDimension /CustTable.DefaultDimension /VendTable.DefaultDimension
//defaultDimension    = InventTable::find(itemId).DefaultDimension;
private void updateDimension(str _dimName, str _dimVal, DimensionDefault _defaultDimension)
{

    DimensionAttributeValueSetStorage   dimStorage = new DimensionAttributeValueSetStorage();
    DimensionAttribute                  dimAttribute;
    DimensionAttributeValue             dimAttributeValue;
    DimensionDefault                    defaultDimension;


    ttsBegin;
    if (_defaultDimension && _dimVal)
    {
       
        dimStorage          = DimensionAttributeValueSetStorage::find(defaultDimension);
        dimAttribute        = DimensionAttribute::findByName(_dimName);
        dimAttributeValue   = DimensionAttributeValue::findByDimensionAttributeAndValue(dimAttribute, _dimVal, true, true);

        dimStorage.addItem(dimAttributeValue);
        // Dimension modified or updated including costcentre as well.
        defaultDimension = dimStorage.save();
    }
    ttsCommit;

}

Exception handling catch standard infolog / error and write to a log table

Exception handling catch standard infolog / error and write to a log table 

Here we will see, how we can capture the standard errors / info and write them to any table.

let's create a class which extends runbase and add the all the required methods like Main, Run, etc.
in Run method, we use the Try and catch section, there we will call the method which will return a string as the info / error list.

public class MKSupplierInvoices extends RunBaseBatch
{
      public static void main(Args _args)
     {
        MKSupplierInvoices supplierInvoice  = MKSupplierInvoices::construct();
        supplierInvoice.caption();
       supplierInvoice.initQuery();
       if (supplierInvoice.prompt())
      {
           supplierInvoice.run();
      }
    }

 public void run()
{
    VendTrans               vendTrans;
     try
    {
              //Logic
    }
    catch (Exception::Error)
    {
        ttsBegin;
        errorInvoiceTable.selectForUpdate(true);
        errorInvoiceTable.PostingStatus = PostingStatus::Error;
        errorInvoiceTable.ErrorText     = this.captureInfoMessage();
        errorInvoiceTable.update();
        ttsCommit;
    }
    catch (Exception::Deadlock)
    {
        retry;
    }
    catch (Exception::UpdateConflict)
    {
        if (appl.ttsLevel() != 0)
        {
            throw Exception::UpdateConflict;
        }
        if (xSession::currentRetryCount() >= #RetryNum)
        {
            throw Exception::UpdateConflictNotRecovered;
        }
        retry;
    }
    catch
    {
        info(infolog.text());
    }
}

public str captureInfoMessage()
{
    SysInfologEnumerator    sysInfologEnumerator;
    SysInfologMessageStruct infoMessageStruct;
    ErrorMsg                logMessage;
    str                           logString;
    str                          ret;
    int                          i;
    #Define.NewLine('\n')

  
    sysInfologEnumerator = SysInfologEnumerator::newData(infolog.infologData());

    while (sysInfologEnumerator.moveNext())
    {
        i = 1;

        if (logMessage)
        {
            logMessage += #Newline;
        }

        infoMessageStruct = SysInfologMessageStruct::construct(sysInfologEnumerator.currentMessage());

        while (i <= infoMessageStruct.prefixDepth())
        {
            logString = logString + infoMessageStruct.preFixTextElement(i) + '. ';
            i++;
        }

        logString = logString + infoMessageStruct.message();
        logMessage = logMessage + infoMessageStruct.message();
    }

   
    ret = logMessage;
    return ret;
}

}

Enable / allowEdit of datasource field on a listPage (ListPageInteraction class) + AX 2012

Here we will see how to make a field alloEdit based on some condition in the ListPage form.

As we all now, every list page has the Interaction class, here we have a method selectionChanged() which is used to get the current record reference, it is similar to active method.

now Override the selectionChanged() method and write the below code to access data source property in ListPageInteraction class.

 public void selectionChanged()
{

        FormDataSource  reqPO_ds;
        ListPage    listPage = this.listPage(); 

        // This will get the recored
        ReqPO       reqPO    = listPage.activeRecord(querydatasourcestr(ReqTransPOListPage,ReqPO)) as ReqPO; 
       // This will get the dataSource Object
       reqPO_ds =   listPage.activeRecord(queryDataSourceStr(ReqTransPOListPage,ReqPO)).dataSource();

        super();
       if (//Add condition) 
       {
         allowEditFieldsOnFormDS_W(reqPO_ds, false);
         reqPO_ds.object(fieldNum(ReqPO, TruckId)).allowEdit(true);
        }
 

}

X++ Job to list all the elements which are not included in version control

 X++ Job to list all the elements which are not included in version control

static void listNonVersionControlled(Args _args)
{
    SysVersionControllable  controllable;
    SysModelElement         sme_root, sme;
    SysModelElementData     smeData;
    SysModelLayer           layer;   
   
    setPrefix('Not version controlled elements');
   
    while select sme_root
        order by elementType
        exists join sme
        exists join smeData
        exists join layer
         where sme.RootModelElement     == sme_root.RecId
            && sme.RecId                == smeData.ModelElement       
            && smeData.layer            == layer.RecId          
            && smeData.ModelId          == xInfo::getCurrentModelId()
            && layer.Layer              == currentAOLayer()
        {       
        controllable = SysTreeNode::newTreeNodePath(SysTreeNode::modelElement2Path(sme_root));       
       
        if (VersionControl.parmSysVersionControlSystem().allowCreate(controllable))
        {
            info(SysTreeNode::modelElement2Path(sme_root));
        }
    }
}

Powershell command to list all the elements in AX model and DB Sync

                               Powershell commands

Install-AXModel

Parameter Set: Default
Install-AXModel -File  [-Config  ] [-Conflict  ] [-CreateParents] [-Database  ] [-Details] [-NoOptimize] [-NoPrompt] [-Replace  ] [-Server  ] [-TargetLayer  ] [ ] 

PS C:\>Install-AXModel -File MyModel.axmodel -Conflict Push

Export-AXModel

Parameter Set: Default
Export-AXModel -File  -Model  [-Config  ] [-Database  ] [-Key  ] [-ManifestFile  ] [-Server  ] [ ]  

PS C:\>Export-AXModel -model Packaging -file c:\models\PackagingSigned.axmodel -key c:\keys\mykey.snk

 ----------------------------------------------------------------------------------------------------
Command to list all the elements in the model in AX (Installed )
(Note: here -Model 15 is user layer model)
------------------------------------------------------------------------------------
&"C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1"
$s = 'D-WBX-DEV06'
$d = 'Weetabix_Dev_Model'
(get-axmodel -server $s -database $d -model 15 -details).elements | format-table -property path -autosize
--------------------------------------------------------------------------------------------------------------------

Command to list all the elements in the model File (Not Installed).
-------------------------------------------------------------------------------------
(get-axmodel -server $s -database $d -file "C:\Lasernet\Lasernet Connector for Microsoft Dynamics AX2012 R3 CU13\Model\VAR\LAC_VAR_5002_17102018_110314_AX2012R3CU13.axmodel" -details).elements | format-table -property path -autosize

Ex:
$filepath = "F:\DevOpsRepository\Builds\1.0.0.316\Application\Appl\MK.axmodel"
$s= "DataBaseServerName"
$d= "AXDatabaseName"
(get-axmodel -server $s -database $d -file $filepath -details).elements | format-table -property path -autosize | Out-File -FilePath "F:\Tmp\WCC model content.txt" -NoClobber
-------------------------------------------------------------------------------------------------------------

AX DB Sync
---------------------------------------------------------------------------------------------------------
$axProcess = Start-Process -PassThru ($axClientPath + "C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin\Ax32.exe") -ArgumentList ($params + " -StartupCmd=Synchronize")
if ($axProcess.WaitForExit($AXSYNCTIMEOUT) -eq $false)
{
    Throw ("Error: Synchronize did not complete within " + $AXSYNCTIMEOUT / 60000 + " minutes")
}
---------------------------------------------------------------------------------------------------------------

Export list of model elements to Excel using PowerShell

(Get-AXModel -Model 'MyModel' -Details).Elements | select path, elementtype | `

Export-Csv -NoTypeInformation -Delimiter ';' c:\mymodel.csv

--------------------------------------------------------------------------

Uninstall-AXModel

Parameter Set: Default
Uninstall-AXModel -Model [-Config ] [-Database ] [-Details] [-Layer ] [-ManifestFile ] [-NoPrompt] [-Server ] [ ] 



PS C:\>Uninstall-AXModel -Model TestModel -Details

SQL DB backup and Restore script

Backup DB

USE [master]
BACKUP DATABASE [DB_NAME] TO  DISK = N'F:\SQLBackup\build_AxDB.bak' WITH NOFORMAT, INIT,  NAME = N'DBNAME', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
BACKUP DATABASE [AB_NAME_model] TO  DISK = N'F:\SQLBackup\build_AxDB_model.bak' WITH NOFORMAT, INIT,  NAME = N'DBNAME', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

Restore DBs

#USE [master]

RESTORE DATABASE [DB_NAME] FROM  DISK = N'F:\SQLBackup\build_AxDB.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Find clients using the DB

DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT
)
INSERT INTO @AllConnections EXEC sp_who2
SELECT * FROM @AllConnections WHERE DBName = 'DB_NAME'

To Kill

USE [master];

DECLARE @kill varchar(8000) = '';  

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  

FROM sys.dm_exec_sessions

WHERE database_id  = db_id('DBNAME')

EXEC(@kill);

Find Top #n tables with max size

USE [D_WBX_BUILD]
select top 30 schema_name(tab.schema_id) + '.' + tab.name as [table],
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
join sys.indexes ind
     on tab.object_id = ind.object_id
join sys.partitions part
     on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
     on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc;


Command to delete data from the table

use [DB_NAME]
--    TRUNCATE TABLE        dbo.DMFWBXASNPACKSTRUCTUREENTITY