Find Relations en related fields in AX 2012

(0) Share

Report

Posted on by mcronald

Hello, i am new to ax 2012.

I want to make a report in SSRS with a bunch of data from our customers:

AccountID, Name, street, postcode, city, representitive, contactperson, email, telephone number, dataareaID and partition.

I think i want to make a view and then a query with that view, or only a query. (i do not know what the best way is for this matter)

So that i can link that query to SSRS if i make a dynamics report.

In ax 2012 i know that i can go to accounts receivable --> All customers.

Then i can put my cursor on every field and press F12 so i can see in which table that field is hiding.

But if i do that for all the neccesary field i just got a list of table names, but i do not have the slitest idea how i can link this tables together. because i don't know for example how to get from de custtable to de logistiscs electronic addresses table so i can get de Phone number and email en so go on for every table.

I can look at every table and see which tables are related, but the final table is not listed there, ( i think because it is linked and linked and linked again trough many tabels, maybe there are 20 tables between them)

Is there some simple trick for this i can understand so i can to this trick every time in the future if i want any table to join with any table.

Next thing is, getting the relations between 2 fields in different tables.

I also have found a website from microsoft with talbe entity relations, but that is useless in my case.

I have allready searched on this forum but did not find any answers which solves my problem.

I hope you have any idea.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

André Arnaud de Cal... 301,075 Super User 2025 Season 2 on at

Like (0)

Report

Hi McRonald,

If you have updated the basic cross references (also used on advanced query forms to link tables) you can find the table relations in the table xRefTableRelation.

This has a table and related table field as well as the relation name. This relation name can have one or more fields which are defined on the table relations.

Was this reply helpful? Yes No
Suggested answer

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report
I do agree with André.

its kind of effort you have to do, either by tools or you can navigate over table Relations Node for each related table and have a look how those are related to each other.

In addition to that, if you visit you any List page or form you can view the query being used behind that, which also shows you relationships. Suppose in your case you want to see data of Customer Master, so directly navigate to Customer List Page at following path

//Accounts receivable/Common/Customers/All customers

Now right click in the gird and click Personalize.

Reach to third tab of form opened (which is named as Query)

You can find most of relationships there for a certain entity or for any form.

After looking at relationship you have choice to create a visual query and use that in your report or create a RDP class and use that in your Report.

I am going to do little effort for you for this time. You have to do this effort yourself next time. in case if you do face any issues, while finding relationships among tables, you can come on community and people can answer that.

Note that in below sample code, I were having choice to include display methods, but I am avoiding it, just because in case if you are not aware of display methods and also, those make a report slow.

Hopefully you will do some struggle, and will come up with some results

CustTable CustTable; DirPartyTable DirPartyTable,DirPartyTableWokerName,DirParyTableCPName; LogisticsPostalAddress LogisticsPostalAddressPrimary; HcmWorker HcmWorker; ContactPerson ContactPerson; LogisticsElectronicAddress LogisticsElectronicAddressPhone,LogisticsElectronicAddressEmail; ; while select AccountNum from CustTable where (CustTable.AccountNum == 'US-040' || CustTable.AccountNum == 'US-028') outer join Name from DirPartyTable where DirPartyTable.RecId == CustTable.Party outer join Street,ZipCode,City from LogisticsPostalAddressPrimary where LogisticsPostalAddressPrimary.Location == DirPartyTable.PrimaryAddressLocation outer join PersonnelNumber from HcmWorker where HcmWorker.RecId == CustTable.MainContactWorker outer join Name from DirPartyTableWokerName where DirPartyTableWokerName.RecId == HcmWorker.Person outer join Party from ContactPerson where ContactPerson.ContactPersonId == CustTable.ContactPersonId outer join Name from DirParyTableCPName where DirParyTableCPName.RecId == ContactPerson.Party outer join Locator, LocatorExtension from LogisticsElectronicAddressPhone where LogisticsElectronicAddressPhone.RecId == DirPartyTable.PrimaryContactPhone outer join Locator from LogisticsElectronicAddressEmail where LogisticsElectronicAddressEmail.RecId == DirPartyTable.PrimaryContactEmail { info(strFmt( 'AccountNum: %1 Name: %2 Street: %3 PostalCode: %4 City: %5 RepresentativeId: %6 RepresentativeName: %7 ContactPersonId: %8 ContactPersonName: %9 Phone: %10 Email: %11', CustTable.AccountNum, DirPartyTable.Name, LogisticsPostalAddressPrimary.Street, LogisticsPostalAddressPrimary.ZipCode, LogisticsPostalAddressPrimary.City, HcmWorker.PersonnelNumber, DirPartyTableWokerName.Name, CustTable.ContactPersonId, DirParyTableCPName.Name, LogisticsElectronicAddressPhone.Locator+' '+LogisticsElectronicAddressPhone.LocatorExtension, LogisticsElectronicAddressEmail.Locator )); }

