How to create multi-select lookup dialog for class in AX 2012

Create these methods in your class:

class DICPromotion2SalesLine extends Runbase
{
    
    DialogRunbase               dialog;
    DialogGroup                 dialogGrp;
    
    FormBuildStringControl      fbsCtrlPromotion;
    FormStringControl           fsCtrlPromotion;
    container                   returnPromotion;
    SysLookupMultiSelectCtrl    msCtrlPromotion;
    str                         promotionRange;
}

protected Object dialog()
{
    FormBuildControl    setupGroupControl;
    ;
    dialog = super();
    dialog.alwaysOnTop(true);
    dialog.windowType(FormWindowType::Standard);
    dialogGrp = dialog.addGroup('Promotion');
    setupGroupControl = dialog.formBuildDesign().control(dialogGrp.formBuildGroup().id());
    fbsCtrlPromotion = setupGroupControl.addControl(FormControlType::String, identifierstr(DICPromotionID));
    fbsCtrlPromotion.label('Promotion id');
    dialog.allowUpdateOnSelectCtrl(true);
    this.dialogSelectCtrl();

    return dialog;
}
public void dialogPostRun(DialogRunbase _dialog)
{
    FormRun formRun;

    super(dialog);

    formRun = _dialog.dialogForm().formRun();

    if (formRun)
    {
        fsCtrlPromotion = formRun.design().control(fbsCtrlPromotion.id());
/// you can build with your own query, or created query in system by method construct
        msCtrlPromotion = SysLookupMultiSelectCtrl::constructWithQuery(formRun, fsCtrlPromotion, this.buildQuery());
    }
}

// build your Query here
public query buildQuery()
{
    Query                   q          = new Query();
    QueryBuildDataSource    qbds;
    DICTmpPromotionTable    tmpPromotionTable;
    ;  
    qbds    = q.addDataSource(tablenum(DICPromotionMaster));
    qbds.addSelectionField(fieldNum(DICPromotionMaster, PromotionID));
    qbds.addSelectionField(fieldNum(DICPromotionMaster, Description));
    qbds.addSelectionField(fieldNum(DICPromotionMaster, WithGroupItem));
    qbds.addSelectionField(fieldNum(DICPromotionMaster, Fromdate));
    qbds.addSelectionField(fieldNum(DICPromotionMaster, ToDate));   
        
    tmpPromotionTable = this.searchPromotion();
    while select tmpPromotionTable
    {
        qbds.addRange(fieldNum(DICPromotionMaster,PromotionID)).value(tmpPromotionTable.PromotionID);        
    }
    return q;
}

