Financial Dimension value in lookup

At times you'll be asked to bring the dimension values in lookup, which might be pretty difficult task.

Here in this post i have created a class using which you could easily bring the financial dimension values in lookup 

AX 2012

class DImensionLookupByName_Sa

{

}

-->>then create a method 

Public static client void lookupDimension(FormStringControl stringControl, Name dimensionName)

{

    Args                    args;

    FormRun                 lookupFormRun;

    DimensionAttribute      dimAttribute;

    if (_stringControl != null)

    {

        args = new Args();

        args.name(formStr(DimensionDefaultingLookup));

        args.lookupValue(_stringControl.text());

        args.caller(_stringControl);        

        dimAttribute = DimensionAttribute::findByName(_dimensionName);

        args.lookupField(dimAttribute.ValueAttribute);

        args.record(dimAttribute);        

        lookupFormRun = classfactory.formRunClass(args);

        lookupFormRun.init();

        _stringControl.performFormLookup(lookupFormRun);

    }

}

Here the two parameters that you are supposed to pass is the form control and the dimension's name which you wanna bring in the lookup..(say businessunit,costcenter,customcostcenter and so on.. )

so, 

create a form with a stringedit control and then , create the one line code in the lookup method 

DImensionLookupByName_Sa::lookupDimension(this,"Businessunit");

you will be able to see the dimension values of businessunit in the lookup.. 

or else, 

You could create a form with two stingedit controls say stringedit1 and stringedit2.

and .. in the stringedit1 use this in the lookup method.. 

{

   Query           query;

SysTableLookup  sysTableLookup;

super();

sysTableLookup = SysTableLookup::newParameters(tableNum(DimensionAttribute), this);

sysTableLookup.addLookupfield(fieldNum(DimensionAttribute, Name));

query = new Query();

query.addDataSource(tableNum(DimensionAttribute));

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

}

This would bring all the dimensionattributes name in the lookup 

and then in the second field's lookup enter the following code

DImensionLookupByName_Sa::lookupDimension(this,stringedit1.text());

this would bring the values of the dimension selected in stringedit1.

Alternate Logic in D365FO X++:

public static void DimensionValueLookup(

        FormStringControl   _dimensionValueControl,

        Name                _localizedName,

        boolean             _promptErrorMessage = false)

    {

        Query                   query = new Query();

        SysTableLookup          sysTableLookup;

        QueryBuildDataSource    qbds;

        DimensionAttribute      dimensionAttribute;

        RecId                   dimensionAttributeId;

        DataAreaId              dataAreaId = curext();

        if (_localizedName)

        {

            dimensionAttribute      = DimensionAttribute::findByLocalizedName(_localizedName, false, currentUserLanguage());

            dimensionAttributeId    = dimensionAttribute.RecId;

        }

        if (dimensionAttributeId)

        {

            sysTableLookup = SysTableLookup::newParameters(DimensionCache::instance().dimensionAttributeBackingTable(dimensionAttributeId), _dimensionValueControl);

            sysTableLookup.addLookupfield(dimensionAttribute.ValueAttribute);

            LedgerDimensionTranslationLookupHelper::addLookupTranslation(sysTableLookup, dimensionAttributeId);

            sysTableLookup.addSelectionField(dimensionAttribute.NameAttribute);

            changeCompany(dataAreaId)

            {

                qbds = query.addDataSource(DimensionCache::instance().dimensionAttributeBackingTable(dimensionAttributeId));

                qbds.addOrderByField(DimensionCache::instance().dimensionAttributeValueField(dimensionAttributeId));

                DimensionAttribute::restrictQueryToCategorizedValues(qbds, dimensionAttributeId);

            }

            sysTableLookup.parmQuery(query);

            sysTableLookup.performFormLookup();

        }

        else if (_promptErrorMessage)

        {

            //Please choose a value for "Dimension type" first!

            checkFailed("@GLS100015");

        }

    }

or

