Sales Table to sales line update

The SalesTable2Line framwork is used in AX to update fields on the SalesLine when the corresponding field in the SalesTable was changed e.g. DevliveryMode. Follow these steps to extend the framework for your own fields.

• Create a new field in the SalesTable and SalesLine e.g. a SalesNote field
• Add the field to the FieldGroup HeaderToLineUpdate in the SalesTable
• Display the new fields in the SalesTable form
• Delete all records from the SalesTable2LineParameters table
• Open Accounts Receivable > Settings > Parameter > "Update order line"
• Extend the SalesTable2LineField.lineUpdateDescription method

case fieldnum(SalesTable, SalesNote): 
    return fieldid2pname(tableNum(SalesLine), fieldNum(SalesLine,SalesNote));

• Add parm methods for the new field to AxSalesTable and AxSalesLine classes

public SalesNote parmSalesNote(SalesNote _salesNote = “) 
{ 
    if (!prmisdefault(_salesNote)) 
    { 
        this.setField(fieldnum(SalesLine, SalesNote), _salesNote); 
    }
 
    return salesLine.SalesNote; 
}

• Create a set method in the AxSalesLine class

protected void setSalesNote() 
{ 
    if (this.isMethodExecuted(funcname(), fieldnum(SalesLine, SalesNote))) 
    { 
        return; 
    }
 
    this.setAxSalesTableFields();
 
    if (this.isAxSalesTableFieldsSet() || this.axSalesTable().isFieldModified(fieldnum(SalesTable, SalesNote))) 
    { 
        this.parmSalesNote(this.axSalesTable().parmSalesNote()); 
    } 
}

• Add the call of this set method in the AxSalesLine.setTableFields method

Reports Unpack Method raised the Debugger mode with error "Wrong Argument types in variable.."

I was updated the AX 2009 RU6 with  RU8 and as the users checked user reports; the error and debugger for unpack method is raised with the following description.


'Wrong Argument types in variable assignment'


This error can also be raised if you are importing any report to the AOT.


To Resolve the error i used following action for particular user.
1. Go to Administrator
2. Open Users form and select the user
3. Select User Options.
4. Select Usage Data
5. Click on the Reset button.

This will reset all the temp data of selection,range etc.

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

You might get this error message following an upgrade:

Cannot execute a data definition language command on (). The SQL database has issued an error. Problems during SQL data dictionary synchronisation. The operation failed. Synchronise failed on n table(s)

To find out exactly what the problem is you will need to have a look in the Windows event viewer on the AOS server, which will look something like this:

If the error is similar to this (I.E of the family “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found”) then you have the following options to solve the problem:

1. If you are not worried about the data in the table then the simplest solution is to drop the table

Otherwise:

2. In AX, temporarily disable the index (that causes the duplicate) and use the table browser with filters(using data from the event log) to correct the data.

or

3.  Use SQL Server management studio to resolve the problem – see this microsoft knowledge base article for a great guide on how to do this: http://support.microsoft.com/kb/139444

=================================================================

=================================================================

Run the below job , in order to get the table name

static void forceDbSynchronize(Args _args)

{

    Dictionary              dict;

    int                     idx, lastIdx, totalTables;

    TableId                 tableId;

    Application             application;

    SysOperationProgress    progress;

    StackBase               errorStack;

    ErrorTxt                errorTxt;

    ;

    application = new Application();

    dict = new Dictionary();

    totalTables = dict.tableCnt();

    progress = new SysOperationProgress();

    progress.setTotal(totalTables);

    progress.setCaption("@SYS90206");

    errorStack = new StackBase(Types::String);

    lastIdx = 0;

    try

    {

        for (idx = lastIdx+1; idx <= totalTables; idx++)

        {

            tableId = dict.tableCnt2Id(idx);

            progress.setText(dict.tableName(tableId));

            lastIdx = idx;

            application.dbSynchronize(tableId, false, true, false);

            progress.incCount();

        }

    }

    catch (Exception::Error)

    {

        errorTxt = strFmt("Error in table '%1' (%2)", tableId, dict.tableName(tableId));

        errorStack.push(errorTxt);

        retry;

    }

    setPrefix("@SYS86407");

    errorTxt = errorStack.pop();

    while (errorTxt)

    {

        error(errorTxt);

        errorTxt = errorStack.pop();

    }

}

