Create LookUps Using X++

Hi All,

Here I create Lookups using X++ Code(without Ax Table or EDT relation). For that i override a String(Text) Control’ s lookup method.

public void lookup()

{
//super();

// Added by Vasanth Arivali

// Declaration
Query   LookupQuery    =   new Query();

QueryBuildDataSource     LookupQueryBuildDataSource;

QueryBuildRange                  LookupQueryBuildRange;
SysTableLookup CustomSysTableLookup =       SysTableLookup::newParameters(tableNum(InventTable), this);

;

// Add fields that you want in Lookups

CustomSysTableLookup.addLookupField(fieldNum(InventTable, ItemId));

CustomSysTableLookup.addLookupField(fieldNum(InventTable,ItemName));

CustomSysTableLookup.addLookupField(fieldNum(InventTable,ItemGroupId));

CustomSysTableLookup.addLookupField(fieldNum(InventTable,NameAlias));

CustomSysTableLookup.addLookupField(fieldNum(InventTable,ItemType));

CustomSysTableLookup.addLookupField(fieldNum(InventTable,DimGroupId));

// Add DataSource, Range and Value

LookupQueryBuildDataSource =

LookupQuery.addDataSource(tableNum(InventTable));

LookupQueryBuildRange=

LookupQueryBuildDataSource.addRange(fieldNum(InventTable,ItemVisibility));

LookupQueryBuildRange.value(queryValue(NOYESCOMBO::Yes));

// Execute the Query

CustomSysTableLookup.parmQuery(LookupQuery);

CustomSysTableLookup.performFormLookup();
// Added by Vasanth Arivali

}

 

Find the Table Name in Dynamics Ax 2009

Hi,

Here i write a sample code to find the Table Name of Ax Tables using the TableId.

 

static void findTables(Args _args)

{

Dictionary            dictionary;

TableId                                  tableId;

tableName         tableName;

;

 

dictionary            =             new Dictionary();

// tableId             =             dictionary.tableNext(0);

tableId                  =             359;       //366; //62;

tableName         =             dictionary.tableName(tableId);

 

info(strfmt(“%1 – %2”,int2str(tableId), tableName));

 

//while (tableId)

//{

// tableId = dictionary.tableNext(tableId);

// tableName = dictionary.tableName(tableId);

//}

 

}

 

Thank You,

Vasanth Arivali.

 

Argument Passing in Dynamics Ax 2009

Hi,
Here a sample code to pass argument to one form to another form and using of Args() class.
Steps:
1) Create two Forms named FormA and FormB
2)Use the EmplTable as the Datasource of both forms
3)Design FormA with one Grid and add 4 data fields to the Grid(EmplId,DEL_Name,Grade,EmplStatus…..)
4)Assign the datasource for the grid and the data fields
5)Add a Button in FormA
6)Override the Clicked() method and write the below code:
void Clicked()
{
Args    _args;
FormRun _formRun;
EmplId   _empId;
;

_empId = EmplTable.EmplId;  // Selected employee id in the Grid is assigned to the variable which is pass to the next form_args = new Args(); // creating a object for args class
_args.name(formstr(VA_FormB));  // Form Menuitem
_args.caller(this);  // Form Caller(Current Form is mentioned as this)_args.parm(_empId); // Employee Number is passed to next form[but parm() is not a best practise]
_args.record(EmplTable); // Table name is passed

_formRun = ClassFactory.formRunClass(_args); //new FormRun(_args);   // Creating object for FormRun
_formRun.init();   // Form Initialization for Load
_formRun.run();  // Form Run for process
_formRun.wait(); // Form Wait for Display
}

