Microsoft Dynamics AX (Archived)

Table relation

(0) Share

Report

Posted on by Community Member

Dear AX forum,

I have a question, perhaps someone can help me.

How do we obtain table relations programmatically?

Can we do that with TreeNode class?

What about the reverse.

For example,

in PurchLine Table there's a relation to PurchTable table

What about the reverse? Can we find which tables are related to PurchTable?

Thanks

Steven

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (0)

Community Member on at

Like (2)

Report

Hi Stephen,

You can use the reverse engineer tool (Tools >Reverse Engineer). This will show you relations between tables.

Anyway, why do you want to do that?

Was this reply helpful? Yes No
Suggested answer

Denis Macchinetti 16,444 on at

Like (4)

Report

Hi

You have to use the DictRelation Class.

Take a look at patrikluca.blogspot.it/.../find-all-relations-on-dynamics-ax-table.html

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Okay...

There's some other things that I'd like to do to AX Database which aren't in AOS.

Sometimes I have to code outside AOT.

For example, I need some quick reports that I prefer to do it in SQL Server Query Window, not from AOS reporting service.

That's why the easiest way to do it, is to know all table relations.

Other reason: I'm learning Dynamics AX, and the fastest way to study it, is by knowing table relations.

Thanks for your advice Mr. Musau.

Is there anyway we do it programmatically from a new form?

Sincerely

Steven

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Oh, sorry. There are two replies. Thanks Mr. Macchinetti

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Hi Stephanus,

The link Denis has shared may be able to achieve what you want using X++ code. You just modify the output from a .csv file to a table so that you can be able to display it on a form.

To get new relations you rerun the process, but first delete all records before reinserting the new ones.

Was this reply helpful? Yes No
ArunGarg 3,926 on at

Like (0)

Report

Hi Denis,

Thanks for sharing a useful information.

Regards

Arun

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Dennis for your reply.

Before I go further, may I ask. What this procedure is supposed to do?

In AOT we can see, for example PurchLine related to PurchTable, what about the REVERSE?

Are we able to extract what tables are related to PurchTable? And it returns PurchLine

Because in AOT we only see one way. PurchLine is related to PurchTable.

Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (2)

Report

First, note that the AOT relationship information is not used by AX (it is for documentation purposes only) and is not always correct.

As one example, the AOT says LedgerJournalTrans_Asset.RefRecId and LedgerJournalTrans.RecId have an ExactlyOne-to-ExactlyOne relationship, which neither makes sense nor matches the data in the database (there can be, and in our system there are, LedgerJournalTrans rows without related rows in the LedgerJournalTrans_Asset).

You said you were doing this for SQL Server queries. I have a SQL Server script that returns the relationships for any specified table, regardless which side of the relationship(s) the specified table is on.

However, before the script will work, a one-time run of some X++ code I wrote has to be done. The code walks the AOT > Data Dictionary > Tables nodes and captures relationship info into a SQL Server table (the AOT relationships are good enough for my purposes).

If you are interested ask.

PS: Another issue I ran into when trying to do SQL Server queries against the AX business database was enums. So I did a similar thing: wrote X++ code that is run one time and walks the enum nodes and saves the info to a SQL Server table. Then I have a scalar-valued function that I used to convert from the AX enum int value to the text.

Was this reply helpful? Yes No
Srinath Sundaresan 510 on at

Like (1)

Report

Hi Stephanus

You can look at the ERD diagrams for all the tables in Ax here. Here you can see the parent child relation ships centered around a process in Ax.

www.microsoft.com/.../ax2012r2

Please mark this question as answered if it helps.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Unfortunately, the Microsoft's ERDs do not contain info for all tables in AX, nor do they contain enough information for those that are there.

For fixed assets there are only 2 ERDs. Looking at the main one (that posts to the GL) centered on AssetBookTable, there are a lot of tables missing: AssetLedger, AssetLedgerAccounts, AssetAdditions, AssetDepreciationProfile, AssetDepreciationProfileSpec, AssetGroupBookSetup, and AssetLocation are those I noticed.

And for the 7 tables that are in the ERD, the relationships don't indicate the cardinality ratio (1:1 or 1:M, etc.) or the participation constraints (required or optional in the relationship).

Plus, the site would be hard to use in a SQL Server query.

Was this reply helpful? Yes No