15 Kasım 2017 Çarşamba

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();

14 Kasım 2017 Salı

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 this code to form:

public class FormRun extends ObjectRun
{
    InventDimCtrl_Frm_EditDimensions        inventDimFormSetup;
}

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)); //InventDimDisplay is the datasource name.
            inventDimFormSetup.formSetControls( true);
            break;

        // Datasource Field change
        case InventDimFormDesignUpdate::FieldChange :
            inventDimFormSetup.formActiveSetup(InventDimGroupSetup::newItemId(SNBProductDemonte.ItemId)); //InventDimDisplay is the datasource name.
            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://community.dynamics.com/ax/b/daxbeginners/archive/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

10 Kasım 2017 Cuma

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()

8 Kasım 2017 Çarşamba

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();

13 Ekim 2017 Cuma

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);
    }

9 Ağustos 2017 Çarşamba

AX 2012 - Add filter for one dimension for a query's financial dimension

If you try to this with add tables you'll see you have to add lots of table to query. There is a class which do this for you DimensionProvider with AX 2012:

DimensionProvider           dimProvider = new DimensionProvider();


        dimProvider.addAttributeRangeToQuery(element.query(),element.query().dataSourceNo(1).name(),
       FieldStr(MyTable,DefaultDimension),
        DimensionComponent::DimensionAttribute,
        OMOperatingUnit::find(depRecId,OMOperatingUnitType::OMDepartment).OMOperatingUnitNumber,
            "Department",true);

BTW if you get an error like Unknown type: RefRecId it would be about EDT of your table. In my case they used RefRecId EDT instead of DimensionDefault EDT with DefaultDimension field. In your case it would be other than RefRecId. There is a bug like this with original AX table HcmEmployment.

8 Ağustos 2017 Salı

AX 2012 - Check if period closed

Ledger:

FiscalCalendars::checkModuleIsOpen(SysModule::Ledger, myTable.TransDate,FiscalCalendars::findPeriodByPeriodCodeDate(Ledger::fiscalCalendar(CompanyInfo::find().RecId),myTable.TransDate),false)

or

select firstOnly period
        where transDate >= period.StartDate  && transDate <= period.EndDate
            exists join ledgerPeriod
                where ledgerPeriod.FiscalCalendarPeriod == period.RecId &&
                      ledgerPeriod.Status != FiscalPeriodStatus::Open;
    if (period.RecId != 0)
        throw error("Fiscal period is closed!..");

Sales:

FiscalCalendars::checkModuleIsOpen(SysModule::Sales, CustInvoiceJour.InvoiceDate,FiscalCalendars::findPeriodByPeriodCodeDate(Ledger::fiscalCalendar(CompanyInfo::find().RecId),CustInvoiceJour.InvoiceDate),false)

Invent closing:

InventClosing::findClosingDate(endmth(InventJournalTrans.TransDate))

2 Ağustos 2017 Çarşamba

AX 2012 - Compile Error about Security Development Tool

Security Development Tool  is a great tool for security issues. But sometimes it would be a trouble. If you get this error you have to know reason is that tool:

Error executing code: SysSecurityRecorder_**** object does not have method 'MenuItemInvoked'.

I just found a blog which is written in french and I translated into english with Google translate (Unfortunately Google translate is terrible with turkish translates). There are two methods advised there succeeded with me:

Way #1: Delete AOD files per client computer.

Way #2: Just run this code at AX DB:

UPDATE SYSSQMSETTINGS SET GLOBALGUID = '{00000000-0000-0000-0000-000000000000}'

7 Temmuz 2017 Cuma

AX 2012 - Send data from server class to form with TempDB

If you try to send data from server class to form with TempDB by parameter you'll see no data will be came. You may select In Memory table or use linkPhysicalTableInstance method to link two TempDB table:

Class:

public static server void populateData(MyTempTable _tmp)
{
MyTempTable tmp;
...
...
tmp.linkPhysicalTableInstance(_tmp);
...
...
return;
}

Form:


MyClass::populateData(MyTempTable);
MyTempTable_DS.research();
MyTempTable_DS.refresh();

4 Ocak 2017 Çarşamba

AXAPTA - Synchronize tables

That code works on common situation my colleauge found from web (don't know the source):

    Dictionary              dict;
    int                     idx, lastIdx, totalTables;
    TableId                 tableId;
    Application             application;
    SysOperationProgress    progress;
    StackBase               errorStack;
    ErrorTxt                errorTxt;
    ;

    application = new Application();
    dict = new Dictionary();
    totalTables = dict.tableCnt();
    progress = new SysOperationProgress();
    progress.setTotal(totalTables);
    progress.setCaption("@SYS90206");
    errorStack = new StackBase(Types::String);

    lastIdx = 3000;
    try
    {
        for (idx = lastIdx+1; idx <= totalTables; idx++)
        {
            tableId = dict.tableCnt2Id(idx);
            progress.setText(dict.tableName(tableId));

            lastIdx = idx;
            application.dbSynchronize(tableId, false, true, false);
            progress.incCount();
        }
    }
    catch (Exception::Error)
    {
        errorTxt = strFmt("Error on table: '%1' (%2) ", tableId, dict.tableName(tableId));
        errorStack.push(errorTxt);
        retry;
    }

    setPrefix("@SYS86407");
    errorTxt = errorStack.pop();
    while (errorTxt)
    {
        error(errorTxt);
        errorTxt = errorStack.pop();
    }