Date Lookup in a String Control

Sometimes, you may need to lookup the date calendar in a string control. This was easily possible by overriding the lookup function and call the below method in the AX older versions.

public void lookup()
{
    ;

    this.performFormLookup(SysLookup::LookupDate());
}

But in AX2012R2 you cannot use the lookupDate function like this because it is made protected and can only be used by functions in the classes which are derived from SysLookup.

Its work around is to directly call the SysDateLookup form.

The code is as below.

public void lookup() // Form control lookup
{
    FormRun formRun;
    Args    args;
    ;

    args        = new Args(formStr(SysDateLookup));
    args.caller(element);
    formRun     = classfactory.formRunClass(args);
    formRun.init();
    this.performFormLookup(formRun);
}

Happy DAXing!

Microsoft Dynamics AX Job – Eliminate Duplicate Barcode issue

I wrote a job that eliminated the duplicates barcode issue in AX 2012. The job goes through the list of the barcodes, finds duplicated barcodes and adds a D and a counter value at their beginning.  Duplicated barcodes, which are renamed after running this job, can always be found easily by getting a list of the barcodes starting with the letter “D”.

I am sharing the code here:

static void EliminateDuplicateBarcodes(Args _args)
{
    InventItemBarcode inventItemBarcode,inventItemBarcode1;
    int counter;

    counter=1;

    while select itemBarCode,RecId from inventItemBarcode where inventItemBarcode.itemBarCode != ''
    {
        while select forUpdate itemBarCode,recid from inventItemBarcode1
            where inventItemBarcode1.itemBarCode == inventItemBarcode.itemBarCode
                && inventItemBarcode1.RecId != inventItemBarcode.RecId
        {
            ttsBegin;
            inventItemBarcode1.itemBarCode = strFmt("D%1-%2",counter,inventItemBarcode1.itemBarCode);
            inventItemBarcode1.update();
            ttsCommit;

            counter++;
        }

    }

}

Happy Coding :)

Developing SSRS Report Using RDP in Microsoft Dynamics AX 2012

Today I am sharing, how to develop a RDP reports. I will be elaborating the process step by step, so that it is very easy for you to follow the steps and develop your own report.

Step 1: Create a Temp Table SSRS_VendTableTmp

Create a new table and set its table type to InMemory or TempDB. Add the following fields from the vendtable in your table.

  • Vendor Account
  • Address
  • County
  • State
  • Country
  • Telephone
  • Fax
  • Email
  • Telex Number

Step 2: Create a Query SSRS_VendTable

Create a new query and add vendtable in its Data source. Your Query should look like this:

SSRS1

 

Step 3: Create a Data Contract Class SSRS_VendTableDC

The Data contract class defines the parameters in your report. Your Data contract class will have the following methods.

Class Declaration

class SSRS_VendTableDC
{
AccountNum accountNum;
}

Parm Methods – Add as many as you want

[DataMemberAttribute("AccountNum")]
public AccountNum parmAccountNum(AccountNum _accountNum = accountNum)
{
accountNum = _accountNum;
return accountNum;
}

Step 4: Create a Report Data Provider Class SSRS_VendTableRDP

Your RDP class should have the following methods:

 

1.	[ SRSReportQueryAttribute (querystr(SSRS_VendTable)),
SRSReportParameterAttribute(classstr(SSRS_VendTableDC))
]
class SSRS_VendTableRDP extends SRSReportDataProviderBase
{
SSRS_VendTableTmp vendTableTmp;
}

2.	[SRSReportDataSetAttribute("Tmp_SRCustTable")]
public tmpSR_CustTable getTmpSR_CustTable()
{
select * from tmpSR_CustTable;
return tmpSR_CustTable;
}
3.	[SysEntryPointAttribute(false)]
public void processReport()
{
QueryRun queryRun;
Query query;
VendTable vendTable;
SSRS_VendTableDC vendTableDC;
AccountNum accountNum;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
query = this.parmQuery();
vendTableDC = this.parmDataContract() as SRSRDPCustTableContractClass;
accountNum = vendTableDC.parmAccountNum();
// Add parameters to the query.
queryBuildDataSource = query.dataSourceTable(tablenum(VendTable));
if(accountNum)
{
queryBuildRange = queryBuildDataSource.findRange(fieldnum(VendTable, AccountNum));
if (!queryBuildRange)
{
queryBuildRange = queryBuildDataSource.addRange(fieldnum(VendTable, AccountNum));
}

if(!queryBuildRange.value())
queryBuildRange.value(accountNum);
}
queryRun = new QueryRun(query);
while(queryRun.next())
{
vendTable = queryRun.get(tableNum(VendTable));
this.insertTmpTable(VendTable);
}
}

