AX 2012 - Last cost price

You can put method to InventTable or any table which has ItemId field:

public CostPrice SNBfindLastPrice(DatePhysical _date = systemDateGet(),InventDim _inventdim = null)
{

    InventTrans           inventTrans;
    InventTransOrigin     origin;
    InventDim             inventdim;
    LineAmount            purchprice;
    RecId                 OK = false;
    InventDimParm         dimParm;
    InventDim             dimJoin;


    dimparm.initFromInventDim(_inventdim);

    select firstOnly CostAmountAdjustment,CostAmountPosted,Qty from inventTrans
                order by inventTrans.DateClosed desc,inventTrans.DateFinancial desc
            where inventTrans.ItemId == this.ItemId &&
                  !inventTrans.ReturnInventTransOrigin &&
                 inventTrans.DateClosed != dateNull() &&
                 inventTrans.DateClosed <= _date
        exists join origin
            where origin.RecId == inventTrans.InventTransOrigin &&
                  origin.ReferenceCategory == InventTransType::SummedUp
        #InventDimExistsJoin(inventTrans.InventDimId,dimJoin,_inventdim,dimParm);
    OK = inventTrans.RecId;

    if (!OK)
    {
        select firstOnly CostAmountAdjustment,CostAmountPosted,Qty from inventTrans
                    order by inventTrans.DateFinancial desc
                where inventTrans.ItemId == this.ItemId &&
                      !inventTrans.ReturnInventTransOrigin &&
                      inventTrans.Qty > 0 &&
                      inventTrans.DateFinancial <= _date
            exists join origin
                where origin.RecId == inventTrans.InventTransOrigin &&
                      origin.ReferenceCategory == InventTransType::Purch
            #InventDimExistsJoin(inventTrans.InventDimId,dimJoin,_inventdim,dimParm);
        OK = inventTrans.RecId;
    }
    if (!OK)
    {
        select firstOnly CostAmountAdjustment,CostAmountPosted,Qty from inventTrans
                    order by inventTrans.DateFinancial desc
                where inventTrans.ItemId == this.ItemId &&
                      inventTrans.Qty > 0 &&
                      inventTrans.DateFinancial <= _date
            exists join origin
                where origin.RecId == inventTrans.InventTransOrigin &&
                      origin.ReferenceCategory == InventTransType::SummedUp
            #InventDimExistsJoin(inventTrans.InventDimId,dimJoin,_inventdim,dimParm);
        OK = inventTrans.RecId;
    }
    if (!OK)
    {
        select firstOnly CostAmountAdjustment,CostAmountPosted,Qty from inventTrans
                    order by inventTrans.DateFinancial desc
                where inventTrans.ItemId == this.ItemId &&
                      inventTrans.Qty > 0 &&
                      inventTrans.DateFinancial <= _date
            exists join origin
                where origin.RecId == inventTrans.InventTransOrigin &&
                      ( origin.ReferenceCategory == InventTransType::InventTransaction ||
                        origin.ReferenceCategory == InventTransType::BOMMain)
            #InventDimExistsJoin(inventTrans.InventDimId,dimJoin,_inventdim,dimParm);
    }
    if (inventTrans.Qty != 0)
        return  abs( (inventTrans.CostAmountAdjustment+inventTrans.CostAmountPosted ) / inventTrans.Qty );
    else
        return 0;
}

AX 2012 - Multiselect Lookup

There is a class and a sample form (tutorial_LookupMultiSelectGrid) at AOT for generate multi select lookup:

public class FormRun extends ObjectRun
{
    SysLookupMultiSelectCtrl msCtrl;
}


public void init()
{
   ...  
    super();
    ...
    
    msCtrl = SysLookupMultiSelectCtrl::construct(element, InventLocationIdExc, queryStr(InventLocationSRS));
    msCtrl.set(this.InventLocationList());
    ...
}

We can get list like below and set like up in init.

container InventLocationList()
{
    Container           Ids,Names;
    InventLocation      location;
    int i;
    RecId               recId;

    for (i=1;i<=conLen(vInventLocationIdExc);i++)
    {
        recId = conPeek(vInventLocationIdExc,i);
        select firstOnly location
            where location.RecId == recId;
        Ids += location.RecId;
        Names += location.InventLocationId;
    }

    return [Ids,Names];
}

We can save like this at Pack method:

container pack()
{
    ...
    vInventLocationIdExc    = msCtrl.get();
    ...
}