Service Tax Hotfix for INDIA localization (KB2554055)

Service tax patch affects only GLP and SYP layer.

When we run the axupdate.exe, it asks for a configuration file.

Then it auto run itself.

Even if it is successfully installed,

It always skips to import the following few classes:

1.       InventCostInputAmount
2.       InventLedgerConflictMessage
3.       InventLedgerConflictMessageTest
4.       BlackListTransfer_IT

Then we need to go to the extracted files of that patch and find the XPOs related to GLP and SYP layer.

From these XPOs Select only the above selected classes and Import them.

You may find few errors related to the following classes:

1.          InventUpd_Physical
2.           InventQuarantineUpdStartUp
3.           InventMovement

Error may say that InventUpd_Physical is not a class.

Double click that error and locate the Class.

You will find that there are some changes in the class in usr layer.

Just export and import the class.

It will solve all the related errors too.

In addition to these above classes you may get errors in following classes too:

1.        TaxSalesQuotation
2.        TaxSales

Similarly here also you need to just export and import the Tax Sales class to resolve the related errors.

Hope it helps!!!

Go To Main Table on Custom Field On Form

While customizing some of the forms in Dynamics Ax 2009 i come across to create a "Go To Main Table" functionality on the custom field placed on the form.
To Execute this i used following code at the design part (field>> methods>>jumpref method)

Code :

public void jumpRef()
{
    Args        args;
    FormRun     fr;
    ;

    args = new Args(formstr(SheCRTermMaster)); //TableName
    args.lookupField(fieldNum(SheCRTermMaster, TermID)); //Fields of Table
    args.lookupField(fieldNum(SheCRTermMaster, Description));
    args.lookupValue(this.text()); //Optional

    fr = ClassFactory.FormRunClass(args);
    fr.init();
    fr.run();
    fr.detach();
}

How to write data into an Excel sheet from Microsoft Dynamics Ax

This article explains

• How to write data into an Excel sheet from Microsoft Dynamics Ax using X++ language.
• How to write data into an Excel sheet using COM object in Microsoft Dynamics Ax.
• How to export Microsoft Dynamics Ax data into an Excel sheet at run time / dynamically /programmatically using X++ language.

I applied it on Dynamics Ax 2009.
Prerequisite:

• You should have X++ basic programming knowledge;