7) Open the Second Form – FormB
8) Add one Grid Control and set the Data Source is EmplTable
9) Add 4 data fields as same in the Form A
10)Now Override the Init() of Form
public void init()
{

parmid      _parmId;
EmplTable   _EmplTable;
//     DictTable   _dictTable;    FormBuildDataSource   _ds;    FormBuildGridControl frmGrid; // These are for dynamic Form creation so leave it
_parmId =  element.args().parm(); // Getting the argument value from the Caller

//info(int2str(element.args().record().TableId));
if(!element.args().caller())   // Check the form is called by caller or directly, if directly it throw error
throw error(“Cant Run Directly”);
if(element.args().record().TableId == tablenum(EmplTable))   // check if the sent Table and the Current form table are equal or not
{

//        _EmplTable = element.args().record();  // Assign the Received Table name to Local Variable
//_dictTable = new DictTable(element.args().record().TableId);  // leave it , is used for Dynamic Form Creation
//_ds = form.addDataSource(_dictTable.name());  // leave it , is used for Dynamic Form Creation

//_ds.table(_dictTable.id());   // leave it , is used for Dynamic Form Creation
//frmGrid = form.addControl(FormControlType::Grid, “Grid”);   // leave it , is used for Dynamic Form Creation

//frmGrid.dataSource(_ds.name());   // leave it , is used for Dynamic Form Creation
//info(strfmt(“%1     %2”,_EmplTable.EmplId,_EmplTable.DEL_Name));

//frmGrid.addDataField(_ds.id(),fieldnum(EmplTable, DEL_Name));  // leave it , is used for Dynamic Form Creation
//        EmplTable_EmplId.dataSource(_EmplTable);   // leave it , is used for Dynamic Form Creation

//        EmplTable_EmplId.dataField(fieldnum(EmplTable,EmplID));   // leave it , is used for Dynamic Form Creation

//        EmplTable_DEL_Name.dataSource(_EmplTable);   // leave it , is used for Dynamic Form Creation

//        EmplTable_DEL_Name.dataField(fieldnum(EmplTable,EmplId));  // leave it , is used for Dynamic Form Creation//        EmplTable_DEL_Email.dataSource(_EmplTable);   // leave it , is used for Dynamic Form Creation

//        EmplTable_DEL_Email.dataField(fieldnum(EmplTable,EmplId));   // leave it , is used for Dynamic Form Creation

super();  // Form Initialization
}
else
{
info(“DataSet Not Received”);  // throw error
}
}

11)Override the Init() of the DataSource

public void init()
{

switch(element.args().dataset())// get the table id sent by caller
{
case tablenum(EmplTable):  // check the table if matches with this tableid
{
_EmplID  =   element.args().parm();  // get the argument value
query   = new Query();            queryBuildRangeProj =                                          query.addDataSource(tablenum(EmplTable)).addRange(fieldnum(EmplTable,EmplId));          // query build for the form to display
queryBuildRangeProj.value(_emplId); // Criteria for the form
EmplTable_ds.query(query); // execution of the query
break;
}
}

super(); //datasource  initialization on the form based on the criteria
}

12) Save it, and create two menu items for each.
13) It is important to change the runon property of the FormB as CalledFrom.
14)Run the FormA and select an Employee Record and click the button.
15)The FormB opens with the Related information of the Selected Employee on form.

Thanks & Regards,
Vasanth Arivali

Connect External Database with Ax 2009

