How to create number sequence in ax at form and table level

OVERVIEW

Number sequences are unique identifiers that can be associated with a master record so that they can be individually distinguished. They can be either formatted as alpha-numeric strings or simply as numbers. Microsoft Dynamics AX 2012 provides an easy way to implement framework to generate custom number sequences. To create custom number sequence need to follow some steps:

STEPS

•  First create a new Extended Data Type (EDT). Open AOT→ Data Dictionary → Extended Data Types
• Right Click on Extended Data Types and create a new EDT ‘MyNumSeq’ of type String
  

•    Set the properties as shown below

• Now go to AOT → Classes and open the NumberSeqModuleCustomer class by right clicking it and selecting View Code

• In  the loadModule method, add the following code after the last line of code

//define the EDT
datatype.parmDatatypeId(extendedTypeNum(MyNumSeq));//define its default properties
datatype.parmReferenceHelp(literalStr(“Unique number for customer group”));
datatype.parmWizardIsContinuous(true);
datatype.parmWizardIsManual(NoYes::No);
datatype.parmWizardIsChangeDownAllowed(NoYes::No);
datatype.parmWizardIsChangeUpAllowed(NoYes::No);
datatype.parmWizardHighest(999999);
datatype.parmSortField(27);

//define its scope
datatype.addParameterType(NumberSeqParameterType::DataArea, true, false);
this.create(datatype);

·         Now, go to AOT → Jobs  and create a new job loadMyNumSeq

Write the following code in the job and then run it

static void loadMyNumSeq(Args _args){//define the class variable
NumberSeqModuleCustomer numSeqMod = new NumberSeqModuleCustomer();

//load the number sequences that were not generated
numSeqMod.load();}

• Now, go to  System Administration → Organization administration → Common → Number sequences → Number sequences

• Click on Number Sequence button in the New button group
  

•  In New Record change value of tabs according to your requirement.

Identification → Name and Code of Number Sequence
Scope parameter → Scope → company
                              →company → “Your company”

·          Set values of segment tab as shown in picture below:

•        Set values of General tab

•    Set values in Largest and continuous as shown in picture and press close button

•         Now click on Generate button and wait for some time it will create new number sequence.

• In the Setup number sequences wizard, Press Next

• In the last step, Click Finish to generate the number sequences

•    Now check your generated number sequence by typing name of your number sequence in filter

•    Now goto Account Receivable , expand setup and click on account receivable parameters

•    Now click on Number Sequence (left below as visible in picture) , in reference column find mynumseq (EDT) and in number sequence code column find mynum (your number sequence code)

•     Create a new table, if you already have a table where you want to use number sequence add a new string field and set properties as follows

•  Create a new form, if you already have a form in which you want to use number sequence then  simply add the table to form’s data source.

•  Write the following code on the Class declaration node

          NumberSeqFormHandler numberSeqFormHandler;

•   Create a new method on the form and write the following code

  NumberSeqFormHandler numberSeqFormHandler()   {      if (!numberSeqFormHandler)
   {

//create a reference of number sequence form handler class specifying the         EDT, Data source name and the field of the table

numberSeqFormHandler =NumberSeqFormHandler::newForm(NumberSeqReference::findReference
(extendedtypenum(NumSeqDemoCustGroupNum)).NumberSequenceId, element,MyNumSeqTable_DS,fieldnum(MyNumSeqTable,MyNumSeq));
}return numberSeqFormHandler;
}

•       Override the close method of the form and write the following code

public void close(){if (numberSeqFormHandler){
numberSeqFormHandler.formMethodClose();
}

super();}

•      Override the create method on the MyNumSeqTable data source and add the following code

public void create(boolean _append = false){element.numberSeqFormHandler().formMethodDataSourceCreatePre();

super(_append);

element.numberSeqFormHandler().formMethodDataSourceCreate(true);}

•       Override the write method on the MyNumSeqTable  data source and add the following code

public void write(){super();

element.numberSeqFormHandler().formMethodDataSourceWrite();}

•     Override the validateWrite method on the MyNumSeqTable  data source and add the following code

public boolean validateWrite(){boolean ret;

ret = super();

ret = element.numberSeqFormHandler().formMethodDataSourceValidateWrite(ret)&& ret;

return ret;}

•     Override the delete method on the MyNumSeqTable  data source and add the following code

public
void delete(){

element.numberSeqFormHandler().formMethodDataSourceDelete();

super();}

•          Override the linkActive method on the MyNumSeqTable  data source and add the following code

public
void linkActive(){

element.numberSeqFormHandler().formMethodDataSourceLinkActive();

super();}

•     Now open form.

• The number in number sequence will be automatically created every time you create a new record in form.

Filtering the data(record) based on selected Input(ComboBox) in Ax

For filtering a particular record    :

1.First create a table  i.e TableName   with fields name , Ids(Base enum) ,  Phno , Age

2.Create a form and drag the table TableName into data source.
in design take a grid and a combo box   give enum type as baseenum 

3.In class:
       public class FormRun extends ObjectRun
       {
             QueryBuildRange qbr2;  //  declartion of range variable
       }

4.In Data source level . Override a method init()

1. public void init()
   {
         super();
        qbr2 = this.query().dataSourceName(“Datasourcename”).addRange(fieldNum(TableName, BaseEnum1));
   }

5.in combo box – in methods override a selectionchange method()
        public int selectionChange()
        {
               int ret;
               ret = super();
               datasourcename_ds.executeQuery();
               return ret;
        }

6.Create a range by  overriding method executequery() in data source level: 
       public void executeQuery()
       {
           qbr2.value(comboboxName.valueStr());
           super();
       }

Note

     Set Auto Declaration :Yes for Combo box

How many Tables are Present under AOT through X++ Code

static void TotalTablesinAx(Args _args)
{
    int x;
    x = TreeNode::findNode('\\Data Dictionary\\Tables').AOTchildNodeCount();
    info(strFmt("Total Tables in AOT = %1",x));
 
}

How To Set Grand Total property in Auto Design SSRS

Expand  Design Node,expand Auto designed node.

•  Expand Data Node
• Select the Field( Here I Selected AmountMST), & Go to Properties.
• In Aggregation Property, Set it to "Sum".

• After Setting Property

   

How to Filter Data on one field Between Two Dates in X++

     In Data Source, Override Execute Query Method and Copy Below Code

    public void executeQuery()
   {
          date  fromdate = today()-7;
          date   todate       = today();
                   this.query().dataSourceTable(tablenum(InventTrans)).addRange(fieldnum(InventTrans,DatePhysical)).value(queryRange(fromdate,todate));

   }

Basic Important SQL Keywords in Ax 2012

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 expression from  both tables. (inner join)
	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().