Open Ax client > AOT > Jobs node, create a new job and copy the below code segment into your job.
static void writeDataIntoExcelSheetFromAx(Args _args)
{
InventTable inventTable;
Container itemIdCon;
COM comRange;
COM comWorkBook;
COM comWorkBooks;
COM comWorkSheet;
COM comCharacters;
COM comAppl;
str test,test1;
int offset = 65-1; //65 means letter 'A'
str 2 columnId;
str fileName;
str time;
int i;
#define.Excel('Excel.Application');

comAppl = new COM(#Excel);
comAppl.visible(true);
comWorkbooks = comAppl.workbooks();
WINAPI::createFile('C:\\test.xls');
comWorkbook = comWorkbooks.open('C:\\test.xls',true,true);
comWorksheet = comWorksheet.new('C:\\test.xls');
comWorksheet = comWorkbook.activeSheet(); //Use comWorkbook.activateSheet(); in case of Ax 3.0
comWorksheet.select();
while select inventTable
{
columnId = num2char(offset + 1);
i++;
test = columnId + int2str(i);
comRange = comWorksheet.range(test);
comCharacters = comRange.characters();
comCharacters.insert(inventTable.ItemId);

columnId = num2char(offset + 2);
test = columnId + int2str(i);
comRange = comWorksheet.range(test);
comCharacters = comRange.characters();
comCharacters.insert(inventTable.ItemName);
}

WINAPI::createDirectory('C:\\AxData');
time = time2str(timenow(),1,1);
time = strrem(time,':');
fileName = 'C:\\AxData\\' + curuserid() + date2str(today(),123,2,0,3,0,4)+ time + 'test' + '.xls';
comWorkbook.saveAs(fileName);
}

Bank Deposite, Payment has been cancelled

While Canceling Payment by system through Customer Transaction (Account Receivable>customer detail>Transactions>Cancel Payment)
Error accoured : "Payment xxxxxxxx has already been canceled"
Solution:
1. Open BankDeposite Table
2. Change MaxAccessMode property of table from View to EDIT
3.Find out the DepositeNum (which mention in Error)
4.Untick the field Cancelled
5.That's done. Now save the table and again change the MaxAccessMode to View

Thnx

Update Records of Table With While Loop

If we have to update the records of the table one by one we should have to follow the syntax as below:

1. Get the records from the tables
2. Start While loop with the table
3.Write code to update the record (Or anything you have to with record)
4.At the End of While Loop Next TableName.
----------------------------------------------------------------------------
Syntax:

tablename = get records form table

while(tablename)
{
    tablename.fieldname=value;
   
    tablename.Update();

    Next Tablename;
}
------------------------------------------------------------------------------
Example:

salesTable  = SalesTable::custOpenOrders(this.AccountNum,true);
while (salesTable)
{
        if (salesTable.CurrencyCode == origCurrencyCode)
        {
            salesTable.convertCurrencyCode(this.Currency);
            salesTable.update();
        }
        next salesTable;
}

Remove red hand In vendors transactions

Following are the steps for removing "RED HAND FROM VENDOR TRANSACTION"

1. In vendor detail form select that particular vendor and note information like voucher,date,amount
2. Open VendTransOpen table
3. Select that vendor and note RecId
4. Open SpecTrans table
5. Select RefRecid of that RecId
6. Delete that record from table

insert_recordset with join query in Ax 2009

The ListOfFields in the destination table must match the list of fields in the source tables. Data is transferred in the order that it appears in the list of fields. Fields in the destination table that are not present in the list of fields are assigned zero-values as in other areas in X++. System fields, including RecId, are assigned transparently by the kernel in the destination table.

insert_recordset  DestinationTable  (  ListOfFields  )

select  ListOfFields1  from  SourceTable  [ where  WhereClause  ]

[ join  ListOfFields2  from  JoinedSourceTable 

[ where  JoinedWhereClause  ]]

~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:

The following X++ code example shows a join of three tables on an insert_recordset statement that has a sub-select. Also, a while select statement with a similar join is shown.

A variable is used to supply the inserted value for one column. The str variable must be declared with a length that is less than or equal to the maximum length of the corresponding database field.

X++ Sample code

static void InsertJoin42Job(Args _args)
{
    GmTabDepartment tabDept2;
    GmTabEmployee tabEmpl3;
    GmTabProject tabProj4;
    GmTabEmployeeProject tabEmplProj5;
    str 64 sDescriptionVariable = "From variable.";
    ;
    DELETE_FROM tabEmplProj5;

    INSERT_RECORDSET tabEmplProj5 (Description, EmployeeRecId, ProjectRecId)    Select  sDescriptionVariable, RecId   from    tabEmpl3
      join   tabDept2    where tabEmpl3 .DepartmentGuid == tabDept2 .DepartmentGuid
        join RecId   from tabProj4 where tabDept2 .DepartmentGuid == tabProj4 .DepartmentGuid    ;

    info(int642str(tabEmplProj5 .rowCount())
        + " ==Number of rows inserted.");

    WHILE SELECT *
        from
            tabEmplProj5
            join tabEmpl3
                where tabEmplProj5 .EmployeeRecId == tabEmpl3 .RecId
            join tabProj4
                where tabEmplProj5 .ProjectRecId == tabProj4 .RecId
    {
        info(
            tabEmpl3 .EmployeeName
            + "  --works on--  "
            + tabProj4 .ProjectName
            + " (" + tabEmplProj5 .Description + ")."
            );
    }
/*****************  Actual Infolog output
Message (01:05:41 pm)
4 ==Number of rows inserted.
Alice  --works on--  Project ZZZ (From variable.).
Alice  --works on--  Project YY (From variable.).
Beth  --works on--  Project ZZZ (From variable.).
Beth  --works on--  Project YY (From variable.).
*****************/
}

box::yesno syntax in Dynamics ax 2009

A simple way to let the user deside what is going to happen.

1 2 3 4 5 6 7

if(box::yesNo("Do you want something to happen?", dialogbutton::Yes) == dialogbutton::Yes) {   somethingHappens = this.callTosSomeReturnMethod();   }

Get Single Records From Datasource and Update records

Hi Friends,

While development i came across the criteria which recommends me to insert or update the records on the basis of perticular field of table. In this case i used the getFirst and getNext methods of the datasource to fetch single records from the specified datasource.

Here is the code sample.......

~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~

InventTrans     tmpInventTrans;

tmpInventTrans = inventTrans_ds.getFirst(0,false);

while(tmpInventTrans)
{
         if(tmpInventTrans.SheAmortCancel == Noyes::No)
        {
            cancelVoucher=false;
            break;
        }
        tmpInventTrans = inventTrans_ds.getNext();
}

~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~

tmpInventTrans = inventTrans_ds.getFirst(0,false);
while(tmpInventTrans)
{
                select forupdate inventTransUpdate where inventTransUpdate.RecId == tmpInventTrans.RecId;
                inventTransUpdate.SheAmortCancel = NoYes::No;
                inventTransUpdate.update();
                tmpInventTrans = inventTrans_ds.getNext();
}
info("Amortization cancellation aborted");

~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~

Dynamics AX - code to Read/Write data to excel

Writing Data to Excel file
How it works
1. Use SysExcelApplication class to create excel file.
2. Use SysExcelWorkbooks and SysExcelWorkbook to create a blank workbook(by default 3 worksheets will be available).
3. Use SysExcelWorkSheets to select worksheet for writing data.
4. SysExcelCells to select the cells in the excel for writing the data.
5. SysExcelCell to write the data in the selected cells.
6. Once you done with write operation use SysExcelApplication.visible to open
file.

static void Write2ExcelFile(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);
}