public boolean getFromDialog()
{
    boolean         ret;
    #Characters
    
    ret = super();
   
    if (msCtrlPromotion)
    {
        returnPromotion = msCtrlPromotion.getSelectedFieldValues(); // get actual value of the selected rows
        returnPeriodRecId = msCtrlPromotion.get();  // get RecIds of the selected rows  (it's very useful when you want to look one field and return another field value)  
    }
    promotionRange    = con2StrUnlimited(returnPromotion,#comma);
   
    return ret;
}


How to create form with Display Inventory Dimensions in AX 2012 with X++

Create a table with fields: ItemId, InventdimId


Add these methods to your Form :

public class FormRun extends ObjectRun
{
    InventDimCtrl_Frm_EditDimensions        inventDimFormSetup;    
}

public void  init()
{
    super();  
    element.updateDesign(InventDimFormDesignUpdate::Init);
}


Object inventDimSetupObject()
{
    return inventDimFormSetup;
}

void updateDesign(InventDimFormDesignUpdate mode)
{
    switch (mode)
    {
        case InventDimFormDesignUpdate::Init           :
            if (!inventDimFormSetup)
            {
                inventDimFormSetup  = InventDimCtrl_Frm_EditDimensions::newFromForm(element);
            }
            //inventDimFormSetup.parmSkipOnHandLookUp(true);

        case InventDimFormDesignUpdate::Active         :
            //allow choosing dimension 
inventDimFormSetup.formActiveSetup(InventDimGroupSetup::newItemId(DICPromotionAmountItem.ItemId));
            inventDimFormSetup.formSetControls(true);
            break;
        case InventDimFormDesignUpdate::FieldChange    :
            inventDimFormSetup.formActiveSetup(InventDimGroupSetup::newItemId(DICPromotionAmountItem.ItemId));
            InventDim_PromotionAmountItem.clearNotSelectedDim(inventDimFormSetup.parmDimParmEnabled());
            inventDimFormSetup.formSetControls(true);           
            break;
        default : throw error(strFmt("@SYS54195",funcName()));
    }
}

Override these methods in Form>>Datasource.

public boolean validateWrite()
{
    boolean ret;
    DICPromotionAmountItem.InventDimId = InventDim::findOrCreate(InventDim_PromotionAmountItem).InventDimId;

    ret = super();

    return ret;
}

public int active()
{
    int ret;
    ;
    ret = super();
    element.updateDesign(InventDimFormDesignUpdate::Active);
    return ret;
}

public void modified()
{
 ;
super();
element.updateDesign(
InventDimFormDesignUpdate::FieldChange);
inventDim.clearNotSelectedDim(element.inventDimSetupObject().parmDimParmEnabled());

}

Override method modified for ItemId field in your datasource
public void modified()
{
    ;
    super();
    element.updateDesign(InventDimFormDesignUpdate::FieldChange);
    InventDim_PromotionAmountItem.clearNotSelectedDim(element.inventDimSetupObject().parmDimParmEnabled());
}
In Action Pane >> create a new menuitembutton set bellow the propertitys 
 Caption  : DisplayDimensions
 Menuitemtype  : display
MenuItemName :   InventDimParmFixed


Convert row, column to Excel range in Axapta

This method is show how to convert row and column to Excel range:
rowfrom = 1,
colfrom = 1,
rowto = 5,
colto = 5;
>> A1:E5

public str convertToRange(int rowfrom, int colfrom, int rowto = 0,int colto = 0)
{
    str     strrange, srange1;

    ;
    if(rowfrom <= 0 || colfrom <= 0)
    {
        throw warning("We don`t have row <=0 or column <=0 in Excel");
    }
    if(rowto != 0 && colto != 0)
    {
        if(rowto <= 0 || colto <= 0)
        {
            throw warning("We don`t have row <=0 or column <=0 in Excel");
        }
        if(colto <= 26)
        {
            strrange = num2char(colto+64) + int2str(rowto);
        }
        else
        {
            strrange = num2char((colto-1)/26+64)+num2char(((colto-1)mod 26)+65) + int2str(rowto);
        }
    }
    if(colfrom <= 26)
    {
        srange1 = num2char(colfrom+64) + int2str(rowfrom);
    }
    else
    {
        srange1=num2char((colfrom-1)/26+64)+num2char(((colfrom-1)mod 26)+65) + int2str(rowfrom);
    }
    if(rowto != 0 && colto != 0)
    {
        return srange1 + ":" + strrange;
    }
    else return srange1;
}



How to merge custom default dimension to Ledger Dimension







static LedgerDimensionAccount replaceLedgerCustomDim(LedgerDimensionAccount _sourceDimension, Name _dimAttributeName, DimensionValue _dimensionValue)
{
//_dimAttributeName >> Site
//_dimensionValue >> "00"
//>> replace "01" to your ledger dimension Site
    #LedgerSHA1Hash
    DimensionSHA1Hash               hash; //To store the calculated hash for DimensionAttributeValueSet
    HashKey                         valueKeyHashArray[]; //To store the has key of dimension in question
    Map                             dimAttrIdx, dimAttrRecId; //to store the dimension index and backing entity type
    DimensionAttributeSetItem       dimAttrSetItem; // Contains the number of dimensions active for a account structure ledger
    DimensionAttribute              dimAttr; // Contains the financial dimensions records
    DimensionFinancialTag           dimensionFinancialTag ; //Backing entity view for Custome type dimension //>> you can change other view contain existing Dimension
    DimensionAttributeValue         dimAttrValue; // Contains used financial dimension values
    DimensionAttributeValueSet      dimAttrValueSet; //Contains default dimension records
    DimensionAttributeValueSetItem  dimAttrValueSetItem; //Contains individual records for default dimensions
    LedgerDimensionAccount          sourceDimension = _sourceDimension, targetDimension; //Record Id DimensionAttributeValueCombination table in which attribute value is to be replaced
    DimensionDefault                sourceDefDimension, targetDefDimension; //To hold the default dimension combination from a Ledger dimension
    LedgerDimensionDefaultAccount   defaultLedgerAccount;
    DimensionEnumeration            dimensionSetId; //Record id for table that contains active dimensions for current ledger

    int                             dimAttrCount, i;
    int                             backEntityType; //Stores the backing entity type for Custom type dimension
    ;

    //The Custom backing entity will be the view DimAttributeHcmWorker
    backEntityType = tableNum(DimensionFinancialTag);

    //Initialize the map to store the backing entity types
    dimAttrIdx = new Map(Types::Integer, Types::Integer);
    dimAttrRecId = new Map(Types::Integer, Types::Int64);

    //Get the record Id (dimension set id) for current ledger to find active dimensions
    dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();

    //Find all the active dimensions for current ledger except main account and store there
    //backing entity type in the map
    while select * from dimAttr
            order by Name
            where dimAttr.Type != DimensionAttributeType::MainAccount
        join RecId from dimAttrSetItem
            where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
                dimAttrSetItem.DimensionAttributeSet == dimensionSetId
    {
        dimAttrCount++;
        dimAttrIdx.insert(dimAttr.BackingEntityType, dimAttrCount);
    }

    //initialize hash key array to null
    for (i = 1; i<= dimAttrCount; i++)
    {
        valueKeyHashArray[i] = emptyGuid();
    }

    // Get the default dimensions from Ledger dimensions
    sourceDefDimension      = DimensionStorage::getDefaultDimensionFromLedgerDimension(sourceDimension);

    //Get the default account from Ledger dimensions
    defaultLedgerAccount    = DimensionStorage::getLedgerDefaultAccountFromLedgerDim(sourceDimension);

    //Find the Dimension attribute record for the dimension to work on
    dimAttr.clear();
    select firstOnly dimAttr
        where dimAttr.BackingEntityType == backEntityType &&
                dimAttr.Type            != DimensionAttributeType::DynamicAccount &&
                dimAttr.Name            == _dimAttributeName;

    //Get the backing entity type for the dimension value to process
    select firstOnly dimensionFinancialTag
        where dimensionFinancialTag.Value == _dimensionValue &&
                     dimensionFinancialTag.FinancialTagCategory == dimAttr.financialTagCategory();
    if (dimensionFinancialTag)
    {
        //Find the required Dimension Attribute Value record
        //Create if necessary
        dimAttrValue = DimensionAttributeValue::findByDimensionAttributeAndEntityInst(dimAttr.RecId, dimensionFinancialTag.RecId, false, true);

        //Store the required combination hash keys
        valueKeyHashArray[dimAttrIdx.lookup(backEntityType)] = dimAttrValue.HashKey;

        //Calculate the hash for the current values
        hash = DimensionAttributeValueSetStorage::getHashFromArray(valueKeyHashArray, dimAttrCount);

        //Null hash indicates no values exist, which may occur if the user entered an invalid value for one dimension attribute
        if (hash == conNull())
        {
            throw error(strFmt("Wrong value for %1 Dimension",_dimAttributeName));
        }

        // Search for existing value set
        dimAttrValueSet = DimensionAttributeValueSet::findByHash(hash);

        // This value set does not exist, so it must be persisted
        if (!dimAttrValueSet)
        {
            ttsbegin;

            // Insert the value set with appropriate hash
            dimAttrValueSet.Hash = hash;
            dimAttrValueSet.insert();

            //Insert Custom dimension set item
            dimAttrValueSetItem.clear();
            dimAttrValueSetItem.DimensionAttributeValueSet = dimAttrValueSet.RecId;
            dimAttrValueSetItem.DimensionAttributeValue = dimAttrValue.RecId;
            dimAttrValueSetItem.DisplayValue = dimensionFinancialTag.Value;
            dimAttrValueSetItem.insert();

            ttscommit;
        }

        //Replace the value in default dimension
        targetDefDimension = DimensionDefaultingService::serviceReplaceAttributeValue(sourceDefDimension, dimAttrValueSet.RecId, dimAttr.RecId);

        //Combine the target default dimension, default ledger account to get the resultant ledger dimension
        targetDimension = DimensionDefaultingService::serviceCreateLedgerDimension(defaultLedgerAccount, targetDefDimension);

        //info(strFmt("Before: %1", DimensionAttributeValueCombination::find(sourceDimension).DisplayValue));
        //info(strFmt("After: %1", DimensionAttributeValueCombination::find(targetDimension).DisplayValue));
    }
    return targetDimension;
}

How to find or create default Dimension from X++ in AX 2012

This is static method to find or create default dimension:

static DimensionDefault createDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                               result;
    int                                                      i;                            
    DimensionAttribute                            dimensionAttribute;
    DimensionAttributeValue                   dimensionAttributeValue;
    //_attr is dimension name in table DimensionAttribute
    container               conAttr =   _attr;
    container               conValue = _value;
    str                     dimValue;

    for (i = 1; i <= conLen(conAttr); i++)
    {
        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

        if (dimensionAttribute.RecId == 0)
        {
            continue;
        }

        dimValue = conPeek(conValue,i);

        if (dimValue != "")
        {
            // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
            dimensionAttributeValue
                    dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

            // Add the dimensionAttibuteValue to the default dimension
            valueSetStorage.addItem(dimensionAttributeValue);
        }
    }
    result = valueSetStorage.save();
    return result;
}