While MsCtrl.get() gives us RecId values below gives us field values:

container               c = msCtrl.getSelectedFieldValues();

AX 2012 - Add InventDim to Form Grid

Add InventDim table to data source, than make InnerJoin with table which inventDimId field used (in this sample table name is SNBProductDemonte).

Add a Group control like this at the grid:




Add this code to 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)
{
   InventDimParm inventDimParmVisible;

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

                // Use the methods on InventDimParm
                // to set which dimensions to show when form is initialized
         //       inventdimparmvisible.inventsiteidflag       = true;
           //     inventdimparmvisible.InventLocationIdFlag   = true;
                inventDimFormSetup.parmDimParmVisibleGrid(inventDimParmVisible);

        // Datasource Active
        case InventDimFormDesignUpdate::Active  :
            inventDimFormSetup.formActiveSetup(InventDimGroupSetup::newItemId(SNBProductDemonte.ItemId)); 
            inventDimFormSetup.formSetControls( true);
            break;

        // Datasource Field change
        case InventDimFormDesignUpdate::FieldChange :
            inventDimFormSetup.formActiveSetup(InventDimGroupSetup::newItemId(SNBProductDemonte.ItemId)); 
            InventDim.clearNotSelectedDim(inventDimFormSetup.parmDimParmEnabled()); // InventDim is referring to datasource name
            inventDimFormSetup.formSetControls( true);
            break;

        default :
            throw error(strFmt ("@SYS54195", funcName()));
    }
}

Add this code to table which InventDimId field:

public boolean validateWrite()
{
    boolean ret;

    SNBProductDemonte.InventDimId = InventDim::findOrCreate(InventDim).InventDimId;
    ret = super();
  
    return ret;
}

public int active()
{
    int ret;
    ret = super();

    element.updateDesign(InventDimFormDesignUpdate::Active);
    return ret;
}

Add this code to modified method of ItemId field:

public void modified()
{
    super();

    element.updateDesign(InventDimFormDesignUpdate::FieldChange);
    InventDim.clearNotSelectedDim(element.inventDimSetupObject().parmDimParmEnabled());
}


Add InventDimParmFixed Display menu item from AOT to form.

Source:
https://daxbeginners.wordpress.com/2014/08/05/how-to-dynamically-display-inventory-dimension/

Axapta - Don't Miss One Second When filtering Between Two DateTime Fields

If we take time 00:00 of day after last day and use < instead of <= we don't miss even one second:

fromDate = DateTimeUtil::newDateTime(FromDateEdit.dateValue(),0,DateTimeUtil::getCompanyTimeZone());
    toDate   = DateTimeUtil::newDateTime(ToDateEdit.dateValue()+1,0,DateTimeUtil::getCompanyTimeZone());

    while select count(RecId) from serviceOrderTable
                where serviceOrderTable.createdDateTime >= fromDate
                    && serviceOrderTable.createdDateTime < toDate

AX 2012 - SSRS Report Carry Forward and Page Total

Unfortunately adding carry forward and page total are a bit tricky with SSRS. I used up a blog (Peter ?) for carry forward and another blog (Annette Theißen) for page total.

Name our field for use total AccountingCurrencyAmountDebit.

Add a textbox for carry forward named dCarry, and value like below:

=RunningValue(Fields!AccountingCurrencyAmountDebit.Value,SUM,"LedgerTransListAccountDS")

or if we will group by account num:


=RunningValue(Fields!AccountingCurrencyAmountDebit.Value,SUM,"AccountNum_0")


Carry previous named dCarryH and like this:

=RunningValue(Fields!AccountingCurrencyAmountDebit.Value,SUM,"AccountNum_0") - Fields!AccountingCurrencyAmountDebit.Value

Put visibility to no both of them. Even they hidden values will be calculated.

For show carry previous value add a textbox to Page Header,  it's value should be like:

=First(ReportItems!dCarryH.Value)

Visibility have to bound a condition (So there won't be carry previous at first page):

=Globals!PageNumber=1

Add a textbox for carry forward to Page Footer and put value like this:

=last(ReportItems!dCarry.Value)

It's Visibility bound a condition too (So there won't be carry forward at last page):

=Globals!PageNumber=Globals!TotalPages

We need two data methods and some variables for page total. For add Data method do right mouse - Add Data Method at treeview's Data Methods node under Designs. Than double click for open code window. Our code is red lined:

