Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 2Discover the latest updates and new features releasing from October 2021 through March 2022.
2021 release wave 2 plan
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
I need to create new view in SQL which will show me all vendors with their active account number. Can you help me ?
Okay, and what's the problem?
I'm not sure how to filter only active accounts numbers
I try something like this:
SELECT distinct VENDTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME,Address.ADDRESS ,
VendBankAccount.ACCOUNTID BankAccount ,VendBankAccount.NAME 'Bank Name' ,VendBankAccount.ACCOUNTNUM 'Bank account number',
VendBankAccount.RegistrationNum 'Routing Number',VendBankAccount.SWIFTNo,VendBankAccount.BankIBAN
from VENDTABLE left outer JOIN VendBankAccount ON VendBankAccount.VENDACCOUNT = VENDTABLE.ACCOUNTNUM --AND VendBankAccount.ACCOUNTID = VENDTABLE.BANKACCOUNT
left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY
left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = DIRPARTYTABLE.PRIMARYADDRESSLOCATION
Hmm, I thought you want to create a view in AX 2012. If so, you should create it in AOT. Meddling with the database directly is difficult and risky. You would need direct access to the database, you would bypass AX security, you couldn't use AX business logic (which is likely your current problem), you would have to manually handle data areas (which is where you've failed already), date-effective tables (another bug) and so on and so on. Please stop that and create a view in AOT.
By the way, please always paste formatted code via Insert > Insert Code (in the rich-formatting view). For example:
SELECT DISTINCT * FROM VendTable
LEFT OUTER JOIN VendBankAccount ON VendBankAccount.VendAccount = VendTable.AccountNum
--AND VendBankAccount.AccountId = VendTable.BankAccount
LEFT OUTER JOIN DirPartyTable ON DirPartyTable.RECID = VendTable.Party
LEFT OUTER JOIN LogisticsPostalAddress AS Address ON Address.Location = DirPartyTable.PrimaryAddressLocation
That's much easier to read, isn't it?
Regarding active bank accounts, I think that you should check ActiveDate and ExpiryDate fields.
Business Applications communities