How to use a View to Simplify Working with Query Classes in Dynamics AX

by | Updated August 15, 2016 | Development, Dynamics AX

Have you ever used a View to simplify working with Query classes in Dynamics AX? I have worked on a couple projects where my lookups were not working quite like I wanted them to when working with the Query classes. The problems specifically happened when I wanted to see data from a child table that was two, three or four levels deep. The lookup wouldn’t run or the data coming back into the lookup form would say ‘Unknown’. I thought to myself, it would be nice if the data I wanted to see was in one table and I would not need to do any joins using the Query classes. This is when I realized that using a custom View created in the AOT would be handy.

Let’s say I wanted to see data related to Project Contracts and their Customers that are associated with Projects in my lookup. (Physical table and field names in parentheses):

 

The customer name lives in DirPartyTable so this will require joining three tables to get to it:

To create the view in the AOT, you can choose a table based one or a query based one. If you want a query based one, then you create the query in the AOT first and then select it using the Properties window of the view object. This is an example of the CustBankAccountCrossCompanyView that is query based:

Chris R_Cust Bank Account

 

I used a table based query for this solution. The ProjTable will be the parent table and will join the other three tables I listed above. The view looks like this:

AOT Data dictionary views

 

Now that my view is defined in the AOT, I can now test it in a job or a custom lookup with X++. The following is a job that displays the data for a certain Customer number:

Note: There is only one QueryBuildDataSource required since the view joins the underlying tables behind the scenes.

 

Here would be a similar example of the job code in a method that would be used in a lookup filtering on a Customer number:

I hope this technique helps if you run across the same problems joining multiple QueryBuildDataSource objects when you need data from child tables.

 

Related Posts

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

march

03mar10:00 am10:30 amThe Modern Manufacturer - MRP vs. MPS – What, When and How

03mar2:00 pm2:30 pmLot Management Best Practices for Batch Manufacturers

10mar10:00 am10:30 amEnhancing the Customer Experience and Driving More Business with Dynamics 365 Customer Service

10mar2:00 pm2:30 pmThe Five Steps to Managing Sales Tax

11mar11:00 am12:00 pmConfab with Stoneridge - Livestream - Power Apps Component Framework Power Hour

17mar10:00 am10:30 amThe Modern Manufacturer: Pro Tips for your Finance Team

24mar10:00 am10:30 amUnlock the Potential of Digital Marketing with Dynamics 365 Marketing

25mar11:00 am12:00 pmConfab with Stoneridge - Livestream - The Four Keys to Choosing the Best ERP or CRM Solution

31mar10:00 am10:30 amThe Modern Manufacturer: Keys to Reporting Success

31mar01aprTraining Class: Financial Reporting W/Account Schedules for Dynamics 365 Business Central

april

31mar01aprTraining Class: Financial Reporting W/Account Schedules for Dynamics 365 Business Central

07apr10:00 am10:30 amBoost Seller Productivity and Close More Business with Dynamics 365 Sales

07apr2:00 pm2:30 pmEnsuring Quality for Batch Manufacturers

08apr11:00 am12:00 pmConfab with Stoneridge - Livestream - Jumping into the Deep End of the Data Lake

14apr10:00 am10:30 amThe Modern Manufacturer: Cost Accounting in D365

21apr12:00 pm1:00 pmGetting Started with Barcode Scanners and WMS Apps for Business Central

22apr11:00 am12:00 pmConfab with Stoneridge - Livestream - Moving from Manual to Automated with Strategic Automation Strategies

28apr10:00 am10:30 amThe Modern Manufacturer: Best Practices for Inventory Turn

28apr12:00 pm1:00 pmPower Up Microsoft Dynamics by Automating Budgeting, Dashboarding and Reporting

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X