public static void lookupDimensionValues(DimensionAttribute _dimensionAttribute, FormStringControl _formStringControl, CompanyId _companyId = curext(), DimensionLookupParameters _lookupParameters = null)

    {

        Args        args;

        FormRun     formRun;

        Debug::assert(_formStringControl != null);

        Debug::assert(_companyId != '');

        if (!_lookupParameters)

        {

            _lookupParameters = new DimensionLookupParameters();

        }

        if (_lookupParameters.parmFilterDate() == dateNull())

        {

            _lookupParameters.parmFilterDate(DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()));

        }

        changecompany(_companyId)

        {

            args = new Args();

            args.name(formStr(DimensionLookup));

            args.callerFormControl(_formStringControl);

            args.caller(_formStringControl.formRun());

            args.lookupValue(_formStringControl.text());

            args.lookupField(_dimensionAttribute.ValueAttribute);

            args.record(_dimensionAttribute);

            args.parmObject(_lookupParameters);

            formRun = classfactory.formRunClass(args);

            formRun.init();

            _formStringControl.performFormLookup(formRun);

        }

    }

Insert PO with multiple lines from excel

Just like PR if you want to create PO with multiple lines use this code snippet..

create an excel with 

Column 1 -> vend account 

Column 2 -> ItemID

Column 3 -> Quantity

Column 4 -> Price 

Column 5 -> Purchid

If you need multiple lines , create multiple excel rows with same PurchID

static void POEXCEL(Args _args)

{

    Dialog                  dialog;

    DialogField             dialogField;

    FilenameOpen            filename, Filename2;

    PO_temp                 potemp,potempline;

    PurchTable              purchtable;

    PurchLine               purchline;

    PurchFormLetter purchFormLetter;

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    FileIOPermission        permission;

    int row = 0;

    str itemid,name,purchid;

    real price,qty;

     #File

     str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)

    {

    switch (_cv.variantType())

    {

    case (COMVariantType::VT_BSTR):

    return _cv.bStr();

    case (COMVariantType::VT_R4):

    return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

    case (COMVariantType::VT_R8):

    return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

    case (COMVariantType::VT_DECIMAL):

    return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

    case (COMVariantType::VT_DATE):

    return date2str(_cv.date(),123,2,1,2,1,4);

    case (COMVariantType::VT_EMPTY):

    return "";

    default:

    throw error(strfmt("@SYS26908", _cv.variantType()));

    }

    return "";

    }

     //importing from excel

    dialog = new Dialog("Posting Payment Journal");

    dialogField = dialog.addField(ExtendedTypeStr("FilenameOpen"),"Source file");

    if (dialog.run())

   {

     filename = dialogField.value();

     permission = new fileIOpermission(filename,"RW");

     permission.assert();

     application = SysExcelApplication::construct();

     workbooks = application.workbooks();

     try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("File not found");

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);

    cells = worksheet.cells();

       delete_from potemp;

         do

    {

    //Incrementing the row line to next Row

    row++;

    //row++;

    name        =  COMVariant2Str(cells.item(row,1).value());

    itemid      =  COMVariant2Str(cells.item(row,2).value());

    qty         =  cells.item(row,3).value().double();

    price       =  cells.item(row,4).value().double();

    purchid  =   COMVariant2Str(cells.item(row,5).value());

        potemp.Name       = name;

        potemp.ItemId     = itemid;

        potemp.PurchQty   = qty;

        potemp.PurchPrice = price;

        potemp.PurchId = purchid;

        potemp.insert();

                //row++;

            //name1 = COMVariant2Str(cells.item(j+1,1).value());

          type = cells.item(row+1, 1).value().variantType();

     }

       //info(strFmt("%1,%2",empId,salary));

      while (type != COMVariantType::VT_EMPTY);

      application.quit();

    }

    while select potemp group by potemp.PurchId,potemp.Name

    {

purchtable.initValue();

purchtable.PurchId = potemp.PurchId;

purchtable.OrderAccount = potemp.name;

purchtable.initFromVendTable();

//if (!purchtable.validateWrite())

//{

//throw Exception::Error;

//}

purchtable.insert();

    while select potempline where potempline.PurchId == potemp.PurchId

    {

        ttsBegin;

    purchLine.initFromPurchTable(purchTable);

    purchLine.ItemId = potempline.ItemId;

    purchLine.PurchQty = potempline.PurchQty;

    purchLine.PurchPrice = potempline.PurchPrice;

    purchLine.createLine(true, true, true, true, true, true);

    ttsCommit;

    purchLine.clear();

    }

    purchFormLetter = PurchFormLetter::construct(DocumentStatus::PurchaseOrder);

    purchFormLetter.update(purchTable, strFmt("Inv_%1", purchTable.PurchId));

    //Posting PO Invoice

    purchFormLetter = PurchFormLetter::construct(DocumentStatus::Invoice);

    purchFormLetter.update(purchTable, strFmt("Inv_%1", purchTable.PurchId));

    purchtable.clear();

    }

}