Reading Data from Excel File

static void 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();
}

Dimension Lookup on Report dialog

I received a requirement to show the Department dimension on the report dialog, there are couple of ways to achieve this functionality,

but in my opinion, the easiest way to create a new EDT called DepartmentDimension and make a relations to DepartmentDimension == Dimension.num. And 0 == Dinemsions.DimensionCode; where 0 is the dimension you want to use. Finally add following code in the dialog() method of the class:

dialog.addFieldValue(typeid(DepartmentDimension ) ,departmentDimensionVariable ,"Label - if required","help text - if required");

Refresh Caller form From another/child form in dynamics ax 2009

It is quite straight forward.  In the parent form, write a method like below -
void bookrefresh()
{
    <form datasource>.reread();
    <form datasource>.refresh();
   <form datasource>.research(true); // This is used to be on the same record.
}
In the child form, you can do this -
void bookRefresh()
{
    FormRun callerForm = element.args().caller();

    if (callerForm)
    {
        if (callerForm.name() == formstr(<form name>))
        {
            callerForm.bookRefresh();
        }
    }
}

Using Like in axapta x++ Query

Sometimes, we want to filter records in Dynamics AX Query, using Like criteria and we don't know how to do it... 


The way to do that is using DataAreaId as a FieldId to filter.