This is the example to connect the External Database to Ax
void ODBCConnection()
{
LoginProperty LP                =  new LoginProperty();
OdbcConnection                  myConnection;
TableName                            TableName;
Statement                              myStatement;
ResultSet                                 myResult;
#define.Constring(“DSN=DSNNAME;UID=USERID;PWD=PASSWORD”)

try
{
LP.setOther(#Constring);
myConnection = new OdbcConnection(LP);
}
catch
{
info(“Check username/password.”);
return;
}

myStatement = myConnection.createStatement();
new SqlStatementExecutePermission(“SELECT * from TableNamewhere bImportflag = 0”).assert();
myResult = myStatement.executeQuery(“SELECT * from TableNamewhere bImportflag = 0”);
CodeAccessPermission::revertAssert();
while (myResult.next())
{
TableName.nMember                = Member;
TableName.tMemberName            = MembName;
TableName.tMemberAddress         = Address;
TableName.tRescountry            = Country;
TableName.tResState              = State;
TableName.insert();
}
}

Thank & Regards

Vasanth Arivali

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

YouTube – 【CCTV2010春节联欢晚会】22.杂技《试比天高》

 

Amazing Video

YouTube – 【CCTV2010春节联欢晚会】22.杂技《试比天高》
 

Creating Number Sequence for a New Module

Creating Number Sequence for a New Module

Say you want to create a new module called Pre Purchase, and for simplicity, we will create just One new Number sequence.

Here’s what to do:-

 

1. Edit the baseEnum NumberSeqModule, adding a reference for your new module (Pre Purchase)

 

2. Create a new EDT say PurchaseRequisitionId which will be used in the module

 

3. Create new class NumberSeqReference_PrePurchase that extends NumberSeqReference

Add 3 methods to that class

public class NumberSeqReference_PrePurchase extends NumberSeqReference

{

}

———————————————————————

protected void loadModule()

{
NumberSequenceReference numRef;

;

/* Setup PurchaseRequisitionId */

numRef.dataTypeId = typeid2extendedtypeid (typeid (PwC_PurchaseRequisitionId));
numRef.referenceHelp = literalStr("Unique key for Purchase Requisition identification. The key is used when creating new Purchase Requisitions."); // Use Labels here
numRef.wizardContinuous = true;
numRef.wizardManual = NoYes::No;
numRef.wizardAllowChangeDown = NoYes::No;
numRef.wizardAllowChangeUp = NoYes::No;
numRef.sortField = 1;
this.create(numRef);
}

———————————————————————

static NumberSeqModule numberSeqModule()

{
return NumberSeqModule::PrePurchase;
}

 

4. Modify the NumberSeqReference Class for the following methods

 

\Classes\NumberSeqReference\moduleList

 

Add the following code

 

// PrePurchase Begin

 

moduleList += NumberSeqReference_PrePurchase::numberSeqModule();

 

// PrePurchase End

 ———————————————————————

\Classes\NumberSeqReference\construct

 

 Add the following code  

// Pre Purchase addition begin

 

case (NumberSeqReference_PrePurchase::numberSeqModule()):

return new NumberSeqReference_PrePurchase(_module);

 

// Pre Purchase addition end

———————————————————————

 

5.  Create a parameters table and form

You should create a parameters table and form for your new module. The easiest way is generally to duplicate an existing Parameters table and modify it as required.

The important elements on the new parameter table are the numberSeqModule() and numberSeqReference() methods.

client server static NumberSeqModule numberSeqModule()

{

    return NumberSeqReference_ PrePurchase::numberSeqModule();

}

---------------------------------------------------------------------

client server static NumberSeqReference numberSeqReference()

{

    return NumberSeqReference::construct(NSParameters::numberSeqModule());

}

In the parameters form, you must ensure that the code in the numberSeqPreInit(), numberSeqPostInit() and NumberSequenceType.executeQuery() methods correctly reflect your new number sequence elements. 

6. Calling a number sequence

Add this code into this TableParameters

static client server NumberSequenceReference numRefSomeMethodID()
{
return NumberSeqReference::findReference(typeId2ExtendedTypeId(typeid(Your EDT)));
} 

7. Add this declaration in the form ClassDeclaration

public class FormRun extends ObjectRun
{
NumberSeqFormHandler numberSeqFormHandler;
}

8. Add this method to the form where you want Number sequences

NumberSeqFormHandler numberSeqFormHandler()
{
if 

(!numberSeqFormHandler)
{
numberSeqFormHandler=
NumberSeqFormHandler::newForm(YourTableParameters::numRefSomeMethodID().NumberSequence,
element,
YourTable_DS,
fieldnum(YourTable,YourField));
}
return numberSeqFormHandler;
}

 9. Add Create, Write and Delete methods to the Data source in the Form:

 

Create:

 

void create(boolean append = false)

           // If created externally

{

    ;

    element.numberSeqFormHandler().formMethodDataSourceCreatePre();

 super(append);

     element.numberSeqFormHandler().formMethodDataSourceCreate();

}

 

 

Write:

 

void write()

{

 

 

    ttsbegin;

 

    element.numberSeqFormHandler().formMethodDataSourceWrite();

 

    super();

 

    ttscommit;

}

 

Delete:

 

void delete()

{

    ttsbegin;

    element.numberSeqFormHandler().formMethodDataSourceDelete();

 

    super();

    ttscommit;

}

10. Create a new Number Sequence in BASIC module and Run Wizard

Basic -> Setup -> NumberSequences -> Number Sequences
 
Create New Number Sequence code and assign the format. Click "Wizard" button and choose your number sequence in the next screen.
 
Now open your form to see the Number Sequence you created. 

Inserting Values to Invent Table from Text File

Hi Everyone,

Here I posted the X++ code for Inserting Text File values into Invent Table.

Inserting Values to Invent Table from Text File

    FilenameOpen               filename;

    dialogField                     dialogFilename;

    Dialog                            dialog;

    TextIO                           file;

    container                       con;

    InventTable                   inventTable1,inventTable2;

    InventTableModule       inventTableModule;

    InventItemGroup           inventItemGroup,inventItemGroup1;

    ItemGroupId                  itemGroupId,itemGroupId1;

    ItemId                            itemId;

    str                                  conItem,conItemGroup;

    #File

    ;

 

 

    dialog                     =   new Dialog("ISIS Bulk Upoad");

    dialogFilename      =   dialog.addField(typeId(FilenameOpen));

 

    dialog.filenameLookupFilter(["@SYS15896",#Mactxt]);  //Create a macro in #File to open all text files(*.txt)

    dialog.filenameLookupTitle("Upload from Text File");

    dialog.caption("Upload from text file");

 

    dialogFilename.value(filename);

 

    if(!dialog.run())

        return;

 

    filename            =   dialogFilename.value();

 

    try

    {

   

    file                = new TextIO(filename, #IO_READ);

 

    if (!file)

    throw Exception::Error;

 

    file.inRecordDelimiter(‘\n’);  //For Next Record

    file.inFieldDelimiter(‘\t’);  //For Next Column value 

 

    ttsbegin;

 

        while(file.status() == IO_STATUS::OK)

        {

            con                   = file.read();

 

            conItem           = conpeek(con,1);

            conItemGroup = conpeek(con,3);

 

            if(conItem!="0")

            {

 

            itemId                   = substr(conItem,1,8);

            ItemGroupId         = substr(conItemGroup,2,13);

 

            //Item Group Table Insert

 

            inventItemGroup1    = inventItemGroup::find(ItemGroupId);

 

                if(inventItemGroup1)

                {

                    info(strfmt("ItemGroup (%1) already exist",ItemGroupId));

                }

                else

                {

                    inventItemGroup.ItemGroupId = ItemGroupId;

                    inventItemGroup.insert();

                    info(strfmt("ItemGroup (%1) Inserted Successfully",ItemGroupId));

                }

                //Item Group Table Insert End

 

                //Invent Table Insert

 

                inventTable1     = inventTable::find(ItemId);

                itemGroupId1    = inventTable::find(ItemId).ItemGroupId;

 

 

                if(inventTable1)

                {

                   if(itemGroupId1 != ItemGroupId)

                   {

                        inventTable2.ItemGroupId = ItemGroupId;

                        inventTable2.update();

                        info(strfmt("ItemGroup for Item (%1) Updated successfully",itemId));

                   }

                   else

                   {

                        info(strfmt("Item (%1) and ItemGroup (%2) already exist",itemId,itemGroupId));

                   }

                }

                else

                {

                    //Invent Table Insert

                    inventTable2.initValue();

                    inventTable2.ItemId              = itemId;

                    inventTable2.ItemGroupId    = ItemGroupId;

                    inventTable2.DimGroupId     = "N – W";

                    inventTable2.ModelGroupId = Enum2str(InventModel::FIFO);

                    inventTable2.insert();

 

                    select forupdate inventTableModule;

                    // Cost

                    inventTableModule.initValue();

                    inventTableModule.ItemId              = ItemId;

                    inventTableModule.ModuleType    = ModuleInventPurchSales::Invent;

                    inventTableModule.insert();

                    // Purchase order

                    inventTableModule.initValue();

                    inventTableModule.ItemId              = ItemId;

                    inventTableModule.ModuleType    = ModuleInventPurchSales::Purch;

                    inventTableModule.insert();

                    // Sales order

                    inventTableModule.initValue();

                    inventTableModule.ItemId              = ItemId;

                    inventTableModule.ModuleType    = ModuleInventPurchSales::Sales;

                    inventTableModule.insert();

 

                    //Invent Item Location Insert

                    inventItemLocation.ItemId           = ItemId;

                    inventItemLocation.inventDimId  = InventDim::inventDimIdBlank();

                    inventItemLocation.insert();

 

                    info(strfmt("Item and ItemGroup for Item (%1) Inserted successfully",itemId));

                }

            }//Null check

        }//While

    ttscommit;

    }

    catch(Exception::Error)

    {

        Error("Upload Failed");

    }

Text File Format that I used for Upload :

Insert Values to Item Group Table from Excel

Insert Values to Item Group Table from Excel

Hi Everyone,

Here I published  the X++ code for Inserting Excel Data in to ItemGroup Table.

Condition: If the importing values are same as the values in the table then the values will not be uploaded, if the description of the item differs then the description only updated in the table.

 

Void Clicked()

{

    SysExcelApplication              application;

    SysExcelWorkbooks              workbooks;

    SysExcelWorkbook                workbook;

    SysExcelWorksheets             worksheets;

    SysExcelWorksheet               worksheet;

    SysExcelCells                        cells;

    COMVariantType                  type;

    System.DateTime                 ShlefDate;

 

    FilenameOpen                     filename;

    dialogField                           dialogFilename;

 

    Dialog                                  dialog;

    InventItemGroup                 inventItemGroup1;

    ItemGroupId                        itemGroupId;

    Name                                  itemGroupName,itemGroupName1;

    int                                        row;

    #Excel

 

    // convert into str from excel cell value

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

    }

 

    ;

 

 

    dialog                       =   new Dialog("Upoad from Excel");

    dialogFilename        =   dialog.addField(typeId(FilenameOpen));

 

    dialog.filenameLookupFilter(["@SYS28576",#XLS,"@SYS28576",#XLSX]);

    dialog.filenameLookupTitle("Upload from Excel");

    dialog.caption("Upload from Excel");

 

    dialogFilename.value(filename);

 

    if(!dialog.run())

        return;

 

    filename                =   dialogFilename.value();

 

    application           =   SysExcelApplication::construct();

    workbooks           =   application.workbooks();

 

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("File cannot be opened.");

    }

 

    workbook            =   workbooks.item(1);

    worksheets         =   workbook.worksheets();

    worksheet           =   worksheets.itemFromNum(1);

    cells                    =   worksheet.cells();

 

    try

    {

       ttsbegin;

 

        do

        {

            row++;

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

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

 

            if(row>1)

            {

                if(substr(itemGroupId,1,1) ==’6′)

                {

                    itemGroupId           = substr(itemGroupId,2,strlen(itemGroupId));

                    inventItemGroup    = inventItemGroup::find(itemGroupId);

                    itemGroupName1   = inventItemGroup::find(itemGroupId).Name;

 

                    if(inventItemGroup)

                    {

                        if(itemGroupName1!=itemGroupName)

                        {

 

                        select forupdate inventItemGroup1 where inventItemGroup1.ItemGroupId == itemGroupId;

                        inventItemGroup1.Name = itemGroupName;

                        inventItemGroup1.update();

                        info(strfmt("Item Description for Item GroupId (%1) Updated succesfully",ItemGroupId));

                        }

 

                    }

                    else

                    {

                        inventItemGroup1.ItemGroupId    = itemGroupId;

                        inventItemGroup1.Name              = itemGroupName;

                        inventItemGroup1.insert();

                        info(strfmt("Item GroupId (%1) uploaded succesfully",ItemGroupId));

                    }

                }

            }

 

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

 

        }while (type != COMVariantType::VT_EMPTY);

 

        application.quit();

 

        ttscommit;

 

    }

    catch

    {

        Error("Upload Failed");

        application.quit();

    }

}

Excel Format that I used for Upload :

Hiding Content pane of AX 2009

Hiding Content pane of AX 2009

Here i posted the code to hide the content pane of Dynamics Ax2009

The below code is to hide the content pane of Ax 2009

static void hideContentPaneWindow(Args _args)

{

     #WinApi

     HWND contentPane = WinApi::findWindowEx(WinAPI::findWindowEx(infolog.hWnd(), 0, ‘MDIClient’, ”),0,’ContentFrame’,” );

     ;

    if (contentPane)

    WinApi::ShowWindow(contentPane,#SW_HIDE); // To restore use #SW_RESTORE

}