Open table through code + ax 2012

static void OpenTableShareing(Args _args)

{
SysTableBrowser sysTableBrowser = new SysTableBrowser();
;

sysTableBrowser.run(tablenum(HCMWorker));
}

Table Inheritance + AX 2012

In Microsoft Dynamics AX 2012, tables can inherit, or extend, from the tables that are situated above them in a hierarchy. A base table contains fields that are common to all tables that derive from it. A derived table inherits these fields, but also contains fields that are unique to its purpose. Each table contains the Support Inheritance and Extends properties, which can be used to control table inheritance.

First we need understand that when we need to apply this inheritance methodology to tables. First we need to identify the Parent table and its siblings like

Here ‘Basic Info’ is the Parent table containing fields that are required for its child table i.e. CompnayTable and EmpTable. And these tables have their own fields as well.Now to design a Inheritance pattern we need to consider following important things:

• We can only apply inheritance on regular tables not on Temp or Memory tables
• A type discriminator field must be defined on any table inheritance hierarchy created in the AOT. The field must be defined as an int64 type on the root table, with the name of the field set to InstanceRelationType.
• The InstanceRelationType field of the root table is read-only and stores the TableIDs of record instances; it is populated automatically by Microsoft Dynamics AX 2012.
• Also we can only set the table properties for the table inheritance only when there are no fields in the table.
• If these requirements are not met, a compilation error will occur when the table inheritance hierarchy is compiled.

Let create parent Table ‘BasicInfo’

Setting its SuppourtInheritance Prpoerty to Yes

When you save and compile,it will give following error

For this we need to create a discriminator; name ‘InstanceRelationType’ and of type Int64.Now I have created a field including discriminator

Finally I have set another table property ‘InstanceRelationType’ to ‘InstanceRelationType’  field

And when I compiled, the error is gone now.

Now we have to create Two child tables

EmpTable:

First I have set the ‘SupportInheritance Property to Yes.

Now for the child table to have the parent we need to set the ‘Extends’ property of child table to parent table value like

And finally added all fields.

CompanyTable:

Same as what already we have done for EmpTable

Now when I compile it still give the two errors i.e.

Basically these errors indicates that the automatic relation that have been created in the child table,have the same relation name and so it is showing duplicate so for this I have changed the name See below screen shot

ScreenShot (see below)

 

And that’s it we have created Parent-child hierarchy table .

Finally, we need to also set the ‘Abstract’ property of the parent table to ‘Yes’ and for this we need to understand below.

Abstract versus concrete tables:

Tables in a table inheritance hierarchy can be defined as either abstract or concrete, depending on whether the table property Abstract is set to Yes or No. Records can only be created for concrete table types. Any attempt to create a record and insert it in an abstract table will result in a run-time error. The position of the table in the inheritance hierarchy does not restrict its ability to be defined as abstract.

Copying data of one table to another table across the company in AX

//Copying data of one table to another table across the company in AX2009

static void CopyData(Args _args)
{
    TableA                  tableA;
    TableB                  tableB;
    DataArea                dataArea;

    ;

  while select dataArea
     {

        changeCompany(dataArea.id)
        {
            tableA= null;
            tableB= null;
            while select tableA
            {
              tableB.CustAccount        = tableA.CustAccount;
              tableB.ItemCode           = tableA.ItemCode;
              tableB.insert();
            }
        }
     }

      info(strfmt("Mission Accomplished"));

Recover lost columns using SQL

This will be something even a novice DBA would know about, but hopefully it might be helpful for a Dynamics AX developer who isn't too familiar with SQL - but wants to learn
Get back a couple of columns that were lost due to a flawed client cache (apparently). This may also easily happen if someone accepts a database synchronization which creates data loss of specific columns.

The solution is quite easy. A DBA or developer with some SQL Server experience can prepare a restored database sitting on the same SQL Server Instance, or create a Linked Server for the purpose of this restore.

When the database is ready, make sure to keep users away while you restore the data. It should be a matter of minutes. I restored 3 columns in around 800 000 rows in just a few minutes. Keeping users away is just a precaution. SQL Server will by itself make sure the entire update either fails or succeeds.

Here is an example SQL query to restore the columns:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

BEGIN TRANSACTION RestoreColumnData WITH MARK N'Restoring Column Data'; GO UPDATE     SalesTableInTarget SET        SalesTableInTarget.CustomColumn1       = SalesTableInSource.CustomColumn1 ,            SalesTableInTarget.CustomColumn2       = SalesTableInSource.CustomColumn2 ,            SalesTableInTarget.CustomColumn3       = SalesTableInSource.CustomColumn3 FROM       DynamicsAXTarget.dbo.SALESTABLE            AS SalesTableInTarget            INNER JOIN DynamicsAXSource.dbo.SALESTABLE AS SalesTableInSource            ON         SalesTableInSource.RECID = SalesTableInTarget.RECID GO COMMIT TRANSACTION RestoreColumnData GO

The query simply joins the target table and the source table based on the RecId,which should be unique. I chose to mark the update in a custom transaction. 

You can find multiple examples of similar queries on the web, but sometimes it helps to put these things in our AX context.

Hope this helps someone.

Easy Extended AX Table-Browser + AX 2009

Developers open the tables from AOT in the Table-Browser and by hand attempt to add/delete the records by hitting Ctrl+N/Alt+F9 keys and often copies the records from the table-browser and paste them to excel for checking/saving/printing/... the table data to avoided by simply extending the Table-Browser by adding the Standard data Toolbar to the form

Step-1: Go to the AOT and select \Forms\SysTableBrowser\Designs\Design
and then change the design property WindowType value from “Popup” to “Standard”



Changing WindowType property value to “Standard”

Step-2: Save and compile the SysTableBrowser Form after changing the WindowType property value



The original Table-Browser (WindowType property is “Popup”)



Easy Extened Table-Browser with Standard-Toolbar (WindowType property is “Standard”)

That’s it, enjoy the easy extended table-browser with the standard toolbar with all table/record action features

How to limit inserting records in a table

Here is an example of a method in a form for limiting the number of records to 12 :

void Query_cntRecords()
{
Query query = new Query();
QueryRun queryRun;
QueryBuildDataSource qbd;
;

qbd = query.addDataSource(tablenum(Thy_InventSymbolsIcons));
queryRun = new QueryRun(query);

if (SysQuery::countTotal(queryRun) == 13)
{
info(strfmt("Only 13 records"));
}
}