Creating Vendors through X++ in AX 2012- PART IV

Creating Vendors through X++ in AX 2012- PART IV

Create a contact for the vendor

Check below posts for more details

Creating Vendors through X++ in AX 2012- PART I

Creating Vendors through X++ in AX 2012- PART II

Creating Vendors through X++ in AX 2012- PART III

-Harry

Custom lookup for Dialog fields in Axapta [AX2012]

Custom lookup for Dialog fields in Axapta

Overriding the event methods (e.g. custom lookup, modify, validate, selectionChange) on dialog controls is not as straight forward as it is on form controls, but the good news is that it is possible!
In order to override the event methods on dialog controls, the following needs to be done.
class TestDialogFields
{
    Dialog                   dialog;  
    DialogField           newProdIdField;
}
public static void main(Args args)
{
    TestDialogFieldstestDialogFields = new TestDialogFields();
     DialogRunbase               dialogRunbase;
    ;
    dialogRunbase = DialogControlOverload.showDialog();
    if(dialogRunbase.run())
    {
        // code for update  once the use selects a record from the lookup and click 'ok'
     }
}
Dialog showDialog()
{
    FormRun     formRun;
    ;
    dialog = new Dialog('Dialog fields test');
    newProdIdField = dialog.addField(typeId(ProdId));
    dialog.run(true);
    dialog.formRun().controlMethodOverload(true);
    dialog.formRun().controlMethodOverloadObject(this);
    return dialog;
}
If we want to create a Custom lookup
public void Fld1_1_lookup()
{
    FormStringControl control = dialog.formRun().controlCallingMethod();
    boolean ret;
    SysTableLookup          sysTableLookup =  SysTableLookup::newParameters(tablenum(Prodtable, control);
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource;
    QueryBuildRange         queryBuildRange;
    ProdTable               prodTable;
     ;
   prodTable = ProdTable::find(prodId);
sysTableLookup.addLookupfield(fieldnum(ProdTable, ProdId));
    sysTableLookup.addLookupfield(fieldnum(ProdTable, Name));
  //You can add the ranges for filtering
   sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}
We can add all the other methods  as follows
public void Fld1_1_modified()
{
  // modify code goes here
}
public void Fld1_1_validate()
{
// validation code goes here
}
 
-Harry

How to Read/Write an Excel file through X++ code

How to write an Excel file through X++ code

In This Post you will found two code sample
1.Write data in excel through X++ code.
2. Read from an Excel through X++ code

1.Write data in excel through X++ code.





static void thaAxapta_Write2Excel(Args _args)
{
InventTable inventTable;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');
cell = cells.item(1,1);
cell.value("Item");
cell = cells.item(1,2);
cell.value("Name");
row = 1;
while select inventTable
{
    row++;
    cell = cells.item(row, 1);
    cell.value(inventTable.ItemId);
    cell = cells.item(row, 2);
    cell.value(inventTable.ItemName);
}
application.visible(true);
}

2. Read from an Excel through X++ code

static void theAxapta_ReadExcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
ItemId itemid;
Name name;
FileName filename;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "C:\\item.xls";
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();
do
{
    row++;
    itemId = cells.item(row, 1).value().bStr();
    name = cells.item(row, 2).value().bStr();
    info(strfmt('%1 - %2', itemId, name));
    type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}

Multiple Tables In Query

Axapta query on multiple tables

Hey friends try this code to add multiple tables in query.

QueryRun selectReportQuery()

{

    Query                   query           = new Query();

    QueryRun                localQueryRun;

    QueryBuildDataSource    qbds1;

    QueryBuildDataSource    qbds2;

    QueryBuildDataSource    qbds3;

    ;

    qbds1 = query.addDataSource(tableNum(salesTable));

    qbds1.addRange(fieldNum(salesTable, affVendorAdjust)).value('1');

    qbds2 = qbds1.addDataSource(tableNum(custInvoiceJour));

    qbds2.fetchMode(JoinMode::InnerJoin);

    qbds2.relations(false);

    qbds2.addLink(fieldNum(salesTable, salesId),fieldNum(custInvoiceJour, salesId));

    qbds2.addSortField(fieldNum(CustInvoiceJour, OrderAccount), SortOrder::Ascending);

    qbds2.addSortField(fieldNum(CustInvoiceJour, InvoiceId), SortOrder::Ascending);

    qbds2.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange(invBeginDate, invEndDate));

    qbds3 = qbds1.addDataSource(tableNum(salesLine));

    qbds3.relations(true);

    qbds3.joinMode(JoinMode::InnerJoin);

    qbds3.fetchMode(queryFetchMode::One2Many);

qbds3.addRange(fieldNum(salesLine, mbsLineVendor)).value(queryValue(vendAccount));

Here is some more code related to query in axapta

1. Sample union query from AX 2009

2. Building a query object

3. How to create a Query(dynamically)

4. Join Two Tables at Run Time

-Harry

Cannot execute a data definition language command on (). The SQL database has issued an error.

Error:
Cannot execute a data definition language command on  ().
The SQL database has issued an error.

Suggested Solutions:
http://support.microsoft.com/kb/937765
http://kdynamics.blogspot.in/2012/07/cannot-execute-data-definition-language.html
-Harry

Creating Vendors through X++ in AX 2012- PART III

Creating Vendors through X++ in AX 2012- PART III

Creating Vendors through X++ in AX 2012- PART I

Creating Vendors through X++ in AX 2012- PART II

Create postal address



-Harry

Error: Cannot create a record in Balance (CustVendTmpOpenTransBalances). Currency: , 0,00.The record already exists

Error: Cannot create a record in Balance (CustVendTmpOpenTransBalances). Currency: , 0,00.The record already exists

Hi all,
I found a good form post for the same error,
check this link
Error: Cannot create a record in Balance (CustVendTmpOpenTransBalances). Currency: , 0,00.The record already exists
-Harry

X++ Code to Create Purchase Order and Post the Invoice.

X++ Code to Create Purchase Order and Post the Invoice.

Following code will create the Purchase order from code and post the invoice as well by making use of "PurchFormLetter" class.

static void TheaxaptaCreatePOInvoice(Args _args)
{
NumberSeq numberSeq;
Purchtable Purchtable;
PurchLine PurchLine;
PurchFormLetter purchFormLetter;
;
ttsbegin;
numberSeq = NumberSeq::newGetNumFromCode(purchParameters::numRefPurchaseOrderId().NumberSequence,true);
// Initialize Purchase order values
Purchtable.initValue();
Purchtable.PurchId = numberSeq.num();
Purchtable.OrderAccount = '3000';
Purchtable.initFromVendTable();
if (!Purchtable.validateWrite())
{
throw Exception::Error;
}
Purchtable.insert();
// Initialize Purchase Line items
PurchLine.PurchId = Purchtable.PurchId;
PurchLine.ItemId = 'B-R14';
PurchLine.createLine(true, true, true, true, true, false);
ttscommit;
purchFormLetter = purchFormLetter::construct(DocumentStatus::Invoice);
purchFormLetter.update(purchtable, // Purchase record Buffer
"Inv_"+purchTable.PurchId, // Invoice Number
systemdateget()); // Transaction date
if (PurchTable::find(purchTable.PurchId).DocumentStatus == DocumentStatus::Invoice)
{
info(strfmt("Posted invoiced journal for purchase order %1",purchTable.PurchId));
}
}

You can Change the document status to packingSlip , if you want to post packing slip by using the same code.

-Harry

How to Enable/Disable fields in a dialog

How to Enable/Disable fields in a dialog

If you need to enable/disable the dialog fields use dialog postrun method in your code.
here is some code sample for enabling/disable dialog fields.

public void dialogPostRun(DialogRunbase _dialog)
{
    ;
    super(_dialog);
// allow to call the event methods of this class (e.g. theaxapta1_modified() method)
    _dialog.dialogForm().formRun().controlMethodOverload(true);
    _dialog.dialogForm().formRun().controlMethodOverloadObject(this);
}

Now you can create event methods on your dialog fields like theaxapta1.modified() where you can address other components in the dialog, and modify their properties as well.

public boolean theaxapta1_modified()
{
    FormStringControl   control = dialog.formRun().controlCallingMethod();
    boolean             isFieldModified;
    ;
    isFieldModified = control.modified();
// every time the employee id is changed,it will update the employee name
    if(isFieldModified)
    {
        dlgFldEmplName.value(EmplTable::find(control.text()).Name());
    }
    return isFieldModified;
}

You have to make sure that the control “Employee ID” gets the same control ID as used in the event method name (). This should be done at the time of adding the control to the dialog. for eg:

protected Object dialog(DialogRunbase _dialog, boolean _forceOnClient)
{
    ;
    dialog = super(_dialog, _forceOnClient);
// Add a new field by explicitly specifying the field id.
    // This field id is used to create the field event methods (e.g. theaxapta1_modified()).
    dlgFldEmplId = new DialogField(dialog, typeid(EmplId), #dlgFlgEmplIdFieldNo);
    dialog.addCtrlDialogField(dlgFldEmplId.name());
    dlgFldEmplId.init(dialog);
    dlgFldEmplId.label("@SYS81251");
    dlgFldEmplId.helpText("@SYS81251");
    dlgFldEmplId.value(emplId);
// verify that the field name generated by the system is correct
    if(dlgFldEmplId.name() != #dlgFlgEmplIdFieldName)
    {
        throw error(strfmt("@SYS79285", dlgFldEmplId.name(), #dlgFlgEmplIdFieldName));
    }
// Add a new field and let the Dialog framework to do all the work by assigning the field id and initializing the control since there are no event methods for that field.
    dlgFldEmplName = dialog.addFieldValue(typeid(EmplName), emplName, "@SYS54564", "@SYS54564");
    dlgFldEmplName.enabled(false);
    return dialog;
}

Then you can get the value of fields using this method :

public boolean getFromDialog()
{
    boolean ret;
    ret = super();
// get the values from the dialog in order to save them in SysLastValue (using pack() method)
    emplId      = dlgFldEmplId.value();
    emplName    = dlgFldEmplName.value();
    return ret;
}
verify whether a correct employee id has been specified
public boolean fld900_1_validate()
{
    FormStringControl   control = dialog.formRun().controlCallingMethod();
    ;
// verify whether a correct employee id has been specified
    return EmplTable::checkExist(control.text());
}

The pack and unpack methods are used as following:

public container pack()
{
// pack the employee id and employee name and save them in SysLastValue
    return [#CurrentVersion, #CurrentList];
}
public boolean unpack(container packedClass)
{
    boolean  ret;
    Integer  version = conpeek(packedClass,1);
    ;
    switch (version)
    {
        case #CurrentVersion:
// get the employee id and employee name values from the container that has been saved in the SysLastValue record
            [version, #CurrentList] = packedClass;
            ret = true;
            break;
        default:
            ret = false;
    }
    return ret;
}

Finally, in the main method :

static void main(Args args)
{
    SETutorialDialogControlEvent  seTutorialDialogControlEvent = SETutorialDialogControlEvent::construct();
    ;
// show the dialog
    if (seTutorialDialogControlEvent.prompt())
    {
// if OK is pressed, run some code (in that case, run() method should be overriden in the current class)
        seTutorialDialogControlEvent.run();
    }
}

Where SETutorialDialogControlEvent is defined as following:

public static SETutorialDialogControlEvent construct()
{

return new SETutorialDialogControlEvent();

}

As you can see the macro #dlgFlgEmplIdFieldNo is used to assign the ID to the dialog control. 
Overriding the event methods (e.g. modify, validate, selectionChange) on dialog controls is not as straight forward as it is on form controls.

-Harry