How to add ledger account and offset account (LedgerDimension) into Form in AX 2012

1. In the AOT, locate the "YourTable" and create 2 new fields with the following properties (click on Yes to automatically add a foreign key relation once asked):

Name: Account, OffsetAccount
ExtendedDataType LedgerDimensionAccount

2.  Find the table's relation named DimensionAttributeValueCombination, and change
their property as follows:

UseDefaultRoleNames    No

3.  In the AOT, find the "YourForm", and add the following code to its class declaration:

public class FormRun extends ObjectRun
{
    LedgerDimensionAccountController   dimAccountController;
    LedgerDimensionAccountController   dimOffsetAccountController;
}

4.  Add the following code to the bottom of the form's init()method:
public void init()
{
    super();
    dimAccountController        = LedgerDimensionAccountController::construct(DICHRLedgerAccountSetup_ds, fieldStr(DICHRLedgerAccountSetup,Account));
    dimOffsetAccountController  = LedgerDimensionAccountController::construct(DICHRLedgerAccountSetup_ds, fieldStr(DICHRLedgerAccountSetup,Account));
}


5.  On the same form, created 2 segmented entry controls , set datasource and reference field to Account, OffsetAccount.
Override three of their methods with the following code for segmentedEntry Account control

Public void loadAutoCompleteData(loadAutoCompleteDataEventArgs _e)
{
    super(_e);
    dimAccountController.loadAutoCompleteData(_e);
}

