web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Query to retrieve all ...
Microsoft Dynamics AX (Archived)

Query to retrieve all customers addresses

(0) ShareShare
ReportReport
Posted on by YiMeister 2,365

Hi,

I need help finding the relationships to query for all of the addresses for a specific customer. I know a customer can have more than one address. I also need the column in the address grid called "Purpose" . 

I tried using the DirPartyPostalAddressView view but it displays the addresses in multiple formats and unsure how to narrow it down to just 1 format. 

If I have the correct tables or relationships between them then that will be very helpful.

*This post is locked for comments

I have the same question (0)
  • TestBot Profile Picture
    TestBot 950 on at
    RE: Query to retrieve all customers addresses

    Thanq A-kow,

    I will look forward your query and check.

    I am another doubt regarding contact info for  the same customer

  • Suggested answer
    axk Profile Picture
    axk 925 on at
    RE: Query to retrieve all customers addresses

    The order of joining looks a bit chaotic for me, but check this code, it should work for you! : ) 

        CustTable               custTable;
    DirPartyTable           dirPartyTable;
    DirPartyLocation        dirPartyLocation;
    DirPartyLocationRole    dirPartyLocationRole;
    LogisticsLocation       logisticsLocation;
    LogisticsLocationRole   logisticsLocationRole;
    LogisticsPostalAddress  logisticsPostalAddress;
    
    
    while select custTable
            where   custTable.AccountNum == /* YOUR ACCOUNT */
        join dirPartyTable
                where   dirPartyTable.RecId == custTable.Party
        join dirPartyLocation
                where   dirPartyLocation.Party == custTable.Party
            join dirPartyLocationRole
                    where   dirPartyLocationRole.PartyLocation == dirPartyLocation.RecId
                join logisticsLocationRole
                        where   logisticsLocationRole.RecId == dirPartyLocationRole.LocationRole
                        &&      logisticsLocationRole.Type == /* YOUR ROLE - LogisticsLocationRoleType::Delivery*/    
            join logisticsLocation
                    where   logisticsLocation.RecId == dirPartyLocation.Location
                join logisticsPostalAddress
                        where   logisticsPostalAddress.Location == logisticsLocation.RecId
    
    {
        info(strFmt("%1", logisticsPostalAddress.Address));            
    }
  • TestBot Profile Picture
    TestBot 950 on at
    RE: Query to retrieve all customers addresses

    Hi a-kow,

           select firstonly logisticsLocation join logisticsPostalAddress where logisticsLocation.RecId == logisticsPostalAddress.Location

                       exists join dirPartyTable

                                    where dirPartyTable.PrimaryAddressLocation == logisticsLocation.RecId

                       exists join smmOpportunityTable

                                    where smmOpportunityTable.Party == dirPartyTable.RecId

                       exists join dirpartylocation

                                    where dirpartyLocation.Location == logisticslocation.RecId

                                       && dirpartyLocation.Party == smmOpportunityTable.Party

                       exists join dirpartylocationrole

                                    where dirpartyLocationRole.PartyLocation == dirpartyLocation.RecId

                       exists join logisticsLocationRole

                                    where dirpartyLocationRole.LocationRole == logisticslocationRole.RecId

                                    && logisticsLocationRole.type == LogisticsLocationRoleType::Business;

    Is this correct query? Pls suggest

  • Suggested answer
    axk Profile Picture
    axk 925 on at
    RE: Query to retrieve all customers addresses

    Hello Test Bot,

    The relation is:

    (...)

    DirPartyLocation.RecId <=> DirPartyLocationRole.PartyLocation

    DirPartyLocationRole.LocationRole <=> LogisticsLocationRole.RecId

    In LogisticsLocationRole table, you have role "Type" field.

  • TestBot Profile Picture
    TestBot 950 on at
    RE: Query to retrieve all customers addresses

    Hi,

    Kirsten Wotton

    Did you got the relation/query to fetch the customer address based on role?If so, please share the query. thanks in advance

  • Community Member Profile Picture
    Community Member on at
    RE: Query to retrieve all customers addresses

    Hi  Andre -

    I know this an old post but I am trying to create a list of addresses for a customer. I was able to do this by following your table suggestions and relationships. What I can't seem to find is the relationship for the LogisticsLocationRole table to the other tables listed so that I can add 'Type' to the query. It would also be helpful to have the DirPartyPostalAddressView relationship to these tables as I would like to use the Xrecversion_LogisticsPostalAddress. Thank you

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: Query to retrieve all customers addresses

    Hi André Arnaud ,

    I am currently extracting data from AX that will give me Primary, Billing and Shipping Address for a Retail Customer.

    You said -

    "The next tables are involved for addresses:

    CustTable (Party = DirPartyTable.RecId)

    DirPartyTable

    DirPartyLocation (Party == DirPartyTable.RecId and Location == LogisticsLocation.RecId)"

    ## My Question - Is this the Party address?  

    LogisticsLocation

    LogisticsPostalAddress (Location == LogisticsLocation.RecId)

    ## My Question - Is this the Shipping  address?  

    You said -

    "You can also have a look at the view DirPartyLocationRolesView.

    ## My Question - I want to get the Billling Address. Is this something i can get from DirPartyLocationRolesView by looking at the role?

    Your help is much Appreciated.

    Ananth Pillai

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: Query to retrieve all customers addresses
    Hi André Arnaud ,
     
     
    I am currently extracting data from AX that will give me Primary, Billing and Shipping Address for a Retail Customer. 
     
     
    You said - 

    "The next tables are involved for addresses:

    CustTable (Party = DirPartyTable.RecId)

    DirPartyTable

    DirPartyLocation (Party == DirPartyTable.RecId and Location == LogisticsLocation.RecId)"

    ## My Question - Is this the Party address?  

    LogisticsLocation

    LogisticsPostalAddress (Location == LogisticsLocation.RecId)

    ## My Question - Is this the Shipping  address?  

    You said -

    "You can also have a look at the view DirPartyLocationRolesView.

    ## My Question - I want to get the Billling Address. Is this something i can get from DirPartyLocationRolesView by looking at the role? 

    Your help is much Appreciated.

    Ananth Pillai

  • Vinai Varghese Profile Picture
    Vinai Varghese 150 on at
    RE: Query to retrieve all customers addresses

    you can use LogisticsPostalAddress for the address details of the customer.

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 298,911 Super User 2025 Season 2 on at
    RE: Query to retrieve all customers addresses

    Hi Yimeister,

    The next tables are involved for addresses:

    CustTable (Party = DirPartyTable.RecId)

    DirPartyTable

    DirPartyLocation (Party == DirPartyTable.RecId and Location == LogisticsLocation.RecId)

    LogisticsLocation

    LogisticsPostalAddress (Location == LogisticsLocation.RecId)

    The view you have used should be correct. Can you explain what you mean with multiple formats?

    If you want to include the purpose(s) you need to have a look into the table DirPartyLocationRole and LogisticsLocationRole. You can also have a look at the view DirPartyLocationRolesView.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Guy Terry Profile Picture

Guy Terry 2 Moderator

#1
Martin Dráb Profile Picture

Martin Dráb 2 Most Valuable Professional

#1
Community Member Profile Picture

Community Member 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans