Database error in AX

Error : Cannot select a record, Name: 0. The SQL database has issued an error.

Solution : System Administration  →  Periodic → Database → SQL administration → Table Actions → Synchronize database.

How to create number sequence in ax at form level

CREATE NUMBER SEQUENCE

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.

How to use Reference Group in a form in AX

Ø  Reference Group control of a form will show the User needed fields like Name or any other specific field instead of a RecId but will save only RecId upon user selection in the backend. To use this Feature we need to use Replacement Key of a Table.

Ø  Index Specified in Replacement key of a table will be shown instead of RecId in the form. Following Example will briefly explain the creating and use of Replacement Key and Reference Group Field Control of a Form.

Ø  Important: This concept will only work upon having the table relations with RecId, as it’s a MS Design Framework.

v  Create two tables

• Parent_Table
•  Child_Table

v  Create three fields in Parent_Table

•     AccNo(String)
•    Name(String)
•  Phone(String)

o    Create an index in Parent_Table

NameIdx

Properties:

AllowDuplicates :NO

Alternatekey      :Yes

o    Drag drop name field of Parent_Table into NameIdx index.

o    Parent_Table
Properties

                  Replacementkey    : NameIdx

v  Create Three fields in Child_Table

• Name(Int64)
•  Address(String)
•  ZipCode(String)

o    Create a relation between Name field of Child_Table and RecId of Parent_Table.

Relations of two Tables:

Set field                : Name
Set RelatedField : RecId

v  Create a form

•   ReferenceGroupForm

o    Add Parent_Table and Child_Table to datasource of form.

o    Select join source fo child datasource and set it to Parent_Table.

o    Create 2 grids (for parent_Table and Child_Table)

o    Set Grid1 datasource : Parent_Table

o    Set Grid2 datasource: Child_Table

•     Add fields of Parent_Table data source to grid1.
•    Add fields of  Child_Table data source to grid2.

NOTE: While creating referecegroup, use Int64 datatype for that field which is used. Because we need to create a relation between a field of child_table(Name) and parent_table(RecId). We know that RecId is Int64 type. Reference Group concept will only work on RecIds.

OUTPUT:  As we can see in form now, top grid is containing data from parent table and below grid

is containing data from child table. When we create new record in child table it will show all the

name record from parent table instead of RecId(Related field). But in  backend(Child_Table) it will save the recId of Parent_Table.

• Now, In picture it is clear how the value coming in front end (form) and save in backend (table)