Tuesday, March 26, 2019

How to get number of rows loaded in a grid

In fact we can use numberOfRowsLoaded on a linked form data source; however, it shows the number of those cached only.

So, if you are editing row number 28, for example, and then call ExecuteQuery() on the data source, you will probably get a lower number of rows loaded in the grid yet, say, 19.

In order to get the right number, you should use the aforementioned method together with allRowsLoaded() one.

public int wblNumberOfRowsLoaded()
{
    int wblNumOfRec;
    int wblCounter;

    wblNumOfRec = PSAActivityEstimates_DS.numberOfRowsLoaded();
    // nothing to load yet
    if(!wblNumOfRec)
    {
        return wblNumOfRec;
    }
    // so far we numbered those lines cached only
    while(!PSAActivityEstimates_DS.allRowsLoaded())
    {
        // so let's move it on until the end of loading
        PSAActivityEstimates_DS.setPosition(wblNumOfRec);
        PSAActivityEstimates_DS.getNext();
        // just to avoid never ending adventure
        if(wblCounter>1000)
        {
            break;
        }
        wblCounter++;
        // get the number for the next iteration
        wblNumOfRec = PSAActivityEstimates_DS.numberOfRowsLoaded();
    }
    // now all numberOfRowsLoaded() calls will return the correct number of rows in grids
    wblNumOfRec = PSAActivityEstimates_DS.numberOfRowsLoaded();

    return wblNumOfRec;
}

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