using System;
using System.Collections.Generic;
using System.Security.Permissions;
using System.Data;
using Microsoft.Dynamics.Framework.Reports;
using Microsoft.Dynamics.AX.Application.Reports;
public partial class LedgerTransListAccount
{
    static double previousTotal;
    static double pageTotal;
    static bool NewAccount;
    static string lastAccount;

     ...
   
 [DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
    public static double PageTotal(double total)
    {
         if (NewAccount)
            return total;
         else
            return total - previousTotal;
    }

    [DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
    public static double PreviousTotal(double total)
    {
        previousTotal = total;

        return 0;
    }
[DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
    public static string Reset()
    {
        NewAccount = false;
        lastAccount = "";

        return "";
    }

    [DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
    public static string FollowAccount(string account)
    {
        if (lastAccount != "" && lastAccount != account)
            NewAccount = true;
        lastAccount = account;

        return account;

    }
      
}

Add a textbox at Page Footer:

=PreviousTotal(First(ReportItems!dCarryH.Value))

Unfortunately if we make visible = false it doesn't calculate. Instead of make visible false we make display format as number and change Show zero as combobox value with blank (Not Nonel!!).

Add a textbox for page total:

=PageTotal(last(ReportItems!dCarry.Value))

If you ask why we used an extra textbox; unfortunately SSRS doesn't accept two field for expression parameters.

Add a textbox to page header:

=Reset()

AX 2012 - BOM explode

I used Christian Silva's code for this. He doesn't consider about invent color/invent size and unit conversions. It's a trouble when a BOM has half-product necessaries convert from gr to kg. I included unit conversion and select BOM with color/invent size:

class SNBBOMExplode
{
    SNBBOMExplodeTmp    tmpBOM;
    SNBBOMExplodeData   bomdata;
    ItemId              itemId;
    EcoResItemSizeName  inventSizeId;
    EcoResItemColorName inventColorId;
    InventSiteId        inventSiteId;
}

boolean hasChild(ItemId _itemId)
{
    BOMVersion  bomVersion;
    ;

    //Check if the item is also a BOM item.
    select firstonly bomVersion
            where bomVersion.ItemId == _itemid
            && bomVersion.Active
            && bomVersion.FromDate <= systemdateget ()
            && (!bomVersion.ToDate || bomVersion.ToDate >= systemdateget ());

    if (bomVersion.RecId)
        return true ;

    return false ;
}

private int InsertParentItem(ItemId _itemId, int _level)
{
    InventTable inventTable;
    ;

    //Gets the parent information and then insert on TmpBOMExplode Table with level 0.
    select firstOnly inventTable where inventTable.ItemId == _itemId;

    tmpBOM.ItemId = _itemId;
    tmpBOM.Level = _level;
    tmpBOM.BOMQty = 1 ;
    tmpBOM.HasChild = this.hasChild(_ItemId);
    tmpBOM.insert();

    return _level+ 1 ;
}

void itemExplode(ItemId _ItemId, int _level = 0, BOMQty _bomQty = 1)
{
    BOM         bomTable;
    InventTable inventTable;
    BOMVersion  bomVersion;
    InventDim   inventDim;
    BOMQty      qtyS;

    ;

    //Insert parent Item
    if (_level == 0)
        _level = this.InsertParentItem(_ItemId, _level);

    //Verifies if the Item exists in BOMVersion Table.
    //The item must be active and not expired.
    select firstonly bomVersion
            where bomVersion.ItemId == _itemid
            && bomVersion.Active
        exists join inventDim
            where inventDim.inventDimId == bomVersion.InventDimId &&
                  (inventDim.InventColorId == inventColorId || inventColorId == "") &&
                  (inventDim.InventSizeId == inventSizeId || inventSizeId == "");
        //    && bomVersion.FromDate <= systemdateget()
        //    && (!bomVersion.ToDate || bomVersion.ToDate >= systemdateget());

    if (bomVersion.RecId)
    {
        //Every item on BOMVersion has a BOMId which is used to show
        //which products belong to a BOM Item.
        While select bomTable
            where bomTable.BOMId == bomVersion.BOMId
            join inventTable
            where bomTable.ItemId == inventTable.ItemId
        {
            //Insert the items that compose the BOM Item with level 1.
            tmpBOM.ItemId    = bomTable.ItemId;
            tmpBOM.RefItemId = bomVersion.ItemId;
            tmpBOM.BOMQty    = bomTable.BOMQty / bomTable.BOMQtySerie * _bomQty;
            tmpBOM.Level     = _level;
            tmpBOM.UnitId    = bomTable.UnitId;
            tmpBOM.HasChild  = this.hasChild(bomTable.ItemId);
            tmpBOM.MainItemId = this.parmItemId();
            tmpBOM.insert();

            //This method is used to check if the BOM Item is composed by
            //another BOM Item, case true it will call the method recursively
            // with level 2.
            if (tmpbom.HasChild == NoYes::Yes)//--- unit conversion ---
            {

                qtyS =  UnitOfMeasureConverter::convert(
                            tmpBOM.BOMQty,
                            UnitOfMeasure::unitOfMeasureIdBySymbol(tmpBOM.UnitId),
                            UnitOfMeasure::unitOfMeasureIdBySymbol(bomTable.inventTable().inventUnitId()),
                            NoYes::Yes,
                            InventTable::itemProduct(tmpBOM.ItemId));

                this.itemExplode(bomTable.ItemId, _level+ 1, qtyS);
            }
        }
    }
}

void linkTables(SNBBOMExplodeTmp _tmp)
{
   tmpBOM.linkPhysicalTableInstance(_tmp);
}

EcoResItemColorName parmInventColorId(EcoResItemColorName _inventColorId = inventColorId)

{

    inventColorId = _inventColorId;

    return inventColorId;

}

EcoResItemSizeName parmInventSizeId(EcoResItemSizeName  _inventSizeId = inventSizeId)

{

    inventSizeId = _inventSizeId;

    return inventSizeId;

}

ItemId parmItemId(ItemId _itemId = ItemId)

{

    ItemId = _itemId;

    return ItemId;

}

SNBBOMExplodeTmp  parmSNBBOMExplodeTmp()

{

    select * from tmpBOM;

    return TmpBOM;

}

void run()

{

    delete_from tmpBOM;

    this.itemExplode(ItemId,0);

}

This's structure of tempDB table named SNBBOMExplodeTmp which I used:

BOMQty     BOMQty

HasChild   NoYesId

ItemId     ItemId

Level      BOMLevel

MainItemId ItemId

RefItemId  ItemId

UnitId     UnitOfMeasureSymbol

Sample usage:

         SNBBOMExplodeTmp    tmpBOM;

    SNBBOMExplode       bomExp = new SNBBOMExplode();

    

    bomExp.parmItemId(list.ItemId);

    bomExp.linkTables(tmpBOM);

    bomExp.parmInventColorId(list.InventColorId);

    bomExp.parmInventSizeId(list.InventSizeId);

    bomExp.run();

AX 2012 - Order by a Financial Dimension

Financial dimensions with AX 2012 collected in RecId fields and that RecId referes a couple of tables so it's a bit complicated. Fortunately there is a class for make it easy:

DimensionProvider           dimProvider = new DimensionProvider();
    dimProvider.addOrderByAttribute(BorAXJournalTrans_DS.query(),BorAXJournalTrans_DS.query().dataSourceNo(1).name(),
       FieldStr(BorAXJournalTrans,DefaultDimension),
        DimensionComponent::DimensionAttribute,SortOrder::Ascending,
"Department");
BorAXJournalTrans_DS.executeQuery();
  
If you don't want to use that class alternate is:

RecId  depRecId = 5637152827; //RecId value of department dimension at DimensionAttribute table
    BorAXJournalTrans   boraxJournalTrans;
    DimensionAttributeValueSet dimensionAttributeValueSet;
    DimensionAttributeValueSetItem dimensionAttributeValueSetItem;
    DimensionAttributeValue dimensionAttributeValue;
    DimensionAttribute dimensionAttribute;

while SELECT firstOnly10 borAXJournalTrans 
    ORDER BY DimensionAttributeValueSetItem.DisplayValue
 JOIN dimensionAttributeValueSet 
    where borAXJournalTrans.DefaultDimension == dimensionAttributeValueSet.RecId 
 JOIN dimensionAttributeValueSetItem 
    where dimensionAttributeValueSet.RecId == dimensionAttributeValueSetItem.DimensionAttributeValueSet 
 JOIN dimensionAttributeValue 
    where dimensionAttributeValueSetItem.DimensionAttributeValue == dimensionAttributeValue.RecId 
 JOIN dimensionAttribute 
    where dimensionAttributeValue.DimensionAttribute == dimensionAttribute.RecId && 
     dimensionAttribute.RecId == depRecId
    {
        info(dimensionAttributeValueSetItem.DisplayValue);
    }