Customer Addresses - How to join the tables?

(0) Share

Report

Posted on by Community Member

Hello,

I recently upgraded from 4.0 to AX2012 and I'm trying to build a simple report in Crystal to show Customer Account, Name, Address, but I'm struggling with table joins and hope someone would be kind enough to steer me in the right direction.

I am joining CustTable to DirPartyTable by fields (PARTY > RECID)

DirPartyTable to LogisticsLocation (PRIMARYADDRESSLOCATION > RECID)

LogisticsLocation to LogisticsPostalAddress (RECID > LOCATION)

...but I'm getting double records - I have tried all kinds of different join types but the results are the same.
Are my joins even correct? I would really appreciate any direction on this.

Thank you!

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

LaneSwenka 361 on at

Like (2)

Report

Hi Drumcode,

You may find it more straightforward to use the Views that are provided by Microsoft:

CustTable.Party -> DirPartyLocationPrimaryView.Party
DirPartyLocationPrimaryView.Location -> LogisticsPostalAddressView.Location

You will always want to add a where-clause for dataAreaId to limit by company the customer is released, in case you are using the party system to link the same account across multiple legal entities.

Was this reply helpful? Yes No
Suggested answer

Bilal Issa 4,370 on at

Like (0)

Report

Hi ,

you missed one table only DirPartyLocation , so DirPartyTable to DirPartyLocation (RecId ,Party), then DirPartyLocation to LogisticsLocation (location,recid)

Regards,

Bilal

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ok, I've used your suggestion and something still isn't correct. I am expecting to see 2077 records, but instead Crystal spits out 2174, but that's only after I added the street field from LogisticsPostalAddressView. If Custtable.AccountNum and DirPartyTable.Name fields are on the report, the total number of records is just fine. I've tried all types of link types, the number of records stays the same. Do I have to provide anything else in the selection criteria besides the DATAAREAID? We're only one entity btw.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Was this reply helpful? Yes No
LaneSwenka 361 on at

Like (0)

Report

Can you send us your updated query?

Another thing to be aware of is that LogisticsPostalAddress, and the related View are ValidTimeState based.

Perhaps add another where-clause with the following:

where (getdate() BETWEEN addressView.ValidFrom AND addressView.ValidTo)

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Check the following blogpost for fetching addresses of Customer, Vendor etc by address type.

thinkinginax.blogspot.com/.../getting-address-by-type.html

Hope this helps you

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

I went with using the views. Afnan, I'd love to find out how to join tables per your suggestions, but I'm not all that good with reading X++. Thanks for the input though.

I absolutely hate how MS complicated the whole address structure in 2012, hate it.

Was this reply helpful? Yes No