Thursday, 19 May 2016

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;

    Account_NumberID = fieldNum(Bank_Account, Account_Number); // Getting Account_Number field ID
    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 = treenode.AOTfindChild(TableName);
fieldnode = treenode.AOTfirstChild();
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));
treenode.AOTRestore(); //to load assigned extended data type properties
sysdictTable = sysdictTable::newTreeNode(treenode);


  • Override insert method of table Bank_Account and write following code

public void insert()
    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.

                     ThanYou !

No comments:

Post a Comment