How To Use Temporary Tables In Reports

Step 1: Take a Report and Add a Table. 
Step 2: Make sure that Table Properties set as "Temporary: YES"
(OR) In Report init method write this code 

           Tmtab.setTmp();

Step 3: Apply this code in code in the report level Fetch Method :


public boolean fetch()
{
str acc;

boolean ret;
// ret = super();
while select custTable
{
Tmtab_1.CustAccount = custTable.AccountNum;
Tmtab_1.doInsert();
this.send(Tmtab_1);
}
return true;
}

Different Types Of Joins And Link Types With example

Considering two Tables Car & Rental Table for example :

Car Table

CarId	ModelYear	CarBrand	Model	Mileage	ModelYear	CarId
CA101	2008	Mahindra	Scorpio	15	2008	CA101
CA102	2009	Suzuki	800	12	2009	CA102
CA103	2007	Hundai	i20	5	2007	CA103
CA104	2007	Toyato	Inova	3	2007	CA104
CA105	2009	BMW		5	2009	CA105
CA106	2010	Benz	AZ12	10	2010	CA106

Rental Table

RentalId	CustAccount	CarId	FromDate	ToDate	ToDate	FromDate
RE101	1104	CA101	10/12/2010	10/29/2010	10/29/2010	10/12/2010
RE102	1102	CA102	11/17/2010	12/14/2010	12/14/2010	11/17/2010
RE103	1203	CA103	11/24/2010	12/15/2010	12/15/2010	11/24/2010
RE104	1301	CA104	12/10/2010	1/5/2011	1/5/2011	12/10/2010
RE105	1304	CA101	1/3/2011	1/18/2011	1/18/2011	1/3/2011
RE108	1303
RE107	1202
RE106	2024	CA103	1/10/2011	1/29/2011	1/29/2011	1/10/2011

Inner Join

Select records from main table that have matching record form join table.

Main table record and join table records are joined together and show as single record.

Code

static void JoinExamples(Args _args)

{

CarTable carTable;

RentalTable rentalTable;

;

while select carTable join rentalTable

order by carTable.CarId

where carTable.CarId == rentalTable.CarId

{

info(strfmt("Car id is %1 renatla id is %2",carTable.CarId, rentalTable.RentalId));

}

}

Output

Outer Join

Select all the records from main table and related records from join table

If there is no match, the field from join table is empty

Code

while select carTable outer join rentalTable

order by carTable.CarId

where carTable.CarId == rentalTable.CarId

Output

Exists Join

Selecting record from main table only if there is matching record in join table.

It shoul be return only main table record, not a related table record.

Code

while select carTable Exists join rentalTable

order by carTable.CarId

where carTable.CarId == rentalTable.CarId

Output

Not Exists Join

Selecting record from main table only if there is not matching record in join table.

It is directly irrelevant to the Exists join.

Output

Link Type: Delayed

A pause is inserted before linked child data sources are updated. This enables faster navigation in the parent data source because the records from child data sources are not updated immediately.

Form Output

Form Design in Data Source Level:

Link Type: Active

The child data source is updated immediately when a new record in the parent data source is selected. Continuous updates consume lots of resources.

Link Type: Passive

Linked child data sources are not updated automatically. Updates of the child data source must be programmed on the active method of the master data source.

Writing Data to Text File in AX 2009 & 2012

static void DataToTxt(Args _args)
{
     PurchTable   purchTable;
     BinData     binData;
     TextBuffer  textBuffer;
   
     ;

    textBuffer = new TextBuffer();
    textBuffer.setText('');

    while select purchTable
            where purchTable.PurchStatus == PurchStatus::Backorder
     {
        textBuffer.appendText(strfmt('%1\r',purchTable.PurchId));
        textBuffer.appendText(strfmt('%1\r',purchTable.PurchName));
        textBuffer.appendText(strfmt('%1\r\n',purchTable.PurchStatus));

     }
     textBuffer.getText();
     binData = new BinData();
     binData.setStrData(textBuffer.getText());
     binData.saveFile(@"C:\Users\ADeveloper1\Desktop\batch.txt");

}

Exporting Data to Excel By Code

static void ExcelDocument(Args _args)
{
   SysExcelApplication  xlsApplication;
   SysExcelWorkBooks    xlsWorkBookCollection;
   SysExcelWorkBook     xlsWorkBook;
   SysExcelWorkSheets   xlsWorkSheetCollection;
   SysExcelWorkSheet    xlsWorkSheet;
   SysExcelRange        xlsRange;
   CustTable            custTable;
   int                  row = 1;
   str                  fileName;
   ;
  //Filename
      fileName = "C:\\Test.xlsx";
 //Initialize Excel instance
      xlsApplication           = SysExcelApplication::construct();
 //Open Excel document
 //xlsApplication.visible(true);
//Create Excel WorkBook and WorkSheet
     xlsWorkBookCollection    = xlsApplication.workbooks();
     xlsWorkBook              = xlsWorkBookCollection.add();
     xlsWorkSheetCollection   = xlsWorkBook.worksheets();
     xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
  //Excel columns captions
     xlsWorkSheet.cells().item(row,1).value("Account Num");
     xlsWorkSheet.cells().item(row,2).value("Name");
     row++;
  //Fill Excel with CustTable AccountNum and Name fields (only 10 records)
    while select custTable
    {
       if(row == 10)
         break;
       xlsWorkSheet.cells().item(row,1).value(custTable.AccountNum);
       xlsWorkSheet.cells().item(row,2).value(custTable.Name);
       row++;
    }
  //Check whether the document already exists
      if(WinApi::fileExists(fileName))
      WinApi::deleteFile(fileName);
 //Save Excel document
      xlsWorkbook.saveAs(fileName);
 //Open Excel document
      xlsApplication.visible(true);
 //Close Excel
 //xlsApplication.quit();
 //xlsApplication.finalize();
}

Filtering Data Based On a ComboBox in AX 2009

On your ComboBox you should set the property EnumType to PurchStatus, So that the enum's elements are automatically added as entries.

Later in selectionChange Override method of the ComboBox apply this code :

public int selectionChange()

{

    int ret;

    QueryBuildRange range;

    ;

    ret = super();

range=SysQuery::findOrCreateRange(PurchTable_ds.query().dataSourceTable(

tablenum(PurchTable)),fieldnum(PurchTable,PurchStatus));

    

range.value(queryValue(this.selection()));

PurchTable_ds.executeQuery();

return ret;

}