Can We Find Primary - Foreign Key Mapping In AX?

Hi All,

I’m migrating entire data from one ERP to another. To insert records into AX Tables, I need to know the master tables, insert data into them, then the transaction tables. But the references are not created at the database level, so one can’t identify the master tables simply by running an SQL at the DB.

Is there any way to find out the Primary - Foreign Key mappings in AX and insert the data in the desired sequence or I have to manually check for every column and create the map?

AX uses a concept of relations for this purpose. Relations defines mapping between fields in two tables (composite relations are supported, field can even be mapped to a static value, e.g. a relation may be valid just for a specific type of journal). Relations can be defined on tables or on extended data types (e.g. SalesId data type has a relation to SalesTable table in all tables where SalesId is used as data type for a field).

You can get such information from AX metadata, usually by Dict* and SysDict* classes. I’ll give you an example, anyway this is a task for an AX developer…

TableId tableId1 = tableNum(VendTrans);
TableId tableId2 = tableNum(VendInvoiceJour);
DictRelation        dictRelation;
DictField    field, relatedField;
int i;
setPrefix(strFmt("%1 -> %2", tableId2Name(tableId1), tableId2Name(tableId2)));
dictRelation = new DictRelation(tableId1);
dictRelation.loadTableRelation(tableId2);
if (dictRelation.lines())
{
    for (i = 1; i <= dictRelation.lines(); i++)
    {
        field = new DictField(tableId1, dictRelation.lineTableValue(i));
        relatedField = new DictField(tableId2, dictRelation.lineExternTableValue(i));
        info(strFmt("%1 -> %2",field.name(DbBackend::Sql), relatedField.name(DbBackend::Sql)));
    }
}

You should get the following result:

VOUCHER → LEDGERVOUCHER
ACCOUNTNUM → INVOICEACCOUNT
TRANSDATE → INVOICEDATE

Change table IDs to get what you need.

By the way, relations were somewhat changed for AX2012, please mention your AX version if you have any additional questions.

Hi Martin,

Thank you so much for the code! This has really helped!