SQL query to pull multiple addresses for Vendors in AX 2009
I am trying to create a query that will be able to pull multiple addresses for Vendors out of Dynamics AX 2009. I have found a lot of what I need on the VendTable, but not all. I have about 500 Vendors with around 150 that have at least 2 addresses. Right now what I'm able to pull is just the first address.
Here is my query so far:
SELECT ven.CREATEDDATETIME
,'Organization' [Party, Record Type]
,VEN.NAME [Party, Name]
,VEN.NAMEALIAS [Search Name]
,VEN.LANGUAGEID [Party, Language]
,VEN.NAME [Party Address, Name]
,'Business/RemitTo' [Party Address, Purpose]
,VEN.COUNTRYREGIONID [Party Address, Country/Region]
,VEN.ZIPCODE [Party Address, SIP/postal code]
,VEN.STREET [Party Address, Street]
,VEN.CITY [Party Address, City]
,VEN.[STATE] [Party Address, State]
,CON.NAME [Party Contact, Description]
,'Phone' [Party Contact, Type]
,CON.PHONE [Party Contact, Locator]
,'' [Party Contact, Is Primary]
,VEN.ACCOUNTNUM [AccountNum]
,VEN.VENDGROUP [VendGroup]
,VEN.PAYMMODE [PaymMode]
,VEN.PAYMTERMID [Paym Term Id]
,VEN.CASHDISC [CashDisc]
,VEN.DLVMODE [Deliver Mode]
,VEN.DLVTERM [DlvTerm]
,VEN.W9 [W9]
,VEN.TAX1099REPORTS [Tax 1099 Report]
,VEN.TAX1099BOX [Tax 1099 Box]
,'' [Tax 1099 Name Choice]
,VEN.DBA [DBA]
,VEN.TAX1099REGNUM [Tax 1099 Reg Num]
,VEN.INVENTSITEID [Invent SiteId]
,VEN.INVENTLOCATION [Invent Location]
,VEN.TAXGROUP [Sales Tax Group]
, *
FROM VENDTABLE VEN
LEFT JOIN CONTACTPERSON CON ON con.VENDACCOUNT = ven.ACCOUNTNUM
WHERE ven.CREATEDDATETIME > '2018-04-30'
ORDER BY VEN.ACCOUNTNUM
After this, I need to find where the addresses are stored and how they are connected so that I can get both addresses when there are more than 1.
I did find a table Address that seems to have them all, but I cannot figure out how this table is connected to the VendTable.
Also is there a way to find what table is connected to a form in AX 2009? I have found some of the information that I need is in the Global Address Book, but that is not a table in the SQL DB.
Update
On the advice of @TEEKAY below, I looked up the Datamodel for AX 2009 in an attempt to find a connection between the Address table and the VendTable table. All I could find was:
However, this is not working. The Address table AddrRecId does match up to the VendTable RecId, but not on rows that would actually match data wise. Both tables have a field Name that holds the name of the vendor company like All Phase. When matched using the Address.AddrRecId = VendTable.RecId I would get something like this:
As you can see the Names don't match. But the Address.AddrRecId = VendTable.RecId do match.
I finally figured it out! It is the DirPartyTable that connects the 2 tables like this:
SELECT *
FROM VENDTABLE VEN
LEFT JOIN DIRPARTYTABLE DIR ON DIR.PARTYID = VEN.PARTYID
LEFT JOIN ADDRESS [ADD] ON [ADD].ADDRRECID = DIR.RECID AND [ADD].ADDRTABLEID = 2303
The final [ADD].ADDRTABLEID = 2303 was needed to limit the results to just the VendTable, there are 2 other ADDRTABLEID's, but I believe they are for the CustTable and possibly the Contact table.
It seems that Microsoft documentation is horribly outdated!
So the whole query is now like this:
SELECT 'Organization' [Party, Record Type]
,VEN.NAME [Party, Name]
,VEN.NAMEALIAS [Search Name]
,VEN.LANGUAGEID [Party, Language]
,VEN.NAME [Party Address, Name]
,CASE [ADD1].TYPE WHEN 0 THEN ''
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Delivery'
WHEN 3 THEN 'Alt. Delivery'
WHEN 4 THEN 'SWIFT'
WHEN 5 THEN 'Payment'
WHEN 6 THEN 'Service'
WHEN 7 THEN 'Home'
WHEN 8 THEN 'Other'
WHEN 9 THEN 'Business'
WHEN 10 THEN 'Remit-To'
WHEN 11 THEN 'Third-party Shipping Address'
WHEN 100 THEN 'Remit-To'
END AS [Party Address, Purpose]
,[ADD1].COUNTRYREGIONID [Party Address, Country/Region]
,[ADD1].ZIPCODE [Party Address, SIP/postal code]
,[ADD1].STREET [Party Address, Street]
,[ADD1].CITY [Party Address, City]
,[ADD1].[STATE] [Party Address, State]
,CASE WHEN CON.NAME IS NULL THEN NULL ELSE CON.NAME END AS [Party Contact, Description]
,'Phone' [Party Contact, Type]
,VEN.PHONE [Party Contact, Locator]
,'' [Party Contact, Is Primary]
,VEN.ACCOUNTNUM [AccountNum]
,VEN.VENDGROUP [VendGroup]
,VEN.PAYMMODE [PaymMode]
,VEN.PAYMTERMID [Paym Term Id]
,VEN.CASHDISC [CashDisc]
,VEN.DLVMODE [Deliver Mode]
,VEN.DLVTERM [DlvTerm]
,VEN.W9 [W9]
,VEN.TAX1099REPORTS [Tax 1099 Report]
,VEN.TAX1099BOX [Tax 1099 Box]
,'' [Tax 1099 Name Choice]
,VEN.DBA [DBA]
,VEN.TAX1099REGNUM [Tax 1099 Reg Num]
,VEN.INVENTSITEID [Invent SiteId]
,VEN.INVENTLOCATION [Invent Location]
,VEN.TAXGROUP [Sales Tax Group]
,[ADD].NAME [Party Address, Name]
,CASE [ADD].TYPE WHEN 0 THEN ''
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Delivery'
WHEN 3 THEN 'Alt. Delivery'
WHEN 4 THEN 'SWIFT'
WHEN 5 THEN 'Payment'
WHEN 6 THEN 'Service'
WHEN 7 THEN 'Home'
WHEN 8 THEN 'Other'
WHEN 9 THEN 'Business'
WHEN 10 THEN 'Remit-To'
WHEN 11 THEN 'Third-party Shipping Address'
WHEN 100 THEN 'Remit-To'
END AS [Party Address, Purpose]
, [ADD].COUNTRYREGIONID [Party Address, Country/Region]
, [ADD].ZIPCODE [Party Address, ZIP/Postal code]
, [ADD].STREET [Party Address, Street]
, [ADD].CITY [Party Address, City]
, [ADD].[STATE] [Party Address, State]
FROM VENDTABLE VEN
LEFT JOIN DIRPARTYTABLE DIR ON DIR.PARTYID = VEN.PARTYID
LEFT JOIN ADDRESS [ADD] ON [ADD].ADDRRECID = DIR.RECID AND [ADD].ADDRTABLEID = 2303
AND [ADD].STREET <> VEN.STREET
LEFT JOIN ADDRESS [ADD1] ON [ADD1].ADDRRECID = DIR.RECID AND [ADD1].ADDRTABLEID = 2303
AND [ADD1].STREET = VEN.STREET
LEFT JOIN CONTACTPERSON CON ON CON.VENDACCOUNT = VEN.ACCOUNTNUM
WHERE VEN.CREATEDDATETIME > '2018-04-30'
ORDER BY VEN.ACCOUNTNUM
- Which of definitions of database schema is correct?
- How to get isolation level in EF for MSSQL?
- Generate SQL Create Scripts for existing tables with query
- Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?
- Index for two columns that will be used in an operation on where clause?
- How do you update a datetime field in a table every time another field in the same table gets updated?
- Pros & Cons of TRUNCATE vs DELETE FROM
- comparing and contrasting Sybase and SQL Server
- How does sql server choose values in an update statement where there are multiple options?
- How to disconnect all users in sql server except sa?
- Select Consecutive Numbers in SQL
- SQL Find consecutive numbers in groups
- Spring Quartz configuration for connecting MS SQL server
- The most elegant way to generate permutations in SQL server
- Check column has 'letter' or '-' then bring value from another column
- Reading child XML node from column
- Combine JSON objects from multiple rows into a single object
- Find all stored procedures that reference a specific column in some table
- Laravel 10 stopped working with SQL Server Database
- Using TransactionScope around a stored procedure with transaction in SQL Server 2014
- Entity storing it's status as a FK to a status table. Can I update the FK once the status changes?
- How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
- I need to replace old path's images to new path with the same image
- Format date as yyyy-mm-dd hh:mm:ss.000
- Does a foreign key automatically create an index?
- How do I select only the latest entry in a table for each group so each 'latest' is a single row?
- "The certificate chain was issued by an authority that is not trusted" when connecting DB in VM Role from Azure website
- Kafka Connect SqlServer Configuration (SSL)
- SQL-Server: Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?
- TSQL Email Validation (without regex)