Share
ReportHi,
I need to create new view in SQL which will show me all vendors with their active account number. Can you help me ?
You’re offline. This is a read only version of the page.
5
Hi,
I need to create new view in SQL which will show me all vendors with their active account number. Can you help me ?
Regarding active bank accounts, I think that you should check ActiveDate and ExpiryDate fields.
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?
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
Okay, and what's the problem?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (