Passing Args from Form to Class in Dynamics Ax 2012

In this post, i will demonstrate on how to pass Args from Form to Class in Dynamics Ax.

This post will helpful for those who are keen to learn about Args which is very much fruitful concept in Dynamics Ax.

The Scenario is when I select multiple grids in Form , those records will be passed to Class and once you received those records in Class and you can use it as per your Requirement.

Firstly, make one form from which you will pass the arguments and drag your table to the datasource, then drag the fields to the grid of design node, also drag one button adn override the clicked method.

write the following code in clicked method.
void clicked()
{
int recordsCount;
SampleTbl _SampleTbl;
container con;
Args args;
str multiSelectString;
;

args = new Args();
recordsCount = SampleTbl_ds.recordsMarked().lastIndex(); // gets the total records selected
_SampleTbl = SampleTbl_ds.getFirst(1);

while (_SampleTbl)
{
// storing recid of selected field in container
con = conIns(con,1,_SampleTbl.RecId);

// converting container to string with comma separated
multiSelectString = con2Str(con,',');

_SampleTbl = SampleTbl_ds.getNext(); // moves to next record
}
// passing string
args.parm(multiSelectString);
// calling menu item
new MenuFunction(identifierstr(SampleTest), MenuItemType::Action).run(args);


}


make one class and write the following code in main method of it.
public static void main(Args args)
{
SampleTbl _SampleTbl;
container con;
int i;
str multipleRecords;
;
multipleRecords = args.parm();
info(strFmt('%1',multipleRecords));
// i am just here an infolog of recids but you can do as per requirement once you got multiple records
}

thanks and your feedback will be appreciated

Passing Multiple Records from one form to another through Args in Dynamics Ax 2012

In my last post, i posted on how to pass Record from one Form to another Form. Now i will pass multiple Record from one Form to another through Args.

The Scenario is when I select multiple grids in Form A, those records will be passed to FormB and there FormB will received the all those records and filter it and will show.

Step 1: Make one SampleTable which has three fields named (SIno, name, AddressCity).
Step 2: Make FormA with datasource as SampleTable and drag the datasource fields into the design Grid.
Step 3: Drag one button and override the click method and write the following code.
step 4: Make one Display menuitem of FormB.

void clicked()
{

int recordsCount;
SampleTable _sampleTable;
container con;
Args args;
str multiSelectString;
;


args = new Args();
recordsCount = SampleTable_ds.recordsMarked().lastIndex(); // gets the total records selected
_sampleTable = SampleTable_ds.getFirst(1);

while (_sampleTable)
{
// storing recid of selected field in container
con = conIns(con,1,_sampleTable.RecId);

// converting container to string with comma separated
multiSelectString = con2Str(con,',');

_sampleTable = SampleTable_ds.getNext(); // moves to next record
}
// passing string
args.parm(multiSelectString);
// calling menu item
new MenuFunction(menuitemDisplayStr(FormBMenuItem), MenuItemType::Display).run(args);
}

step 5: Make FormB with datasource as same table for which you take for Form A i.e., SampleTable and drag the fields in to the
Grid of design part.
step 7: write the following code in to the init method of FormB.

public void init()
{
container con;
int i;
str multipleRecords;
super();
// getting string value from caller
multipleRecords = element.args().parm();

// string to container
con = str2con(multipleRecords,",");

// for sorting
for(i = 1;i<= conLen(con) ;i++)
{
SampleTbl_ds.query().dataSourceTable(Tablenum(SampleTbl)).addRange(fieldNum(SampleTbl,RecId)).value(SysQuery::value(conPeek(con,i)));
}

}

thanks and your feedback will be appreciated.

Passing Args from one Form to another Form in dynamics Ax 2012

Args concept in dynamics ax is awesome. one can easily pass arguments from one Form to another Form with very less effort. Here, i am going to demonstrate how to pass Arguments from Form to Form. The Scenario is when I select one grid in Form A, that record will be passed to Form B and there Form B will received the whole record and filter it and will show.

Step 1: Make one SampleTable which has three fields named (SIno, name, AddressCity).
Step 2: Make FormA with datasource as SampleTable and drag the datasource fields into the design Grid.
Step 3: Drag one button and override the click method and write the following code.

void clicked()
{
Args args;
FormRun formRun;
;
super();
args = new args(formstr(FormB)); // sending Args(record) to FormB
args.record(SampleTable);
formrun = classfactory.formrunclass(args);
formrun.init();
formrun.run();
formrun.wait();
formrun.detach();

}





step 4: Make FormB with datasource as same table for which you take for Form A i.e., SampleTable and drag the fields in to the
Grid of design part.
step 5: write the following code in to the init method of FormB.

public void init()
{
SampleTable _sampleTable;
super();
_sampleTable = element.args().record();
SampleTable_ds.query().dataSourceTable(Tablenum(SampleTable)).addRange(fieldNum(SampleTbl,RecId)).
value(SysQuery::value(_sampleTable.Recid));
}

Note: You can pass only one Record with this Code, if you want to pass multiple grids,i will cover in next post.

thanks and your feedback will be appreciated.

Using Tree in Form

1. Firstly, create three tables with the following requirements.

1.1 Create a table (parent table) name: Medicine

Fields type Edt
MedName str
MedDesc str

1.2. Create a table (reference table) name: MedReferencehrr

Fields type Edt
MedRef int64 Medref = reference table: Medicines
DtOwn int64

Relation:
1. ownrelation : MedReferencehrr i.e, DtOwn == recid
2. Foreign key rel : Medicines MedRef = MedReferencehrr .recid



1.3 Create a table( child table ) name : MedDetails

Fields type Edt

MedRef int64 Medref = reference table: Medicines
Name1 str
Desc1 str

Relation: Normal: MedDetails .MedRef = Medicines .recid

1.4 Create a new Form named SampleTree2 and drag 3 tables in the form DS. The following code
must written as it is.


Medicines no relation
MedDetails join source: Medicines type: active
MedReferencehrr
In design: add group in group add a Tree name it as: SampleTree2

Public class FormRun extends ObjectRun
{

boolean isCreatedNew;
boolean isModified;
TreeItemIdx newItemIdx;
TreeItemIdx lastRecordIdx;
int64 lastRecordRecId;
String10 billingGroupRecid;


//
String10 billingeGroupHierarchyRecid;
int64 billingGroupHierarchyParentRecid;


//
QueryBuildRange qbrMedecines;

QueryBuildRange qbrMedReferencehrr;

//
List delRecList;
//

ImageListAppl_HISServices imageAppl;
#define.Root(3)
}
1. Init method :