Step 5: Create Report in Visual Studio

  1. Open Visual studio.
  2. Open a new Project.
  3. Select Microsoft Dynamics AX in the installed templates.
  4. Select report model.
  5. Name your report.
  6.  Click Ok.

SSRS2

Step 6: Setup Report

First of all, add a report in your project. Refer to the below pasted picture.

You can rename your report to VendTableReport.

SSRS3

Once you have added the report, right click on the dataset and add a dataset. Right click on your newly added Dataset and select “Properties”. Select “Report Data provider” in the Data Source Type.

SSRS4

Click on the button provided against the query property and select your RDP class.

SSRS5

Now add AutoDesign in your report. You can add an autodesign by right clicking on the designs or you can drag your  VendTableDataset on the designs node.

Select a Layout template in the properties of your AutoDesign

SSRS6

Select a Style template in the properties of your VendTableDatasetDesign node.

SSRS7

Step 7: Add report to AX AOT

The setup is completed. We need to view the report now. In order to do so, build the report by right clicking on your solution. After successful building of the report, deploy the report and add it to AOT.

SSRS8

Step 8: Create an Output Menu item for your report

In order to view the report in Dynamics AX, add an output menu item. Select SRRS Report in Report Type. Select your report in Object and report design in ReportDesign. Once you have setup the menu item, save it and open it. Here is the final report:

SSRS9

Trade Agreement Data Import Utility

Recently, I had to develop a data import utility as a part of one of the projects I was working on. One of the subparts of the data import utility is the trade agreement import utility. The architecture of the data import utility is as follows:

  • Staging: Data is first import into staging table, where It can be viewed, validated or cleared by the user
  • Validate: Data can be validated in order to ensure the correctness of data
  • Import: Data in the staging tables are processed in the AX tables

I am sharing the Trade Agreement Import utility here.

Trade Agreement Import Utility UI:

TradeAgreementImportUI

Staging Class:

TradeAgreementImportStaging Class

Validation Class:

TradeAgreementImportValidation Class

Import Class:

TradeAgreementImportImport Class

Code to Import Trade Agreements:

