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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Define customer accoun...
Microsoft Dynamics AX (Archived)

Define customer accounts with invoice purpose email

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I'm trying to find which customers in our system have an email address with the invoice purpose. I found LOGISTICSELECTRONICADDRESS in SQL, which shows addresses in the LOCATOR column, but I can't work out how to see which of the addresses are set to invoice purpose.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Sefa Duman Profile Picture
    Sefa Duman 1,167 on at

    Hi,

    AX has global address book framework for address needs. And global address book framework uses many normalized tables to store address information. You have to know the relations between tables. I wrote T-SQL code for your need:

    SELECT cust.ACCOUNTNUM, location.*

    FROM LOGISTICSLOCATION location

    INNER JOIN DIRPARTYLOCATION partyLocation ON location.RECID=partyLocation.LOCATION

    INNER JOIN CUSTTABLE cust ON cust.PARTY=partyLocation.PARTY

    INNER JOIN DIRPARTYLOCATIONROLE partyLocationRole ON partyLocationRole.PARTYLOCATION=partyLocation.RECID

    INNER JOIN LOGISTICSLOCATIONROLE logisticsLocationRole ON partyLocationRole.LOCATIONROLE=logisticsLocationRole.RECID

    WHERE logisticsLocationRole.[TYPE]=1

    This code can help you. In the code "logisticsLocationRole.[TYPE]=1" means the purpose type is invoice.

    I hope this will be usefull.

  • Community Member Profile Picture
    Community Member on at

    Hi Sefa,

    The results returned by this query have a Statement purpose email address, but not an Invoice purpose email. I tried to modify it to {TYPE}= 0,1,2,3,4 but they did not relate to Invoice purpose either unfortunately.

  • Sefa Duman Profile Picture
    Sefa Duman 1,167 on at

    Hi Joshua,

    You may check LogisticsLocationRole table and find which one is invoice purpose. Type field uses LogisticsLocationRoleType enum. Maybe the enum's values are different on your AX instance.

  • Verified answer
    Sefa Duman Profile Picture
    Sefa Duman 1,167 on at

    Meanwhile, I confused email with postal address. If you want to find e-mail information you can use T-SQL like this:

    select cust.ACCOUNTNUM, eAddress.*

    from LOGISTICSELECTRONICADDRESS eAddress

    INNER JOIN DIRPARTYLOCATION partyLocation ON partyLocation.LOCATION=eAddress.LOCATION

    INNER JOIN CUSTTABLE cust ON cust.PARTY=partyLocation.PARTY

    INNER JOIN LOGISTICSELECTRONICADDRESSROLE eAddressRole ON eAddress.RECID=eAddressRole.ELECTRONICADDRESS

    INNER JOIN LOGISTICSLOCATIONROLE locationRole ON locationRole.RECID=eAddressRole.LOCATIONROLE

    where locationRole.[TYPE]=1 and eAddress.[TYPE]=2

    You may change locationRole.[TYPE] filter based on invoice purpose in LogisticsLocationRole table. And eAddress.[TYPE]=2 means e-mail type in LOGISTICSELECTRONICADDRESS  table.

  • Community Member Profile Picture
    Community Member on at

    Hi Sefa,

    I think that was the problem, because the results now look correct. Thank you very much!

    Josh

  • Community Member Profile Picture
    Community Member on at

    For a similar issue I'm having, I tried to alter this query to show customer email addresses against account number, but filtered to not show duplicate values in the email addresses. I used this but I didn't quite work:

    select distinct cust.ACCOUNTNUM, eAddress.*

    from LOGISTICSELECTRONICADDRESS eAddress

    INNER JOIN DIRPARTYLOCATION partyLocation ON partyLocation.LOCATION=eAddress.LOCATION

    INNER JOIN CUSTTABLE cust ON cust.PARTY=partyLocation.PARTY

    INNER JOIN LOGISTICSELECTRONICADDRESSROLE eAddressRole ON eAddress.RECID=eAddressRole.ELECTRONICADDRESS

    INNER JOIN LOGISTICSLOCATIONROLE locationRole ON locationRole.RECID=eAddressRole.LOCATIONROLE

    where LOCATOR LIKE '%@%' and eAddress.ISPRIMARY = '1'

    Am I along the right line for this?

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

Quick Links

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans