In this article we will discuss a very important and useful Dynamics Ax feature, Global Address Book. This article is good for both functional and technical consultants.
Note: This article is for advanced Dynamics Ax users. Good knowledge of Dynamics Ax and SQL server is required.
You can create global address book entry in two ways. First create address book record and then attach it to customer / vendor at the time of customer / vendor creation. Customer (same applies to other business entities as well) form actually do it automatically. If you specify the same Customer Name as of your address book, it will attach it correctly. Second, if you are creating customer first, then also, if Ax does not find name in address book, it will create one address book entry for the same.
Ok, It's good that you can share addresses but what about the cases where you don't want to share a particular address entry on address book between business entities. Yeah, there may be chances where you want to keep address private to your business entity. Do you see Public checkbox on customer form address tab?
Public / Private Address: Only public addresses are stored in global address book. Private addresses are stored with respect to the business entity. I will explain how all this stuff is handled in a short while.
In the above picture, you can see same addresses that you have created on your customer but not it's private address. And if you see grid at the bottom, it says that I am sharing the same address for customer 'RahulCust' and vendor 'RahVend'. Cool, hmmm!
Primary address: This is your primary address for the address book. You can create only one primary address per address book entry.
Techies Talk:
Below are the tables involved in Global Address Book (Public addresses) creation:
Most of the tables store some related information about the party. So, we will only discuss about the important ones used in creating links between address and business entities.
DirPartyTable, DirPartyECommunicationRelationship, DirPartyAddressRelationshipMapping, Address and for business entity we will use CustTable.
So, to define the relationship between Address and Customer, see SQL query below. It's a very simple SQL query which describes relationship between all these tables. Here you go...
--SQL Table name of Ax table DirPartyAddressRelationshipMapping is DIRPARTYADDRESSRELATIONSHI1066
Note: The above query will only work if you are not sharing DIR*, Address or CustTable. If you have any of these tables shared, then just change the Join and Where clause for DATAAREAID such that it points to right normal / virtual companies.
Below are the tables involved in the private addresses:
Note:
AddrTableId = Entity table id.
AddrRecId = Entity record id.
Now, after seeing these queries you should be able to define the relationship between address and any business entity in Ax.
Just for the quick note, there are three parts involved in Global Address Book.
Note: This article is for advanced Dynamics Ax users. Good knowledge of Dynamics Ax and SQL server is required.
Global Address Book: It 's a mechanism to share organization details, mainly communication and address, between different business entities in the company. For example, you may have a customer which is also a vendor; in this case there would be data redundancy or unnecessary duplication of address data if you had to enter the same address on customer and vendor. Wouldn't it be good, if you can store data once in Address table and share this between different business identities of the company. Yeah, sounds good. Solution to this problem is global address book or sometimes you call it directory.
This concept works on business parties. You create a business party in the global address book, defines various information regarding this, like addresses, communication details etc. You can create two types of parties, Organization (customer, vendor, business relation etc.) or Person (contact etc.).
All these parties are uniquely identified by PartyId in Ax. Ax uses this PartyId to connect address book to master records. Open customer form and go to general tab, you will see it.
This concept works on business parties. You create a business party in the global address book, defines various information regarding this, like addresses, communication details etc. You can create two types of parties, Organization (customer, vendor, business relation etc.) or Person (contact etc.).
All these parties are uniquely identified by PartyId in Ax. Ax uses this PartyId to connect address book to master records. Open customer form and go to general tab, you will see it.
You can create global address book entry in two ways. First create address book record and then attach it to customer / vendor at the time of customer / vendor creation. Customer (same applies to other business entities as well) form actually do it automatically. If you specify the same Customer Name as of your address book, it will attach it correctly. Second, if you are creating customer first, then also, if Ax does not find name in address book, it will create one address book entry for the same.
Ok, It's good that you can share addresses but what about the cases where you don't want to share a particular address entry on address book between business entities. Yeah, there may be chances where you want to keep address private to your business entity. Do you see Public checkbox on customer form address tab?
Public / Private Address: Only public addresses are stored in global address book. Private addresses are stored with respect to the business entity. I will explain how all this stuff is handled in a short while.
In the above picture, you can see same addresses that you have created on your customer but not it's private address. And if you see grid at the bottom, it says that I am sharing the same address for customer 'RahulCust' and vendor 'RahVend'. Cool, hmmm!
Primary address: This is your primary address for the address book. You can create only one primary address per address book entry.
Techies Talk:
Below are the tables involved in Global Address Book (Public addresses) creation:
- DirECommunicationAddress
- DirECommunicationTypeTable
- DirECommunicationTypeTxt
- DirOrganizationDetail
- DirParameters
- DirPartyAddressRelationship
- DirPartyAddressRelationshipMapping
- DirPartyECommunicationRelationship
- DirPartyRelationship
- DirPartyTable
- DirPersonGenerationalSuffixTable
- DirPersonPartyDetail
- DirPersonSalutationTable
- DirPrivacyGroupTable
- DirRelationshipTypeTable
- Address
- Entity table. (CustTable, VendTable etc.)
DirPartyTable, DirPartyECommunicationRelationship, DirPartyAddressRelationshipMapping, Address and for business entity we will use CustTable.
So, to define the relationship between Address and Customer, see SQL query below. It's a very simple SQL query which describes relationship between all these tables. Here you go...
SQL:
Select CUSTTABLE.ACCOUNTNUM, CUSTTABLE.PARTYID, DIRPARTYADDRESSRELATIONSHIP.SHARED, DIRPARTYADDRESSRELATIONSHIP.ISPRIMARY,
DIRPARTYTABLE.RECID as DirPRecId, DIRPARTYTABLE.DATAAREAID as DirPDatId,
DIRPARTYADDRESSRELATIONSHIP.RECID as DirPAdRelRecId, DIRPARTYADDRESSRELATIONSHIP.DATAAREAID as DirPAdRelDatId,
DIRPARTYADDRESSRELATIONSHI1066.RECID as DirPAdRelMapRecId, DIRPARTYADDRESSRELATIONSHI1066.DATAAREAID as DirPAdRelMapDatId,
ADDRESS.RECID as AddRecId, ADDRESS.DATAAREAID as AddDatId, ADDRESS.*
From CUSTTABLE
Inner Join DIRPARTYTABLE on CUSTTABLE.PARTYID = DIRPARTYTABLE.PARTYID
and CUSTTABLE.DATAAREAID = DIRPARTYTABLE.DATAAREAID
Inner Join DIRPARTYADDRESSRELATIONSHIP on DIRPARTYTABLE.PARTYID = DIRPARTYADDRESSRELATIONSHIP.PARTYID
and DIRPARTYTABLE.DATAAREAID = DIRPARTYADDRESSRELATIONSHIP.DATAAREAID
Inner Join DIRPARTYADDRESSRELATIONSHI1066 on DIRPARTYADDRESSRELATIONSHIP.RECID = DIRPARTYADDRESSRELATIONSHI1066.PARTYADDRESSRELATIONSHIPRECID
and DIRPARTYADDRESSRELATIONSHIP.DATAAREAID = DIRPARTYADDRESSRELATIONSHI1066.DATAAREAID
Inner Join [ADDRESS] on DIRPARTYADDRESSRELATIONSHI1066.REFCOMPANYID = ADDRESS.DATAAREAID
and DIRPARTYADDRESSRELATIONSHI1066.ADDRESSRECID = ADDRESS.RECID
and DIRPARTYADDRESSRELATIONSHI1066.DATAAREAID = ADDRESS.DATAAREAID
Where CUSTTABLE.ACCOUNTNUM = 'RahulCust' and CUSTTABLE.DATAAREAID = 'ceu'
--SQL Table name of Ax table DirPartyAddressRelationshipMapping is DIRPARTYADDRESSRELATIONSHI1066
Note: The above query will only work if you are not sharing DIR*, Address or CustTable. If you have any of these tables shared, then just change the Join and Where clause for DATAAREAID such that it points to right normal / virtual companies.
Below are the tables involved in the private addresses:
- Address
- Entity table. (CustTable, VendTable etc.)
Select * From ADDRESS
Where ADDRTABLEID = 77 and ADDRRECID = 5637144580
Note:
AddrTableId = Entity table id.
AddrRecId = Entity record id.
Now, after seeing these queries you should be able to define the relationship between address and any business entity in Ax.
Just for the quick note, there are three parts involved in Global Address Book.
- Entity Tables like, CustTable, VendTable etc.
- Global Address Book tables, all tables starting with Dir*. Already mentioned above.
- Address tables, tables used to store actual address like Address, AddressCountryRegion, AddressState, AddressZipCode etc.