public void init()
{
super();
imageAppl = new ImageListAppl_HISServices();
imageAppl.addImage(resourceStr(SampleTree),#Root);
SampleTree.setImagelist(imageAppl.imageList());
}

2. Run method :
public void run()
{
super();
if( Medecines.RecId == 0)
{
AddNewService.enabled(true);
}
element.loadTree();

}
3. Loadtree method:

public void loadTree()
{
TreeItemIdx parentItemIdx;

SampleTree2.deleteAll();

parentItemIdx = this.createRootNode();

this.createChildNodes(parentItemIdx,0);

SampleTree2.select(SampleTree2.getRoot());
SampleTree2.expand(SampleTree2.getRoot());
}

4. createRootNode method :

public TreeItemIdx createRootNode()
{
FormTreeItem parentItem;

parentItem = new FormTreeItem("Medicines");
parentItem.data(0);

//parentitem.image(imageAppl.image(#Root));
// parentitem.selectedImage(imageAppl.image(#Root));

returnSampleTree2.addItem(FormTreeAdd::Root,FormTreeAdd::First,parentItem);
}

5. createChildNodes method :

public void createChildNodes(TreeItemIdx _parentIdx, int64 _serviceGroupHierarchyParentRecid)
{
MedReferencehrr MedReferencehrrLocal;
MedReferencehrr MedReferencehrrChild;
Medecines medecines1;
FormTreeItem parentItem;
TreeItemIdx newParentIdx;

while select * from MedReferencehrrLocal
join * from medecines1
where MedReferencehrrLocal.DtOwn == _serviceGroupHierarchyParentRecid
&& MedReferencehrrLocal.MedRef == medecines1.RecId
{

select count(recId) from MedReferencehrrChild
where MedReferencehrrChild.DtOwn == MedReferencehrrLocal.RecId;

if(MedReferencehrrChild.RecId > 0)
{
parentItem = new FormTreeItem();
parentItem.data(MedReferencehrrLocal.RecId);
parentItem.text(medecines1.MedDesc);


newParentIdx = SampleTree2.addItem(_parentIdx,FormTreeAdd::Sort,parentItem);
if(MedReferencehrrLocal.RecId == lastRecordRecId)
{
lastRecordIdx = newParentIdx;
}
element.createChildNodes(newParentIdx,MedReferencehrrLocal.RecId);
}
else
{
parentItem = new FormTreeItem();
parentItem.data(MedReferencehrrLocal.RecId);
parentItem.text(medecines1.MedDesc);


newParentIdx = SampleTree2.addItem(_parentIdx,FormTreeAdd::Sort,parentItem);
if(MedReferencehrrLocal.RecId == lastRecordRecId)
{
lastRecordIdx = newParentIdx;
}
}

}

}



Now add action panel to this tree group . in button group add two buttons type : button name :AddBelow and delete .
In AddBelow button : clicked method :
void clicked()
{
super();
element.addNode(SampleTree2.getSelection());
}
In form Methods : add the method:
addNode method :

public void addNode(TreeItemIdx _parentIdx)
{
FormTreeItem newItem;
FormTreeItem parentItem;

newItem = new FormTreeItem("");
newItem.data(0);
//newItem.image(imageAppl.image(#NormalGroup));
//newItem.selectedImage(imageAppl.image(#NormalGroup));

newItemIdx = SampleTree2.addItem(_parentIdx, FormTreeAdd::Sort,newItem);

SampleTree2.select(newItemIdx);

Medecines_ds.create();

parentItem = SampleTree2.getItem(_parentIdx);
billingGroupHierarchyParentRecid = parentItem.data();
isCreatedNew = true;

AddBelow.enabled(false);
}

In Delete button : clicked method :
void clicked()
{
//super();
FormTreeItem treeItem;

treeItem = BillingTree.getItem(BillingTree.getSelection());

if(element.deleteBillingGroup(treeItem.data()))
{
element.loadTree();
}

}


In form Methods : add the method:
deleteBillingGroup method :
ignore error and cont
public boolean deleteBillingGroup(recId _recId)
{
Medecines medecines2;
MedReferencehrr medReferencehrrlocal;

DialogButton dialogButton;
ListEnumerator enumer;
boolean allDeleted = true;

if(_recId)
{
medReferencehrrlocal = MedReferencehrr::find(_recId);


dialogButton = Box::yesNo('Selected billing group and its subsidary group will be deleted', DialogButton::No);
if( dialogButton == DialogButton::Yes )
{
try
{
delRecList = new List(Types::Int64);

delRecList.addStart(medReferencehrrlocal.MedRef);

element.deleteGroupCode(_recId);

enumer = delRecList.getEnumerator();

ttsBegin;

while(enumer.moveNext())
{
select forupdate medecines2
where medecines2.RecId == enumer.current();

if(medecines2.validateDelete())
{
medecines2.delete();
}
else
{
allDeleted = false;
break;
}
}

if(allDeleted)
{
ttsCommit;
return true;
}
else
{
ttsAbort;
return false;
}

}
catch
{
return false;
}
}
else
{
return false;
}
}


else
{
return false;
}
}
--
In Table MedReferencehrr(In Table not in Form) Create a new method ,write the following code :

public static MedReferencehrr find(
RecId _recid,
boolean _forUpdate = false,
ConcurrencyModel _concurrencyModel = ConcurrencyModel::Auto)
{
MedReferencehrr medReferencehrr ;
medReferencehrr.selectForUpdate(_forUpdate);

if(_forUpdate && _concurrencyModel != ConcurrencyModel::Auto)
{
medReferencehrr.concurrencyModel(_concurrencyModel);
}

select firstonly * from medReferencehrr where medReferencehrr.RecId == _recid;

return medReferencehrr;
}


------
Come Back to Form and Create a New Method In form name : deleteGroupCode


public void deleteGroupCode(recId _parentrecId)
{
MedReferencehrr medReferencehrrChild;
MedReferencehrr medReferencehrrTraverse;

while select * from medReferencehrrTraverse
where medReferencehrrTraverse.DtOwn == _parentrecId

{

select count(recId) from medReferencehrrChild
where medReferencehrrChild.DtOwn == medReferencehrrTraverse.RecId;

if(medReferencehrrChild.RecId > 0)
{
delRecList.addEnd(medReferencehrrTraverse.MedRef);
element.deleteGroupCode(medReferencehrrTraverse.RecId);

}
else
{
delRecList.addEnd(medReferencehrrTraverse.MedRef);
}

}
}


In Form DesignDesigngroupTree SampleTree2 --right click – override method – SelectionChanged :

public void selectionChanged(FormTreeItem _OldItem, FormTreeItem _NewItem, FormTreeSelect _how)
{
super(_OldItem, _NewItem, _how);

if(SampleTree2.getRoot() == SampleTree2.getSelection())
{
AddNewService.enabled(false);
}
else
{
AddNewService.enabled(true);
}

if(isCreatedNew)
{
if(isModified)
{
if(SampleTree2.getSelection() != newItemIdx)
{
//if(HMServiceCatalog_ds.validateWrite())
//{
//HMServiceCatalog_ds.write();
//}
//else
//{
SampleTree2.select(newItemIdx);
//}
}

}
else
{
SampleTree2.delete(newItemIdx);
newItemIdx = 0;
if(Medecines_ds.validateWrite())
{
Medecines_ds.write();
}
isCreatedNew = false;
isModified = false;
AddBelow.enabled(true);
billingeGroupHierarchyRecid = element.getCurrentSelectedItemRecid();
MedReferencehrr_ds.executeQuery();
billingGroupRecid = int642str(MedReferencehrr.MedRef);
Medecines_ds.executeQuery();

}
}
else
{
billingeGroupHierarchyRecid = element.getCurrentSelectedItemRecid();
MedReferencehrr_ds.executeQuery();
billingGroupRecid = int642str(MedReferencehrr.MedRef);
Medecines_ds.executeQuery();

}
}



Now design the form. Take another Group , take action panel with two method,
Name Prop
AddNewService command : New
DeleteNewService command : DeleteRecord

Select a new grid and drag some fields from the Table : MedDetails

In Form DataSource :
1. Medecines

public void init()
{
super();

qbrMedecines = this.query().dataSourceTable(tableNum(Medecines)).addRange(fieldNum(Medecines,recId));
}
public void write()
{
MedReferencehrr MedReferencehrrLocal;

super();

if(isCreatedNew == true)
{
MedReferencehrrLocal.MedRef = Medecines.RecId;
MedReferencehrrLocal.DtOwn = billingGroupHierarchyParentRecid;
MedReferencehrrLocal.insert();

newItemIdx = 0;
billingGroupHierarchyParentRecid = 0;
isCreatedNew = false;
isModified = false;
AddBelow.enabled(true);

element.refresh(MedReferencehrrLocal.RecId);
}
else
{
element.refresh(MedReferencehrr.RecId);
}
}
public void executeQuery()
{
qbrMedecines.value(billingGroupRecid);

super();
}

2. MedReferencehrr

public void init()
{
super();

qbrMedReferencehrr = this.query().dataSourceTable(tableNum(MedReferencehrr)).addRange(fieldNum(MedReferencehrr,recId));
}

public void executeQuery()
{
qbrMedReferencehrr.value(billingeGroupHierarchyRecid);

super();
}






Lastly Create a new method in Form Method :

public void refresh(recId _lastRecid)
{
lastRecordRecId = _lastRecid;

element.loadTree();

SampleTree2.select(lastRecordIdx);
}






Now : In order to Sort the Tree Based on the following upper grid then :

1. Create a new Table MainTableMed
Add few fields
In Table Medecines :
Add relation  new relation give table : MainTableMed;
Right click foreign key PrimaryKey
A field will be generated .

2. In the Form Drag Table : MainTableMed into Datasource ..
InDatasourceTable: Medecines Properities
Join Source = MainTableMed
Link Type = active

In Form DataSource Table MainTableMed  override methods :

public void create(boolean _append = false)
{
super(_append);

SampleTree2.deleteAll();
AddBelow.enabled(false);
}

public void delete()
{
super();

if(MainTableMed.RecId == 0)
{
SampleTree2.deleteAll();
AddBelow.enabled(false);
}

}
public int active()
{
int ret;

ret = super();

if(ret)
{
if(MainTableMed.RecId)
{
element.loadTree();
AddBelow.enabled(true);
}
else
{
SampleTree2.deleteAll();
AddBelow.enabled(false);
}
}

return ret;
}


To work this Moddify the form method : CreateChildNodes


Now design the Form  take Group , add action panel contains New and Delete record Methods and a Grid : pro -datasource : MainTableMed , add few fields

Microsoft Dynamics Ax Macros

In MorphX macros are not commonly used. A few places make use of macros such as keeping track of the list of fields stored when using dialogs. It is recommended only to use macros to define constants. Also supports code, but is not recommended as reusing code from macros is not flexible.

The main difference between a macro and a method is that a macro has no variable declaration part, and the code in a macro is not validated for errors before executed from a method. This is another reasons for not putting code in macros.

When using macros in your code, the macros must be declared after the variable declaration. And the common place to put the definition is in the ClassDeclaration of a class, form or a report. This will make the macro definition available for all parts of the object.

Macro commands:

For writing macros a set of simple macro commands are used. Below are few examples.

Command	Description
#define	Used to define a constant.
	AxExample: See macro HRMConstants.
	Syntax: #define.myConstant100(’100′)
#if.empty	Will return true if the macro has not been called with the parameter validated in the statement.
#if.notempty	Will return true if the macro has been called with the parameter validated in the statement.
	AxExample: See macro InventDimJoin
	Syntax: #if.notempty(%3) print %3; #endif
#endif	Ends a #if.empty or a #if.notempty statement.
	AxExample: See macro InventDimJoin
	Syntax: #endif (following any if statement)
#globalmacro	No difference whether declaring the start of a macro with #localmacro or #globalmacro. #globalmacro is not used in the standard package, consider using #localmacro instead.
#localmacro	Specify the start of a local macro.
	AxExample: See macro BOM
	Syntax: #localmacro.BOMDateSelect (((BOM.fromDate <= %1 || ! BOM.fromDate) && (BOM.toDate >= %1  || ! BOM.toDate))  || ! %1 ) #endmacro
#endmacro	Ends a #LOCALMACRO or a #GLOBALMACRO.
#linenumber	Returns the current line number of the macro. Can be used while debugging, but not of must use.
#macrolib	Used to load an AOT macro from code.
	AxExample: See class method BOMHierarchy.searchDownBOM()
	Syntax: #macrolib.MyMacro
#undef	Undefine a constant declared with #DEFINE. A defined constant cannot be used if #undef is called with the define name.
	Syntax: #define.MyConstant(100) print #MyConstant; #undef.MyConstant print #MyConstant; // will fail, as #MyConstant is not defined.

Defining Constants:

Instead of using text in your code it is strongly recommend defining your text as constants. Often you will need an integer or a text for setting a value. If you are going to set RGB color it is not easy to read the following:

myStringColor(255, 255, 255)

Instead you should consider defining a constant with a descriptive name:

myStringColor(#RBGColorWhite)

Using a macro as a constant rather than entering the value in code makes it easier tomaintain. A good way to organize the constants used in your modifications is by creating a macro in the AOT for keeping all your constants in one place, one good example is HRMConstants.

Creating Macros:

Macros are either created directly in the code, or put in an AOT macro and then the AOT macro is declared in the code.

Example(1): #localmacro created in the code

static void Macros_LocalMacro(Args _args)

{

CustTable custTable;

;

#localmacro.selectCustTable  //Macro definition starts here

#ifnot.empty(%1)

while select %1

#ifnot.empty(%3)

order by %3

#endif

{

info(queryValue(%1.%2));

}

#endif

#if.empty(%1)

info(“No table specified.”);

#endif

#endmacro  //Macro definition ends here

#selectCustTable(CustTable, accountNum)  //Calling Macro with valid parameters

#selectCustTable  //Calling Macro with no parameters – output will be text “No table specified” as per the validation.

}

The macro will select records from a table ‘CustTable’ and print a field from the same as the table to be fetched, and the fields (%1, %2, %3) to be printed are specified in the parameters for the first Macro call.
In the second Macro call, the table has not been specified so text will be printed to the Infolog.

As you cannot declare variables in a macro, integer values prefixed with a percentage sign such as %1 are used instead. This is the common way of using the macros. The validations must be before calling the macro. Notice that you can call a macro without entering the parentheses after the macro name.

Example(2): Create and use an AOT Macro

If your macro is going to be used in several places it would make sense creating the macro in the AOT as you will then be able to reuse the macro.
To create a new macro in the AOT:

1.       Unfold the Macro node, right-click and choose New Macro.

2.      A new empty macro node will be created. You can rename the new macro by opening the property sheet to “MyFirstMacro”

3.      Now paste the code of the #localmacro (from above) to your new macro.

4.      Save the Macro, and it is ready to use.

To use the macro created in the AOT the macro command #macrolib is used. Here the AOT macro is name MyFirstMacro, and it can be used in the code as shown below.

static void Macros_MacroLib(Args _args)

{

CustTable custTable;

;

#macrolib.MyFirstMacro

#selectCustTable(CustTable, accountNum)

#selectCustTable

}

Even this will result in the same output as explained above, the only difference is that we are calling a Macro created in AOT using #macrolib command.

Posted by Ajit kumar G at 10/31/2012 01:33:00 PM

Using tree controls

X++ Implementation of few SQL Keywords

As we already saw in the Previous post, In X++, we can directly use database related statements similar to any other code. Here, we will see and understand how to implement few well-known SQL keywords in X++.

Example(1): To print records in custTable in descending order.
while select custTable order by custTable.AccountNum desc
{
print(strfmt(“%1, %2″, custTable.accountNum, custTable.Address));
}
pause;

Output:

By using order by clause similar to the SQL queries with the keyword ‘desc’, we are able to achieve the expected output.

Example(2): To print Average of Amount from custTrans table.
while select avg(AmountMST) from custTrans
{
print(custTrans.AmountMST);
}
pause;

Output:

Here, we are using while select statement with the Aggregate keyword ‘avg’ on the required field to get the Average of all the values of AmountMST in the table.
Also note that to print the output, You can get the result from the same field, i.e, custTrans.AmountMST.

The below are Details, Syntax and an AX Example respectively for few well-known SQL keywords:

Keyword	Example
asc	Set the sorting order to ascending. All selects are default fetching data ascending.
	Syntax: select custTable order by accountNum asc;
desc	Set the sorting order to descending. Used in combination with order by or group by.
	Syntax: select custTable order by name desc;
	AX Example: See table method CustTable.lastPayment().
avg	Select uses aggregate keyword (avg) using only one call to the database calculating a result based on multiple records
	Syntax: select avg(amountMST) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
count	Aggregate keyword used to count the number of records fetched.
	Syntax: select count(recId) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
sum	Aggregate keyword used to sum values of a field fetched.
	Syntax: select sum(amountMST) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
maxof	Aggregate keyword used to return the highest field value fetched
	Syntax: select maxOf(amountMST) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
minof	Aggregate keyword used to return the lowest field value fetched.
	Syntax: select minOf(amountMST) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
delete_from	Will delete multiple records in one call to the database.
	Syntax: delete_from myTable where myTable.amountMST <=’1000′;
	AX Example: See class method InventCostCleanUp.updateDelSettlement().
exists join	Exists join is used to fetch records where at least one record in the secondary table matches the join expression. No records will be fetched from the secondary table using exists join.
	Syntax: while select custTable exists join custTrans where custTable.accountNum == custTrans.accountNum
	AX Example: See class method InventAdj_Cancel.cancelInventSettlements().
notexists join	Opposite of exists join. Will fetch records from the primary table, where no records in the secondary table match the join expression.
	Syntax: while select custTable notexists join custTrans where custTable.accountNum == custTrans.accountNum
	AX Example: See class method InventConsistencyCheck_Trans.run().
outer join	Outer join will select records from both tables regardless if there are any records in the secondary table matching the join expression.
	Syntax: while select custTable outer join custTrans
	AX Example: See class method SysHelpStatistics.doTeams().
join	Join will fetch Records matching the join expressionfrom both tables. (innerjoin)
	Syntax: while select custTable join custTrans where custTable.accountNum == custTrans.accountNum
	AX Example: See table method SalesTable.LastConfirm().
firstfast	Instruct to select the first record faster. used in situations where only one record is shown, like in a dialog.
	Syntax: select firstfast custTable order by accountNum;
	AX Example: See class method ProjPeriodCreatePeriod.dialog().
firstonly	First record will be selected. Firstonly should always be used when not using while in selects.
	Syntax: select firstonly custTable where custTable.AccountNum == _custAccount (variable)
	AX Example: See Table method CustTable.find().
forupdate	Used If records in a select are to be updated
	Syntax: while select forupdate reqTransBOM where reqTransBOM.ReqPlanId     == this.ReqPlanId
	AX Example: See Table method ReqTrans.deleteExplosionCoverage().
from	Default all fields of a table is selected. From is used to select only the fields specified. Use it for optimization only, as it makes the code more complex.
	Syntax: select accountNum, name from custTable;
group by	Sort the fetched data group by the fields specified. Only the fields specified in the group by will be fetched.
	Syntax: while select custTable group by custGroup;
	AX Example: See class method InventStatisticsUS.calcTotals().
index	Used to set the sorting order of the fetched data. The kernel will convert the keyword index to an order by using the fields from the index. Index should only be used if the fetched data must be sorted in a specific way, as the database will choose a proper index.
	Syntax: while select custTable index accountIdx.
index hint	Index hint will force the database to use the specified index.
	Syntax: while select custTable index hint accountIdx.
	AX Example: See Table method ReqTrans.deleteExplosionCoverage().
insert_recordset	Used to insert multiple records in a table. Insert_recordset is useful when copying data from one table to another as it only requires one call to the database
	Syntax: insert_recordset myTable (myNum,mySum) select myNum, sum(myValue) from anotherTable group by myNum where myNum <= 100;
	AX Example: See class method SysLicenseCodeReadFile.handleDomainLicenseChanges().
update_recordset	Used  to update multiple records in one database call. Useful to initialize fields in a fast way. The fields updated are specified after thekeyword setting.
	Syntax: update_recordset myTable setting field1 = myTable.field1 * 1.10;
	AX Example: See class method ProdUpdHistoricalCost.postScrap().

Parent Link: Using Select statement in X++ to retrieve data

Posted by Ajit kumar G at 10/18/2012 05:09:00 PM 

Labels: Dynamics Ax, SQL Keywords for X++