Friday, March 8, 2019

D365 table name from tableid

Open SQL MSSMS and run the following script with a sought number.


SELECT 
   [ID]
      ,[NAME]
      ,[RECVERSION]
      ,[RECID]
  FROM [AxDB].[dbo].[TABLEIDTABLE]
  where id = 1459

Wednesday, March 6, 2019

Extended version of Universal Field Changer for Microsoft Dynamics AX2012

Oh yeah! The Field changer is still on the road! Now equipped with two updating options:


Feel free to use this surgeon's tool at your own risk! Grab it from here.

Computed column for union values from multiple outer joined data sources in view

Let's say you have a multiple outer joined data sources with similar fields, which can be merged by union to one target field.

PSAActualEntity  data entity (in D365) can be a good example, if you add ProjCostTrans, ProjItemTrans, and ProjEmplTrans to its root data source table ProjTransPosting, which has appropriate relations to each of them.



All aforementioned have CategoryId field, which can be present in one table only at a time.


 Let's place a new computed string field with the following static method with a nested if.


private static server str transCategoryId()
    {
        str         sRet;
        tableName   viewName                = identifierStr(avrPSAActualEntity);
        str         cCategoryProjCostTrans  = SysComputedColumn::comparisonField(viewName,
                                                                                    identifierStr(ProjCostTrans),
                                                                                    fieldStr(ProjCostTrans, CategoryId));
        str         cCategoryProjItemTrans  = SysComputedColumn::comparisonField(viewName,
                                                                                    identifierStr(ProjItemTrans),
                                                                                    fieldStr(ProjItemTrans, CategoryId));
        str         cCategoryProjEmplTrans  = SysComputedColumn::comparisonField(viewName,
                                                                                    identifierStr(ProjEmplTrans),
                                                                                    fieldStr(ProjEmplTrans, CategoryId));
        str         sCategoryProjCostTrans  = SysComputedColumn::returnField(viewName,
                                                                                    identifierStr(ProjCostTrans),
                                                                                    fieldStr(ProjCostTrans, CategoryId));
        str         sCategoryProjItemTrans  = SysComputedColumn::returnField(viewName,
                                                                                    identifierStr(ProjItemTrans),
                                                                                    fieldStr(ProjItemTrans, CategoryId));
        str         sCategoryProjEmplTrans  = SysComputedColumn::returnField(viewName,
                                                                                    identifierStr(ProjEmplTrans),
                                                                                    fieldStr(ProjEmplTrans, CategoryId));
        sRet =
            SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cCategoryProjCostTrans),
                                    cCategoryProjCostTrans, 
                                    SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cCategoryProjItemTrans),
                                                            sCategoryProjItemTrans,
                                                            SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cCategoryProjEmplTrans),
                                                                                sCategoryProjEmplTrans,
                                                                                SysComputedColumn::returnLiteral('')
                                                                                )
                                                        )
                                );

        return sRet;
    }



Monday, February 25, 2019

TempDB table populated in CIL

We have multiple options to provide additional information to records in grids. Some of them, like display methods, are easy to implement. Some of them, like a temporary table, are not.

Let's consider a scenario when our lovely user wants to see reservation info directly in the transaction grid as filterable columns.




Evidently, we cannot use display methods because they cannot be filterable. Given the way this info calculated behind the curtain, there is no way to create a view to join InventTrans on InventTransItem form. So, the last feasible option is to create a temporary table and join it to the main form data source.

In order to populate this TempDB table (it is not enough to create an InMemory one) as much fast as possible, we have to run the logic in CIL.

The following shows how to work with such tables by passing their physical names.

First, create a TempDB table.



Then add it to the form data source so that it would be outer joined to InventTrans table



and place its Init() method, which links the local buffer with a populated TempDB on the SQL server side.

We also need to init its populating engine and calculate its data at least once when the form opens and maybe later via Refresh button for all or one particular item.

final class FormRun extends ObjectRun
{
    wblInventSumFormEngine              wblInventSumFormEngine;
}

void init()
{
    super();
    element.wblInitParameters();
}

private void wblInitParameters()
{
    wblInventSumFormEngine               = new  wblInventSumFormEngine(element);
}

//wblTmpInventSum_DS.Init
public void init()
{
    wblTmpInventSum                  wblTmpInventSumLoc;
    wblTmpInventSumLoc.doInsert();
    delete_from wblTmpInventSumLoc;
    super();
    wblTmpInventSum.linkPhysicalTableInstance(wblTmpInventSumLoc);
}

