Create New Table On New Record Creation Of Another Table

This post will help you if you have requirement to create a new record every time when a new record is created in another table.

Let me explain this with an example:
Assume that you have a master table 'Bank_Account' which contains a field 'AccountNumber'. Now the requirement is  when a new Account Number is added (created) related to this Account Number a new table should be created as 'Transaction_####'. The name of the Transaction_#### would be dynamic.
If the Account Number is 1001 then Transaction_#### name should be Transaction_1001.

Follow the below steps:

• Create a table Bank_Account
• Add a field Account_Number (String)
• Set properties Mandatory : Yes , Allow Edit : No
• Make Account_Number as a Primary Key 
• PK : Go to Index node > New Index
  
  
  
• Field AccountNumber has now become primary key.

• Override modified field method in table Bank_Account and write following code.

 public void modifiedField(FieldId _fieldId)
{
    SysDictTable sysdictTable;
    Treenode treenode;// its a class
    AOTTableFieldList fieldnode;
    str Prefix,Acc,Tablename,prop;
    int pos,Account_NumberID;
    #AOT
    #Properties
    ;

    Account_NumberID = fieldNum(Bank_Account, Account_Number); // Getting Account_Number field ID
    super(Account_NumberID);
    this.insert();
    Prefix = "Transaction_";
    Acc = this.Account_Number;
    TableName= Prefix + Acc;

//#Table path refer the \\Data Dictionary\\Tables and finding the path
treenode = treenode::findNode(#TablesPath);
//AOTadd method is to add table in tables//TableName is table name
treenode.AOTadd(Tablename);
treenode = treenode.AOTfindChild(TableName);
treenode.AOTcompile(1);
treenode.AOTsave();
treenode.AOTfindChild(TableName);
fieldnode = treenode.AOTfirstChild();
fieldnode.addString('AccountNum');
fieldnode = fieldnode.AOTfindChild('AccountNum');
prop = fieldnode.AOTgetProperties();
pos = findPropertyPos(prop,#PropertyExtendeddatatype); //find right place to put extended data type
pos = strFind(prop,'ARRAY',pos,strLen(prop));
pos = strFind(prop,'#',pos,strLen(prop));
fieldnode.AOTsetProperties(prop);
treenode.AOTcompile(1);
treenode.AOTsave();
treenode.AOTRestore(); //to load assigned extended data type properties
sysdictTable = sysdictTable::newTreeNode(treenode);
appl.dbSynchronize(sysdictTable.id());

}

• Override insert method of table Bank_Account and write following code

public void insert()

{

    super();

    info("NewTable " + "Transaction_" + this.Account_Number + " has been created");

}

• An Account is added into Bank_Account

• Now go to AOT > Table node , a new table named 'Transaction_101' has been created.

                     Thank You !

Create Table Using X++ Code

static void autoTable(Args _args)
{
SysDictTable sysdictTable;
Treenode treenode;
AOTTableFieldList fieldnode;
str prop;
int pos;
#AOT
#Properties
;
//Coded by Gautam Verma

//#Table path refer the \\Data Dictionary\\Tables and finding the path
treenode = treenode::findNode(#TablesPath);
 
//AOTadd method is to add table in tables//AutoTableis table name
 
treenode.AOTadd('AutoTable');
treenode = treenode.AOTfindChild('AutoTable');
treenode.AOTcompile(1);
treenode.AOTsave();
treenode.AOTfindChild('AutoTable');
fieldnode = treenode.AOTfirstChild();
fieldnode.addString('AccountNum');
fieldnode = fieldnode.AOTfindChild('AccountNum');
prop = fieldnode.AOTgetProperties();
pos = findPropertyPos(prop,#PropertyExtendeddatatype); //find right place to put extended data type
pos = strFind(prop,'ARRAY',pos,strLen(prop));
pos = strFind(prop,'#',pos,strLen(prop));

fieldnode.AOTsetProperties(prop);
treenode.AOTcompile(1);
treenode.AOTsave();
treenode.AOTRestore(); //to load assigned extended data type properties
sysdictTable = sysdictTable::newTreeNode(treenode);
appl.dbSynchronize(sysdictTable.id());
}

                     Thank You !

Joins In Microsoft Dynamics Ax

There are basically four types of joins in Ax,

1.  Inner Join
2. Outer Join 
3. Exists Join
4. Notexists Join

Inner Join : Inner Join will return records from both Outer table and Inner table, only the records which are available in Inner table. Inner Join will also return duplicate records.

Outer Join : Outer Join will return all the records from both Outer table and Inner table. Outer Join will also return duplicate records.

Exists Join : Exists Join will return records only from Outer table which are available in Inner Table. It will not return any duplicate records. 

NotExists Join : NotExists Join will return records only from Outer table which are not available in Inner Table. It will not return any duplicate records.

So now you are clear with the definitions of multiple types of joins in ax. To understand how practically it works use the following steps.

• First of all create two tables in Dynamics Ax Demo_Outer and Demo_Inner.
• In Demo_Outer table create two fields
• Account (PK)
• Name
• In Demo_Inner table create two fields
• Account (FK)
• Phone
• Insert some records in both the tables

To test the joins create a job and fetch records from tables by using code.

Inner Join : Write the following code to test inner join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
          join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }

}

Inner Join Output :

Inner Join

Outer Join : Write the following code to test outer join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
       outer join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }
}

Outer Join Output : 

Outer Join

Exists Join :  Write the following code to test exists join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
       exists join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }
}

Exists Join Output : 

Exists Join

NotExists Join : Write the following code to test notexists join.

static void Demo_Joins(Args _args)
{
    Demo_Outer demo_Outer;
    Demo_Inner demo_Inner;
    while select demo_Outer
       notexists join demo_Inner
        where demo_Inner.Account == demo_Outer.Account
        {
            info(demo_Outer.Account +" :: "+ demo_Outer.Name +" :: "+ demo_Inner.Phone );
        }

}

NotExists Join Output : 

NotExists Join

                  Thank You !