Notifications
Announcements
No record found.
Hi There, i got an requirement to develop an lookup method to populate data as per our business requirement. We are trying to customize LedgerParameters Form by adding a new field Local Ledger accounts and that field should populate lookup data based on below condition. How can i write the below SQL code as lookup method inside LedgerParameters Form extension class.
SQL : SELECT MAINACCOUNTID,NAME FROM CNLLUXLOCALACCOUNTMAPPINGVIEW INNER JOIN COMPANYINFOENTITYON CNLLUXLOCALACCOUNTMAPPINGVIEW.DATAAREA = COMPANYINFOENTITY.DATAAREAINNER JOIN MAINACCOUNTLEGALENTITY ON COMPANYINFOENTITY.RECID = MAINACCOUNTLEGALENTITY.LEGALENTITY
Thanks & Regards,
Prem
Hi Prem,
What is the CNLLUXLOCALACCOUNTMAPPINGVIEW? This is a customization. Can you explain the lookup requirement with words instead of SQL statements including views which we don't know any detail about?
A standard lookup for main accounts from the current legal entity is a standard component. You can look at examples; e.g. Inventory posting profiles.
I agree with Andrea that we might need to have more information but based on your requirement, you should add the extension field first and then use onlookup event handler and build X++ queries to "override" the look up method.
This might help you when you build the query:
support.microsoft.com/.../an-x-query-example-for-microsoft-axapta-that-you-can-use-when-you-want-to-use-multiple-criteria-d0cd8715-0a01-7716-a10c-7927185944be
Hope it helps.
You can try to add logic in the lookup event handler method of the form string control,sysTableLookup.addLookupField() to add lookup field,
QueryBuildDataSource.addLink() to add relationships:
msdynamics.nevoitech.com/.../
community.dynamics.com/.../add-link-to-existing-query
Hi Judy, I did exactly what you had mentioned. I had created Form extension class and added my lookup field's onlookup event handler and there i had written logic to populate mainaccountid and name from mainaccount table. But, as per my requirement, i need to populate those details which satisfies condition like CNLLUXLocalAccountMapping.Dataarea = MainAccountLegalEntity.Legalentity
Here CNLLUXLocalAccountmapping is our customized view which returns MainAccountId,Name from Mainaccount table as per some conditions. But that view returns values for all legal entities but we want to populate only those records for which equals with MainAccountlegalEntity.LegalEntity.
On top of that we cant simply join CNLLUXLocalAccountMapping.Dataarea with MainAccountLegalEntity.Legalentity directly
We need to join MainAccountLegalEntity.Legalentity with CompanyInfo.recid and from there
CompanyInfo.dataraea with CNLLUXLocalAccountMapping.Dataarea to populate mainaccount details for that specific Company
With fetchMode(QueryFetchMode::One2One) you can innerJoin 3 tables together:
dynamicsuser.net/.../help-wanted-to-innerjoin-3-tables-together-using-x-query
Hope it will help you.
Hi Andre. you said we can take Inventory posting profiles have the standard lookup for mainaccounts. Can you please be more elaborate on that like which forms or methods helps me to populate mainaccount.mainaccountid,mainaccount.name as a lookup for that particular legal entity. Thanks in advance
One of the many examples would be the form which opens when you navigate to: Inventory management > Setup > Posting > Posting.
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.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Abhilash Warrier 669 Super User 2025 Season 2
André Arnaud de Cal... 449 Super User 2025 Season 2
Martin Dráb 384 Most Valuable Professional