public void run()
{
    super();
    element.wblRecalcData();
}

public void wblRecalcData(boolean _forCurrentItemOnly = false)
{
    wblInvenTransByItemDimView  wblInvenTransByItemDimView;

    if(_forCurrentItemOnly)
    {
        wblInventSumFormEngine.parmItemIdCalcFor(InventTrans.ItemId);
        wblInventSumFormEngine.run();
    }
    else
    {
        wblInventSumFormEngine.parmItemIdCalcFor('');
        // todo: uncomment if needed to measure the execution time
        //wblInventSumFormEngine.parmShowCalcInfo(true);
        startLengthyOperation();
        wblInventSumFormEngine.run();
        endLengthyOperation();
    }
}

Now let's take a look at the populating class. It has parameters for showing elapsed time and run for a particular item id only.


/// <summary>
/// Populating TempDB tables in CIL
/// </summary>
/// <remarks>
/// Passing physical table names between CIL and form
/// </remarks>

public class wblInventSumFormEngine
{
 // add more data sources to support multiple TempDB tables populating
 FormDataSource                      fdsTmpInventSum;
 wblTmpInventSum                     wblTmpInventSum;
 ItemId                              itemIdCalcFor;
 boolean                             showCalcInfo;
}

/// <summary>
/// Gets TempDB tables physical names
/// </summary>
/// <returns>
/// Container with names
/// </returns>
/// <remarks>
/// Creates buffers if not exist yet
/// </remarks>
private container getTempTablesNames()
{
 if(!wblTmpInventSum.getPhysicalTableName())
 {
  select firstOnly wblTmpInventSum;
 }
 return [wblTmpInventSum.getPhysicalTableName()];
}

/// <summary>
/// Creates new populating engine for a given form
/// </summary>
/// <param name="_formRun">
/// Caller form
/// </param>
/// <remarks>
/// Looks for a particular data sources to link TempDB tables
/// </remarks>
public void new(FormRun _formRun = null)
{
 Object formObject = _formRun;

 if (_formRun)
 {
  fdsTmpInventSum      = _formRun.dataSource(tableId2Name(tableNum(wblTmpInventSum)));
 }
}

/// <summary>
/// To calculate TempDB table for a particular item only
/// </summary>
/// <param name="_parm">
/// Item id to calculate data
/// </param>
/// <returns>
/// Item id
/// </returns>
/// <remarks>
/// If empty, then calculate for all items
/// </remarks>
public ItemId parmItemIdCalcFor(ItemId _parm = itemIdCalcFor)
{
 itemIdCalcFor = _parm;
 return itemIdCalcFor;
}

/// <summary>
/// Show consumed time to populate TempDB tables
/// </summary>
/// <param name="_parm">
/// Shows if True
/// </param>
public boolean parmShowCalcInfo(boolean _parm = showCalcInfo)
{
 showCalcInfo = _parm;
 return showCalcInfo;
}

public void run()
{
 str                     wblTmpInventSumTempDBTableName;
 // add here more tempDB tables if needed
 [wblTmpInventSumTempDBTableName] = this.getTempTablesNames();
 [wblTmpInventSumTempDBTableName] = wblInventSumFormEngine::calc([itemIdCalcFor, wblTmpInventSumTempDBTableName, showCalcInfo]);
 // push calculated data back to the form data source
 if(fdsTmpInventSum)
 {
  // this assignment works only on the client tier outside of the populating method
  fdsTmpInventSum.cursor().linkPhysicalTableInstance(wblTmpInventSum);
  fdsTmpInventSum.research();
 }
}

/// <summary>
/// Launcher for CIL
/// </summary>
/// <param name="_cont">
/// Container with parameters and all TempDB tables physical names to populate
/// </param>
/// <returns>
/// Container with all populated TempDB tables physical names
/// </returns>
private static server container calc(container _cont)
{
 container               cont;
 XppILExecutePermission  xppILExecutePermission;
 FromTime                startTime               = timeNow();
 ItemId                  itemIdCalcFor           = conPeek(_cont, 1);
 boolean                 showCalcInfo            = conPeek(_cont, 3);

 xppILExecutePermission = new XppILExecutePermission();
 xppILExecutePermission.assert();

 cont = runClassMethodIL(
         classStr(wblInventSumFormEngine),
         staticMethodStr(wblInventSumFormEngine, calcIL),
         _cont
        );
 CodeAccessPermission::revertAssert();

 if(showCalcInfo)
 {
  info(strFmt("Refreshed for %1 in %2",  itemIdCalcFor ? itemIdCalcFor : "all items", timeConsumed(startTime, timeNow())));
 }

 return cont;
}