Import Purchase Requisition From Excel with multiple Lines

Here am giving the code to Import PR from excel and the same PR could have multiple lines, Create an excel sheet with columns like

column 1 -> name OF PR
column 2 -> Item ID
column 3 -> Quantity 
column 4 -> Price 
column 5 -> purchreqID

if you need multiple lines just create multiple rows with same purchreqID

and use the following code snippet..

static void PREXCEL(Args _args)
{
    Dialog                  dialog;
    DialogField             dialogField;
    FilenameOpen            filename, Filename2;
    PR_temp                 prtemp,prtemplines;

    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    PurchReqTable   purchReqTable;
    PurchReqLine    purchReqLine;
    ProjTable       projTable = projTable::find("10002");
    SalesLine       salesLine;// = SalesLine::findInventTransId("10002");
    FileIOPermission        permission;
    int row = 0;
    int j = 0;
    // if the excel has the header
    //parameters
    str itemid,name,purchreqid;
    real price,qty;


    #File

     str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
    {
    switch (_cv.variantType())
    {
    case (COMVariantType::VT_BSTR):
    return _cv.bStr();
    case (COMVariantType::VT_R4):
    return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
    case (COMVariantType::VT_R8):
    return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
    case (COMVariantType::VT_DECIMAL):
    return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
    case (COMVariantType::VT_DATE):
    return date2str(_cv.date(),123,2,1,2,1,4);
    case (COMVariantType::VT_EMPTY):
    return "";
    default:
    throw error(strfmt("@SYS26908", _cv.variantType()));
    }
    return "";
    }




    //importing from excel
    dialog = new Dialog("Posting Payment Journal");
    dialogField = dialog.addField(ExtendedTypeStr("FilenameOpen"),"Source file");





    if (dialog.run())
   {
     filename = dialogField.value();
     permission = new fileIOpermission(filename,"RW");
     permission.assert();
     application = SysExcelApplication::construct();
     workbooks = application.workbooks();
     try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
      //insert without class
   // vendTable = VendTable::find("3114");


delete_from prtemp;

     do
    {
    //Incrementing the row line to next Row
    row++;

    //row++;
    name        =  COMVariant2Str(cells.item(row,1).value());
    itemid      =  COMVariant2Str(cells.item(row,2).value());
    qty         =  cells.item(row,3).value().double();
    price       =  cells.item(row,4).value().double();
    purchreqid  =   COMVariant2Str(cells.item(row,5).value());

        prtemp.Name       = name;
        prtemp.ItemId     = itemid;
        prtemp.PurchQty   = qty;
        prtemp.PurchPrice = price;
        prtemp.PurchReqId = purchreqid;
        prtemp.insert();
                //row++;
            //name1 = COMVariant2Str(cells.item(j+1,1).value());

          type = cells.item(row+1, 1).value().variantType();

     }

       //info(strFmt("%1,%2",empId,salary));
      while (type != COMVariantType::VT_EMPTY);
      application.quit();

    }

     while select  prtemp group by prtemp.PurchReqId ,prtemp.Name
        {


    purchReqTable.clear();
    purchReqTable.initValue();
    purchReqTable.PurchReqId = prtemp.PurchReqId;
    purchReqTable.PurchReqName = prtemp.Name;
    purchReqTable.ProjId = projTable.ProjId;
    purchReqTable.ProjIdDataArea = projTable.dataAreaId;
    purchReqTable.insert();
            while select prtemplines where prtemplines.PurchReqId == prtemp.PurchReqId
            {


    purchReqLine.clear();
    purchReqLine.initValue();
    purchReqLine.initFromPurchReqTable(purchReqTable);
    purchReqLine.ItemId = prtemplines.ItemId;//salesLine.ItemId;
    salesLine = SalesLine::findInventTransId(prtemplines.ItemId);
    purchReqLine.InventDimId = salesLine.InventDimId;
    purchReqLine.PurchQty =  prtemplines.PurchQty;
    purchReqLine.PurchPrice = prtemplines.PurchPrice;

    //purchReqLine.ActivityNumber = 'AO-3456789';//salesLine.ActivityNumber;
    purchReqLine.BuyingLegalEntity = CompanyInfo::find().RecId;
    purchReqLine.InventDimIdDataArea = curext();//salesLine.dataAreaId;
    purchReqLine.initFromProjTable(projTable);
    purchReqLine.insert();
            }
        }

}