Tuesday, March 31, 2015

Computed columns in Views - Dynamics AX 2012

Computed columns have been using in SQL server since many versions but this feature was available Dynamics AX since version 2012.

Follow steps described here to learn how to create view and walthrough the complete example to understand how can we add computed columns in AX 2012 Views.

More examples can be found from here;


Well, here is an example which illustrates how can we add computed columns in a view with different logic.

Computed column; Returning Field in View
public static server str compAmount() { #define.CompView(SWProjForecastCost) #define.CompDS(ProjForecastCost) #define.CostPrice(CostPrice)
   return SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #CostPrice)); }

Computed column; Converting UTC Date to Date in View
public static server str compDate() { #define.CompView(SWProjForecastCost) #define.CompDS(ProjForecastCost) #define.ActualDateTime(ActualDateTime)
str sDateTime;
sDateTime = SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #ActualDateTime));
   return SysComputedColumn::fromUtcDateToDate(sDateTime); }

Computed column; Returning Enum Value in View
public static server str compGeneralTransType() {    return SysComputedColumn::returnLiteral(Transaction::ProjectInvoice); }

Computed column; Multiplying two coulmns and returning resultant from View
private static server str compTotalCostPrice()
{
   #define.CompView(SWProjForecastCost)
   #define.CompDS(ProjForecastCost)
   #define.QtyCol(Qty)
   #define.PriceCol(CostPrice)


   return SysComputedColumn::multiply(
           SysComputedColumn::returnField(
               tableStr(#CompView),
               identifierStr(#CompDS),
               fieldStr(#CompDS, #PriceCol)
           ),
           SysComputedColumn::returnField(
               tableStr(#CompView),
               identifierStr(#CompDS),
               fieldStr(#CompDS, #QtyCol)
           )
         );
}


Computed column; Case Statement in View
public static server str TransType()
{
   #define.CompDS(ProjForecastCost)
   #define.CompView(SWProjForecastCost)
   str ret;
   str ModelId = SysComputedColumn::returnField(identifierStr(SWProjForecastCost), identifierStr(ProjForecastCost), identifierStr(ModelId));
   ret = "case " + modelId +          " when 'Sales' then 'Forecast Sales' " +          " when 'Orders' then 'Forecast Orders' " +          " when 'Latest' then 'Forecast Latest' " +          " end";    return ret;
}


Case Statement for this view looks like in SQL server as below;

   CASE T2.MODELID
      WHEN 'Sales' THEN 'Forecast Sales'
      WHEN 'Orders' THEN 'Forecast Orders'
      WHEN 'Latest' THEN 'Forecast Latest' END


Till next post Happy Daxing :)

Tuesday, March 17, 2015

Retrieving/Picking files from Directory through X++

Following piece of code to pick all files from a directory.


static void readFromDirectory(Args _args)
{
    #Evat_NL
    #File
   
    // variable declration to retrieve files from folder
    Filename            baseFolder;
    Filename            pdfFilename;
    Filename            foundBaseFileName;
    Filename            foundFileName;
   
    container           mainFolder, subFolder, fileContainer;
    boolean             filesFoundMainFolder = true;
    boolean             filesFoundSubFolder = true;
   
    int                 apiResult;   
    int                 startTime, endTime, fileCounter;
   
    RecordInsertList    recordList = null;

    int setCurrentFolder (Filename _filename = '')
    {
        ;
        return WinAPI::setCurrentDirectory(_filename);
    }
    ;  
   
    baseFolder = @"C:\Test\invoices\"; // Reading all pdf invoices from a directory

    apiResult           = setCurrentFolder(SysTreeNode::duplicatePathDelimiters(baseFolder));
    mainFolder          = WinAPI::findFirstFile("*.pdf*");
    foundBaseFileName   = conpeek(mainFolder, 2);   

    startTime = WinAPI::getTickCount();

    while (filesFoundMainFolder)
    {
        if (foundBaseFileName != #currentFolder && 
            foundBaseFileName != #upFolder && 
            foundBaseFileName != '')
        {
            // Concatenating each file with base folder to get full path
            // For example; C:\Test\invoices\Invoice1.pdf
            pdfFilename = baseFolder + foundBaseFileName;

            fileCounter++;
        }
        apiResult            = setCurrentFolder(SysTreeNode::duplicatePathDelimiters(baseFolder));
        foundBaseFileName    = WinAPI::findNextFile(conpeek(mainFolder, 1));
        filesFoundMainFolder = foundBaseFileName ? true : false;
    }

    endTime = WinAPI::getTickCount();

    info(strFmt('It took %1 minutes to read %2 pdf files', ((endTime - startTime)/1000)/60, fileCounter));
}

Friday, March 13, 2015

Tool to copy security permissions in AX

Copying security roles, groups, user options and associating worker against user

The role based security works very well in AX 2012 and security development tool has also made the life of AX system administrator very charming. It was a nightmare to define security for each user in earlier versions of MS Dynamics AX. 

However, there is one area which security development tool does not cover and is often required in many occassions. That is "To Copy user roles from one user to another". Let's say we have created/imported a new user in AX and System Administrator requires to assign same role(s), existing users have, to this new added user. This requirement might come due to identical nature of work or responsibilities shared among different users. 

This tool will help you to copy users roles, users options, user groups, user's main accounts and user association with worker by creating new worker if not exists. User association with worker is required to perform different business processes in AX, one of them is purchase requisition approval.

You can download this tool from CodePlex: https://copysecuritypermissionsax.codeplex.com/

Please feel free to make suggestions and improvements.