/// <summary>
/// Implements the calculating logic and populating TempDB tables
/// </summary>
/// <param name="_con">
/// Container with parameters and all TempDB tables physical names to populate
/// </param>
/// <returns>
/// Container with all populated TempDB tables physical names
private static server container calcIL(container _con)
{
 WHSInventReserve                    whsInventReserve;
 wblTmpInventSum                     wblTmpInventSum;
 ItemId                              itemIdCalcFor                   = conPeek(_con, 1);
 str                                 wblTmpInventSumTempDBTableName  = conPeek(_con, 2);
 wblInvenTransByItemDimView          wblInvenTransByItemDimView;
 // Link to an exiting table
 wblTmpInventSum.useExistingTempDBTable(wblTmpInventSumTempDBTableName);
 // empty existing data
 wblInventSumFormEngine::cleanTmpInventSum(wblTmpInventSum, itemIdCalcFor);
 /***** Implements the calculating logic here *****/
 while select whsInventReserve
  join RecId from wblInvenTransByItemDimView
  where
   (!itemIdCalcFor || whsInventReserve.ItemId == itemIdCalcFor) &&
   wblInvenTransByItemDimView.ItemId == whsInventReserve.ItemId &&
   wblInvenTransByItemDimView.InventDimId == whsInventReserve.InventDimId
 {
  wblTmpInventSum.clear();
  wblTmpInventSum.Itemid                  = whsInventReserve.itemId;
  wblTmpInventSum.InventDimId             = whsInventReserve.InventDimId;
  wblTmpInventSum.AvailOrdered            = whsInventReserve.AvailOrdered;
  wblTmpInventSum.AvailPhysical           = min(whsInventReserve.displayPhysAvailUpHierarchy(), whsInventReserve.AvailPhysical);
  // here you can add any other method calls to get more info about reservation
  wblTmpInventSum.AvailReservDelta        = wblTmpInventSum.AvailPhysical - wblTmpInventSum.AvailOrdered;
  wblTmpInventSum.insert();
 }
 // send tables physical names back to the form
 return [wblTmpInventSum.getPhysicalTableName()];
}

/// <summary>
/// Empties the whole table or for given item id only
/// </summary>
/// <param name="_wblTmpInventSum">
/// TempDB table buffer to empty
/// </param>
/// <param name="_itemIdCalcFor">
/// Item id
/// </param>
static private void cleanTmpInventSum(wblTmpInventSum _wblTmpInventSum, ItemId _itemIdCalcFor = '')
{
 delete_from _wblTmpInventSum
 where
  (!_itemIdCalcFor || _wblTmpInventSum.ItemId == _itemIdCalcFor);
}

As you can see the tricky point is to provide tables physical names to CIL and back in calc() and calcIL() methods.

Now you get it.


Do not forget to build CIL and check it in your Development user options.


Wednesday, January 30, 2019

View computed column to get OR for two flags

Nothing new, just to reiterate the basics.

Say, we need to add a computed column as a result of OR function over two other columns of Boolean type: wblPPOFirmDisableForSalesLine and wblPPOFirmDisableForTransferLine.



Create a new field wblPPOFirmDisable as Enum Computed field


and point it to the following method.



private static server str wblPPOFirmDisable(int _tableNum)
{
    DictView    ctView      = new DictView(tableNum(wblPeggedRefTransOnHoldUnionView));
    str         sTransType1 = SysComputedColumn::comparisonField(identifierStr(wblPeggedRefTransOnHoldUnionView), identifierStr(wblSalesTableOnHoldView),  fieldStr(wblSalesTableOnHoldView, wblPPOFirmDisableForSalesLine));
    str         sTransType2 = SysComputedColumn::comparisonField(identifierStr(wblPeggedRefTransOnHoldUnionView), identifierStr(wblSalesTableOnHoldView),  fieldStr(wblSalesTableOnHoldView, wblPPOFirmDisableForTransferLine));
    str     sRet        = '';

    sRet =
            SysComputedColumn::or2
            (
                SysComputedColumn::equalExpression(sTransType1, SysComputedColumn::returnLiteral(NoYes::Yes)),
                SysComputedColumn::equalExpression(sTransType2, SysComputedColumn::returnLiteral(NoYes::Yes))
            );
    sRet =
        SysComputedColumn::if(sRet, SysComputedColumn::returnLiteral(NoYes::Yes), SysComputedColumn::returnLiteral(NoYes::No));

    return sRet;
}