Was this reply helpful? Yes No
mcronald on at

Like (0)

Report

Thanks Andre, i found the table,

Sohaib, thank you, i found the query tab.

I have to more questions regarding Sohaib answers.

You told me to look at the query tab.

I found all tables i want except one. That is the V ISPrimary thats a mark for the primary address. because if i do not use that i see like 5 addresses for one customer.

If i look at the query tab i see only Primary

DirPartyPostalAddressView(DirPartyPostalAddressView).IsPrimary

But this is in an apart column so you do not see the relations with the other tables.

I went on: /Accounts receivable/Common/Customers/All customers --> Then dubbelclick on a customer --> personalize.

The second thing.

I saw your query. What can i do with that query?

I thought i put it in a job, but it did not work, so maybe you can explain to me how i can use that query?

Was this reply helpful? Yes No
Verified answer

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report
Every party (customer, vendor etc.) may has more than one address. But only one address can be primary at a time.

On other hand each address has valid date/time, after that it’s expired. So each address can be expired or can be active.

Regarding query which I gave you, it’s just for your understanding about relationships among tables and how you can get data. You can test it using job.

If you are unable to get any results using above job that probably because of fact that you are ignoring Customer ID range which I added as per my environment

where (CustTable.AccountNum == 'US-040' || CustTable.AccountNum == 'US-028')

Was this reply helpful? Yes No
psined on at

Like (0)

Report
Hi Sohaib,

in your example you have used this relation:

LogisticsElectronicAddressEmail.RecId == DirPartyTable.PrimaryContactEmail

Can you please tell me in which form/tab/group/control is this field being set/maintained? I've scoured customer form inside out and couldn't find this field actively used anywhere.. The cross-reference add-in only shows it is assigned in DirPartyMerge::runOnServer() static method. Please advise.

On another hand there is also [isPrimary] field on LogisticsElectronicAddress table.

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

Hi psined,

we would appreciate if you can describe your issue in a new thread by mentioning your business needs and what you are looking for. This can help us to answer you :)

Was this reply helpful? Yes No
psined on at

Like (0)

Report

Hi Sohaib, I didn't want to start new thread, because my inquiry goes inline with the discussion in this post. So I was only following up on one of your comments. But I can start a new thread. After reading my further comments please let me know if that's what you want me to do.

The table relations show me (at least) 2 different ways to connect primary electronic address to a party - one by means of LogisticsElectronicAddress.IsPrimary field, another by DirPartyTable.PrimaryElectronicEmail (also fax, phone, etc.). On top of that there is also DirPartyLocation.IsPrimary field (but I assume this one is used for postal addresses only). While I'm able to see where LogisticsElectronicAddress.IsPrimary is set and maintained, I cannot track down the same for DirPartyTable.PrimaryElectronicEmail. As I mentioned previously the cross-reference add-in only finds one place where it gets set - DirPartyMerge::runOnServer() method, other objects either use it for read access only or do not seem to use it at all.

I'm missing background on AX versions prior to 2012. Unfortunately MSFT documentation in MSDN is very sparse and very few blogposts exist delving under the shell of contact information relations in AX 2012. But I have found with the help from other threads (see my thread on relations between ContactPerson/CustTable) that there are several legacy keys slowly being deprecated and removed as a result of normalization. So I'm wondering if this is one of those "legacy" cases for Primary flags or not. You have referred to DirPartyTable.PrimaryElectronicEmail in your code snippet above. I was hoping if you could clarify which objects in AX 2012 R3 set and maintain this particular field. Hope this makes more sense to you and sorry for any confusion.

Was this reply helpful? Yes No