More on Using Views in Microsoft Dynamics AX 2012

by | Updated October 15, 2018 | Development, Dynamics AX

As a follow up to my last blog, Using Computed Columns in AX 2012 , I wanted to share a few more useful tips regarding Views in Dynamics AX 2012.

I think of Views as a type of read-only temporary table. The fields and data are not stored in the database but created at runtime. Like temp tables, they may contain fields from multiple tables and/or fields based on complex business logic. The thing is, AX thinks of them much like tables too, and you can therefore do a lot of things with Views that you can do with Tables. Examples are; joining Views to tables and to other views, using Views in Queries, Forms and Reports, even writing Select statements against them in code.

Lately, I have been exploring different ways to utilize Views in AX, and here are a few things I have learned along the way.

1. Use a View to test a Query

Designing complex queries in the AOT can be a tedious and time-consuming process. Once completed, running the query object from the AOT does not display a resultset, so testing can be a challenge as well. I used to write code like this to test and make sure I was getting the expected resultset from my query:

A much easier way to do this is to simply create a new View in the AOT and drag your query to it. In the example below, I created the MyCustView View and dragged the CustTable query to the Metadata node.

My Cust View

Now I can simply add any field I want from any datasource in the query, save, sychronize and run the view from the AOT to view my results.

Account Num

 

Table Browser My Cust View

2. Base your Views on Queries to allow for joins, filters and grouping

Views only support inner joins. There is no way to specify any other join type. Instead of basing your views on tables, build a query in the AOT. Specify JoinMode, Relations and Ranges if desired. Then drag the Query to the Metadata node of the View and select some fields to show in the View (as in #1 above).

For example, the query used in #1 is CustTable. I changed the base query to use an Outer Join. Note the JoinMode and Relations properties are grayed out in the view, but the Join Mode was specified on the query so still applies. The relations established in the Query also apply here.

AOT Data Dictionary Views

Keeping in mind that Queries can be made up of Tables, Maps, and Views, the possibilities are endless. Here is an example in base AX of a View based on a Query, and the Query contains multiple Views itself.

Activity Soft Booked Date Capacity View

3. Create cross-company Views

Simply create a query as below with the AllowCrossCompany property to Yes. Drag the Query to a View and voila!

Allow Cross Company

4. Solve complex join problems by using Views and joining to Tables and other Views

I recently had a need to use a groupby field to join an additional table, like this…

I want to use ProjId from MCAShippingWorksheetLine to join in the WMSPallet table. This just does not work when ProjId is the GroupBy field. The query returns no records at all. I suspect there may be a couple of ways around this, but instead of wasting a lot of time, I decided to use a simple View for the GroupBy portion of this query. Piece of cake!

Meta Data

Then I was able to use the View in my X++ query, joining the WMSPallet table to the View. (MCAWorksheet_ProjId is my View)

*As a note, you must still add the word “crosscompany” in a select state on the view to behave correctly, if you do not then it will still only return the values for the company you are in.

Related Posts

  • Since the release of Microsoft Dynamics 365, there have been a lot of rumors around when Microsoft support for Dynamics AX 2009 and Dynamics AX 2012 will end. Microsoft Dynamics AX support…

  • This post is meant especially for new users of Microsoft Dynamics AX. I think it is important to be aware of different shortcuts that are available to help your speed and ease…

  • Have you ever found yourself applying the same filter to a list page in Microsoft Dynamics AX 2012 over and over? If so, this quick and easy tip will save…

4 Comments

  1. Luca Dallari

    Hi
    Are you sure about cross-company views?

  2. Brandon Carmichael

    Hello Luca:

    Yes, absolutely.

    Thanks,
    Brandon

  3. Paul Taylor

    The cross-company view does not work ‘just like that’. You still have to insert the word ‘crosscompany’ in the select statement, otherwise it does not behave as the cross-company query would suggest.

  4. Taylor Valnes

    Hello Paul,

    We have updated the post with a footnote mentioning this.

    Regards,
    Taylor

Trackbacks/Pingbacks

  1. Using Computed Columns in Microsoft Dynamics AX 2012 | Stoneridge Software Inc - […] For additional tips check out my post: More on Using Views in Dynamics AX 2012 […]

Submit a Comment

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

Upcoming Events

october

07oct12:00 pm1:00 pmThe Three Paths to Business Central from Dynamics GP

08oct11:00 am12:00 pmConfab with Stoneridge - Livestream - The Vision and Strategy of Microsoft Business Systems

14oct10:00 am10:30 amThe Modern Manufacturer - Managing Complex Cost Modeling

14oct12:00 pm12:30 pmGenerating Custom Inspection or Process Forms

19octAll Day22Stoneridge Connect Fall 2020

22oct11:00 am12:00 pmConfab with Stoneridge - Livestream - Stoneridge Connect Recap

28oct10:00 am10:30 amThe Modern Manufacturer - Engineering Change Management: Introduction of NEW Functionality for Manufacturers Using Dynamics 365

november

11nov10:00 am10:30 amThe Modern Manufacturer - Tears and Trauma of MRP

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