The whole point is to create a string with an SQL statement as a result in static method. There are a lot of standard functions in SysComputedColumn class aiming to help with syntax.

As you can see in this example it is not enough to get a logical comparison result at the first sRet assigning. The latter must be converted explicitly to an SQL string as it goes at the second step.

Finally we get the following string as a part of the whole SQL statement.

CASE WHEN (T1.WBLPPOFIRMDISABLEFORSALESLINE = 1) OR (T1.WBLPPOFIRMDISABLEFORTRANSFERLINE = 1) THEN 1 ELSE 0 END

Wednesday, January 23, 2019

bugs in SysLookupMultiSelectGrid class and form

You can find a lot of example about how to support multiple selection lookups. AX 2012/D365 provide us with SysLookupMultiSelectGrid class and form to implement such scenarios.

There are two bugs however still existing in the standard code.

SysLookupMultiSelectGrid class, method lookup() must be as follows in order to refresh Query in case QueryRun is given.


// the standard method does not update the query in case if queryrun is given
public static void wblLookup(Query _query, FormStringControl _ctrlIds, FormStringControl _ctrlStrs, container _selectField, queryRun _queryRun = null)
{
    SysLookupMultiSelectGrid    lookupMS = new SysLookupMultiSelectGrid();

    lookupMS.parmCallingControlId(_ctrlIds);
    lookupMS.parmCallingControlStr(_ctrlStrs);
    lookupMS.parmQuery(_query);
    lookupMS.parmQueryRun(_queryRun);
    if(_queryRun)
    // Begin: Alexey Voytsekhovskiy
    {
        lookupMS.parmQuery(_queryRun.query());
    }
    // End: Alexey Voytsekhovskiy
    lookupMS.parmSelectField(_selectField);
    lookupMS.run();
}

SysLookupMultiSelectGrid form, method executeQuery() on common data source must be as follows in order not to consider referenced data sources, which may come with a given query.


public void executeQuery()
{
    QueryRun qr;
    Query lookupMultiSelectQueryCopy = new Query(lookupMS.parmQuery());
    FormRun formRun;
    FormDataSource formDataSource;
    int dsCount, i = 1;
    Common formDataSourceCursor, queryRunCursor;

    // Always use the query defined on the SysLookupMultiSelectGrid class. Note that a copy is used
    // so that any modifications made to the Query by the Form at runtime aren't fed back through
    // the next time the lookup is presented to the user. (The Query is used to define which fields
    // are present on the Form during lookup construction. Therefore, if any fields are added at runtime
    // by the Forms engine, duplicated or non-original-Query defined fields may be presented on the
    // 2nd or later presentations of the lookup if a copy isn't used.)
    this.query(lookupMultiSelectQueryCopy);

    // check if user has set any queryrun. If yes, that means the cursors are set by user explicitly,
    // usually the case where query contains tmp tables and needs to be populated.
    qr = lookupMS.parmQueryRun();
    if(qr)
    {
        formRun = this.formRun();
        dsCount = formRun.dataSourceCount();

        // get data source from query run, get the cursor and set it on the form data source cursor.
        for(i = 1; i<=dsCount; i++)
        {
            formDataSource = formRun.dataSource(i);
            if(formDataSource
                            // Begin: Alexey Voytsekhovskiy
                            // we don't need a reference data source here!
                            && !formDataSource.isReferenceDataSource()
                            // End: Alexey Voytsekhovskiy
                            )
            {
                // get form data source cursor and set the queryrun cursor as data on it.
                formDataSourceCursor = formDataSource.cursor();
                queryRunCursor = qr.get(formDataSourceCursor.TableId);
                if(queryRunCursor)
                {
                    if(queryRunCursor.isTempDb() || queryRunCursor.isTmp())
                    {
                        formDataSourceCursor.setTmpData(queryRunCursor);
                    }
                    else
                    {
                        formDataSourceCursor.data(queryRunCursor);
                    }

                }
            }
        }
    }

    super();
}

Saturday, January 12, 2019

Date time range

Just for my records

 (dayRange(-5, 0))

 Taken from here

Thank you Martin!