public void insertPriceDiscAdmTrans(PriceDiscJournalNum _journalNum)
{
    try
    {
        Counter=0;
        ttsBegin;
        
        while select * from tradeAgreement where tradeAgreement.RecordStatus == SL_Status::Created //Only Process Data in staging tables (Status = Created)
        {

            PriceDiscAdmTrans.clear();
            PriceDiscAdmTrans.JournalNum        = _journalNum;
            PriceDiscAdmTrans.AccountCode       =  this.getAccountCode('All');
            PriceDiscAdmTrans.relation          = this.getRelation('PriceSales');
            PriceDiscAdmTrans.Amount            = tradeAgreement.Calculated;
            PriceDiscAdmTrans.Currency          = 'PKR';
            PriceDiscAdmTrans.FromDate          = tradeAgreement.FromDate;
            PriceDiscAdmTrans.Module            = ModuleInventCustVend::Vend;

            ecoResProductMaster = EcoResProductMaster::find(EcoResProduct::findByDisplayProductNumber(tradeAgreement.Item).RecId);
            ecoResDistinctProductVariant.DisplayProductNumber = EcoResProductNumberBuilderVariant::buildFromProductNumberAndDimensions
            (
                ecoResProductMaster.productNumber(),
                EcoResProductVariantDimValue::getDimensionValuesContainer('', tradeAgreement.Size, tradeAgreement.Color, '')
            );
            ecoResProduct = EcoResProduct::findByDisplayProductNumber(ecoResDistinctProductVariant.DisplayProductNumber);
            
            if (ecoResProduct.recid) //Check If Product exist
            {

                if (tradeAgreement.FindNext == 'yes')
                {
                    PriceDiscAdmTrans.SearchAgain = NoYes::Yes;
                }
                else
                {
                    PriceDiscAdmTrans.SearchAgain = NoYes::No;
                }

                PriceDiscAdmTrans.ItemCode      = TableGroupAll::Table;

                if (PriceDiscAdmTrans.ItemCode == TableGroupAll::Table)
                {
                    PriceDiscAdmTrans.ItemRelation = tradeAgreement.Item;


                    if (PriceDiscAdmTrans.ItemRelation != '')
                    {
                        PriceDiscAdmTrans.UnitId = this.getUnitId(PriceDiscAdmTrans.ItemRelation); //Get Product Unit
                    }
                    else
                    {
                        throw error('Item does not exist');
                    }
                }
               
                if (tradeAgreement.Size == '' && tradeAgreement.Color == '') //For Product
                {

                    PriceDiscAdmTrans.InventDimId = 'AllBlank'; // default dimension
                }
                else //For Product Master
                {
                    PriceDiscAdmTrans.InventDimId = this.getInventdim(tradeAgreement.Size, tradeAgreement.Color);
                }
                
                PriceDiscAdmTrans.FromDate = tradeAgreement.FromDate;

                PriceDiscAdmTrans.insert();
                
                //Change Record Status from staged (Created) to processed (Completed)
                ttsBegin;
                select forUpdate tradeAgreementCompleted where tradeAgreementCompleted.recid == tradeAgreement.recid;
                tradeAgreementCompleted.recordStatus = SL_Status::Completed;
                Counter++;
                tradeAgreementCompleted.update();
                ttsCommit;
            }
            else
            {
                throw error(strFmt('Item %1 :Color or Size does not exist',tradeAgreement.Item));
            }

        }
        ttsCommit;
    }
    //rever log in case of any error
    catch(Exception::Error)
    {
        //Change record status from staged (created) to error
        ttsBegin;
        select forUpdate tradeAgreementCompleted where tradeAgreementCompleted.recid == tradeAgreement.recid;
        tradeAgreementCompleted.recordStatus = SL_Status::Error;
        Counter=0;
        tradeAgreementCompleted.update();
        ttsCommit;

        
        ttsAbort;
        info(strFmt('%1',Exception::Error));
    }
    
    //Display the number of records processed
    info(strFmt('%1 TradeAgreements Created',Counter));

Create Excel BOM Report Template from X++ in AX 2012

BOM Excel Template report:

bo1

Path: Product information management/common/release products

Select each grid record whose Production type is BOM or Formula as shown in below screen and click Lines in BOM Engineer action pane or Lines in Formula Engineer action pane

bo2

Add Export to excel button on version Group for exporting excel report template

bo3

Override Export to Excel click button which will call a new class through action menu item

bo4

void clicked()
{
     BOMVersion      BOMVersionlocal;
    str             menuItemStr;
    MenuFunction    menuFunction;
    Args            args = new args();

    super();
    //getFirst method gets all the selected records in the grid
    BOMVersionlocal = BOMVersion_ds.getFirst(1,true);
    args.record(BOMVersionlocal);

    menuItemStr = menuitemActionStr(SL_BOMExport);   
    menuFunction = new MenuFunction(menuItemStr, MenuItemType::Action);
    if (BOMVersionlocal.RecId != 0)
    {
       menuFunction.run(args);
    }
    else
    {
        warning('No record selected');
    }

}

Create a new Class which exporting BOM header and Lines into excel

bo5

public static void Main(Args _args)
{
    ItemId          itemid;
    BOMVersion      BOMVersion;

    BOMVersion = _args.record();

    itemid = BOMVersion.ItemId;


    SL_BOMExport::exportBOM(itemid,BOMVersion.BOMId);
}

public static void exportBOM(ItemId _itemId, BOMId _bomId )
{

    BOMVersion               BOMVersion;
    BOM                      BOM;
    Description              personalNumbervalue;

    SysExcelApplication     application;
    SysExcelWorkBooks       workbooks;
    SysExcelWorkBook        workbook;
    SysExcelWorksheets      worksheets;
    sysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    SysExcelFont            SysExcelFont;

    int                     row = 1;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();            //gets the workbook object
    workbook = workbooks.add();                     // creates a new workbook
    worksheets = workbook.worksheets();             //gets the worksheets object
    worksheet = worksheets.itemFromNum(1);          //Selects the first worksheet in the workbook to insert data
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');           // numberFormat ‘@’ is to insert data as Text


        //header
    worksheet.cells().item(1,1).value(CompanyInfo::Find().Name);
    worksheet.cells().item(1,1).font().bold(true);

    worksheet.cells().item(2,1).value("List of item formula / BOM");
    worksheet.cells().item(2,1).font().bold(true);


    // bom header
    worksheet.cells().item(4,1).value("BOM");
    worksheet.cells().item(4,1).font().bold(true);

    worksheet.cells().item(5,1).value("Name");
    worksheet.cells().item(5,1).font().bold(true);

    worksheet.cells().item(6,1).value("Site");
    worksheet.cells().item(6,1).font().bold(true);

    worksheet.cells().item(4,4).value("From date");
    worksheet.cells().item(4,4).font().bold(true);

    worksheet.cells().item(5,4).value("To date");
    worksheet.cells().item(5,4).font().bold(true);

    worksheet.cells().item(6,4).value("From qty");
    worksheet.cells().item(6,4).font().bold(true);

    worksheet.cells().item(4,7).value("Active");
    worksheet.cells().item(4,7).font().bold(true);

    worksheet.cells().item(5,7).value("Approved by");
    worksheet.cells().item(5,7).font().bold(true);

    worksheet.cells().item(6,7).value("Approved");
    worksheet.cells().item(6,7).font().bold(true);


     select BOMVersion
           where BOMVersion.ItemId == _itemId
           && BOMVersion.BOMId     == _bomId;

        cell = cells.item(4,2);
        cell.value(BOMVersion.BOMId);

        cell = cells.item(5,2);
        cell.value(BOMVersion.Name);

        cell = cells.item(6,2);
        cell.value(InventDim::find(BOMVersion.InventDimId).InventSiteId);

        cell = cells.item(4,5);
        if (BOMVersion.FromDate != dateNull())
        {
            cell.value(BOMVersion.FromDate);
        }
        else
        {
            cell.value("");
        }

        cell = cells.item(5,5);
        if (BOMVersion.ToDate != dateNull())
        {
             cell.value(BOMVersion.ToDate);
        }
        else
        {
             cell.value("");
        }
        cell = cells.item(6,5);
        cell.value(BOMVersion.FromQty);

        cell = cells.item(4,8);
        cell.value(enum2Value(BOMVersion.Active));

        cell = cells.item(5,8);
        personalNumbervalue = HcmWorker::find(BOMVersion.Approver).name();
        cell.value(personalNumbervalue);

        cell = cells.item(6,8);
        cell.value(enum2Value(BOMVersion.Approved));

    row = 6 ;
    row+=2;


     // header lines

       worksheet.cells().item(row,1).value("Item number");
       worksheet.cells().item(row,1).font().bold(true);

       worksheet.cells().item(row,2).value("Product name");
       worksheet.cells().item(row,2).font().bold(true);

       worksheet.cells().item(row,3).value("Configuration");
       worksheet.cells().item(row,3).font().bold(true);

       worksheet.cells().item(row,4).value("Quantity");
       worksheet.cells().item(row,4).font().bold(true);

       worksheet.cells().item(row,5).value("Unit");
       worksheet.cells().item(row,5).font().bold(true);

       worksheet.cells().item(row,6).value("Per series");
       worksheet.cells().item(row,6).font().bold(true);

       worksheet.cells().item(row,7).value("Size");
       worksheet.cells().item(row,7).font().bold(true);

       worksheet.cells().item(row,8).value("Color");
       worksheet.cells().item(row,8).font().bold(true);

       worksheet.cells().item(row,9).value("Style");
       worksheet.cells().item(row,9).font().bold(true);


     while select BOMVersion
           where BOMVersion.ItemId == _itemId
            &&   BOMVersion.BOMId  == _bomId
         join BOM
            where BOMVersion.BOMId == BOM.BOMId

    {

            row++;

            cell = cells.item(row,1);
            cell.value(BOM.ItemId);

            cell = cells.item(row,2);
            cell.value(BOM.itemNameGrid());

            cell = cells.item(row,3);
            cell.value(InventDim::find(BOM.InventDimId).configId);

            cell = cells.item(row,4);
            cell.value(BOM.BOMQty);

            cell = cells.item(row,5);
            cell.value(BOM.UnitId);

            cell = cells.item(row,6);
            cell.value(BOM.BOMQtySerie);

            cell = cells.item(row,7);
            cell.value(InventDim::find(BOM.InventDimId).InventSizeId);

            cell = cells.item(row,8);
            cell.value(InventDim::find(BOM.InventDimId).InventColorId);

            cell = cells.item(row,9);
            cell.value(InventDim::find(BOM.InventDimId).InventStyleId);

    }

    application.visible(true);  // opens the excel worksheet

}

Gift Card in AX 2012

Microsoft Dynamics AX 2012 provides a feature of Gift card to its retail users that is to be issued from Point of Sale (POS) of Store or from any authorized vendors. It is a type of voucher that can be issued from one store and redeemed at any store in the organization. Retail keeps track of gift card balances, so a card can be reused until its available balance reaches zero another feature customer can add money to gift card as well.

Some steps are required to use gift card at Point of sales (POS):

y1

 Detail setups for Gift Card in AX 2012

Configure an account in General ledger main accounts of liability (deferred income) nature

General ledger > main accounts > new main account

1.1

Create a new account for Gift card

1

Define an item group for gift card or use the same which is being used for other products and provide main accounts by

Navigate to Inventory and warehouse management > setup > inventory > item group

1.2

Create a retail product to represent a gift card. Assign barcode if gift card contains any barcode.

Navigate to Product Information Management > Common > Release Product

1.3

Click New Product

1.4

Create a new product as Gift Card

1.5

Create a Payment Method for gift card and assign POS Operation name as pay gift card by navigating through :

Retail > setup > payment method

1.6

Create a payment method for gift card

1.7

Assign the payment methods to store

Retail > common > retail channel > retail stores

1.8

Select a store and assign payment method by set up of action tab

1.9

Click Setup from action tab then click Payment Method

1.10

Click new and select payment method as Gift card from drop down then provide the account number in Posting Fast Tab. The account provided here is the same created in main account as gift card of asset nature. Provide accounts for differences mostly ‘Cash discrepancy’ is used to cater all the differences.

1.11

Click Card setup then click New and enter description and necessary accounts for revenues and assets to posting fast tab

1.12

Provide length of card number by:

Retail > setup > card number

1.13

Card number length depends on company’s requirements

 Different functions of gift card on POS

Gift card offers few functionality which can be used at POS

y20

Issue gift card

Issue gift card from POS by navigating through additional task > gift card

Click Issue Gift Card

y21

Enter or scan card number

Enter amount

1.14

Pay this card from POS same as item sold from POS.

1.15

Pay through Cash or Card

Add to gift card

Add money to gift card by additional task > add to gift card

y21

 Enter / scan card number

Enter a sum of amount to add money to gift card

Then pay cash same as product from POS.

1.16

Balance of gift card

If card owner wants to know balance of gift card user at POS can enter card number and inquire the balance.

y21

Enter gift card number

1.17

Pay through gift card

If customer wants to pay for a transaction through gift card then select payment method as pay gift card and enter/ scan the card number and pay the desired amount

Sale a product and pay through gift card

Select a product

1.18

Pay through gift card by entering card number

1.19

Gift card inquiry form

AX user can inquire transactions related to Gift card which include number of transactions performed by a card with the card number, staff, date and timing of transaction.

Navigate it through retail > inquiry > gift cards

y30

I hope this blog helped you the basic concept of Gift card in AX 2012. Happy POSing J

 

Calling table insert stops the AX service AX2012

Recently, I faced an issue when calling table insert from code. Every time I executed the statement table.insert() the service of dynamics AX2012 was stopped.

There may be two reasons of this issue.

1-      The RecIds of that particular table tables is getting duplicated, in case if you inserted the data to  that particular table from SQL and it picked the old RecId that are not generated by your current AOS.

2-      The table is not well synchronized.

In my case, synchronizing the table resolved the problem.

Happy DAXing!