public void loadSegments()
{
    super();
    dimAccountController.parmControl(this);
    dimAccountController.loadSegments();
}

public void segmentValueChanged(SegmentValueChangedEventArgs _e)
{
    super(_e);
    dimAccountController.segmentValueChanged(_e);
}
Do the same for segmentedEntry OffsetAccount control

6.  On the same form, in "YourTable "datasource, locate the
Account, OffsetAccount field and override three of their methods with the following code for each field:

public Common resolveReference(FormReferenceControl _formReferenceControl)
{
    return dimAccountController.resolveReference();
}

public void jumpRef()
{
    super();
    dimAccountController.jumpRef();
}

public boolean validate()
{
    boolean ret;
    ret = super();
    ret = dimAccountController.validate() && ret;
    return ret;
}

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

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
The following X++ code example uses ODBC to connect to an external database:

    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;
    // Set the information on the ODBC.
    loginProperty = new LoginProperty();    
    loginProperty.setDSN("BaoHuynh");
    
    loginProperty.setDatabase("DBName");
    
    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)

    {
        sql = "SELECT * FROM DBName WHERE FIELD = "
            + your criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

        //Assert permission for executing the sql string.

        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.

        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);

        //Cause the sql statement to run,

        //then loop through each row in the result.
        while (resultSet.next())
        {           
            //Always get fields in numerical order, such as 1 then 2 the 3 
            print resultSet.getString(1);
            print resultSet.getString(2);
            print resultSet.getString(3);
        }
        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");

    }

How to duplicate SSRS report with business logic in AX 2012

This is my own solution to duplicate SSRS report (with business logic or not) in AX 2012:

Ex : PurchPackingSlip report
- Choose duplicate  for report in SSRS and also the DP, Contract and Controller
After that you also got lot of error about business logic (BL) when you deploy

- Now go to AOT, export your Purch Packing Slip Business logic in  > AOT > Visual Studio Project > C Sharp Project > PurchPackingSlipReport.BusinessLogic.

- Open businesslogic file with Note Pad:



- Crtl + F : Find all the old BL name and rename to your BL name.
- Find all old report name and rename to your duplicated report name.
- Find Origin line like the second red rectangle in my pic > change a last number to make Id  for your BL. In this case 68 > 69
- Save and import to AOT > now it become your BL with new name.
- Go to VS: change data method library to your BL
- Add dataset with your RDP class (rename to old dataset name)
- Copy properties of old parameters to new parameters.
- Remove old parameters
- Set dataset report parameters to new parameters in properties Window:


Now deploy,  you got your duplicated report.