In this example, we want to filter the InventTable records that the ItemId begins with "01", ends with "00" and has a length of 10 chars.

Query createQuery()
{
    Query                   q;
    QueryBuildDataSource    qbdsInventTable;
    ;


    q = new Query();


    qbdsInventTable = q.addDataSource(tablenum(InventTable));


    qbdsInventTable(fieldnum(InventTable, DataAreaId))
                    .value(strfmt('(ItemId like "%1")',"01??????00"));


    return q;
}

Browse lines in a form in Dynamics AX

Axapta X++ code to browse lines in a form in Microsoft Dynamics AX.


The function updates field value of all lines of the LedgerJournalTrans form (the field is a new created field):


Also, the function returns the initial position or line in the form.


void clicked()
{
    int pos;
    ;


    super();


    pos=LedgerJournalTrans_DS.getPosition();


    LedgerJournalTrans_DS.research();


    LedgerJournalTrans.DAS_Transfer=NoYes::Yes;
    LedgerJournalTrans.update();


    while(LedgerJournalTrans_DS.queryRun().next())
    {
        LedgerJournalTrans.DAS_Transfer=NoYes::Yes;
        LedgerJournalTrans.update();
    }


    LedgerJournalTrans_DS.research();


    LedgerJournalTrans_DS.setPosition(pos);
}

Change company in Dynamics AX 2009

Hi, everybody!

In Microsoft Dynamics AX Axapta, sometimes, we can to insert/update/delete records in different companies or insert/update/delete according to the company we are...

In Dynamics AX 2009, exists the "changeCompany" function, that allow us to do that easily.

Here's an example:
static void main()
{
    CustTable custTable;
    ;

    //Assume that you are running in company 'aaa'.
    changeCompany('bbb') //Default company is now 'bbb'.
    {
        custTable = null;
        while select custTable
        {
            //custTable is now selected in company 'bbb'.
        }
    }


    //Default company is again set back to 'aaa'.

    changeCompany('ccc') //Default company is now 'ccc'.
    { 
        //Clear custTable to let the select work
        //on the new default company.
        custTable = null;
    

        while select custTable
        {
            //custTable is now selected in company 'ccc'.
        } 
    }

    //Default company is again 'aaa'.
}

Good DaXing!

Send multiple field to another form or object (parmObject (Container))

1. Send
void clicked()
{
    Args  args = new Args();
    ContainerClass conClass_Obj;
    Container conSend;
   ;

         conSend = conins(conSend, 1, "Call Of Duty");
         conSend = conins(conSend, 2,"BattleField");
         conSend = conins(conSend, 3, "Assisins Creed");
         conSend = conins(conSend, 4, "retrive hell");
         conClass_Obj = new ContainerClass(conSend);
         Args.parmObject(conClass_Obj);
         new MenuFunction(menuitemdisplaystr(test_ParamObject_Recieve), MenuItemType::Display).run(args);
}
2. Receive
public void init()
{
    //super();
    str s1,s2,s3,s4;
    containerClass conClass_Obj;
    container conRec;
    Args args;
;
    super();
    args = new args();
    conClass_Obj =  element.args().parmObject();
    conRec = conClass_Obj.value();
    s1 = conpeek(conRec,1);
    s2 = conpeek(conRec,2);
    s3 = conpeek(conRec,3);
    s4 = conpeek(conRec,4);
    info(strfmt("Value1 = %1 , Value2 = %2 , Value3 = %3 ,Value4 = %4",s1,s2,s3,s4));
}

Setting propperties on a FormControl without AutoDeclaration

When you want to set a property of a control on a form without setting the property AutoDeclaration = yes. You can address the control trough the following code, knowing that element is a FormRun-object:

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

element.design().controlName("btnOk").visible(false);

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