Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Removed Query Fields n...
Finance | Project Operations, Human Resources, ...
Suggested answer

Removed Query Fields not recognized in TraceParser SQL

(0) ShareShare
ReportReport
Posted on by ScottG17 14

I have been a developer for many years but new to DAX, please forgive my rookie question :)   I have a Dynamics AX 2012 Query which is the data source for a form; the query has three data sources (tables) joined with left outer joins..  I modified each data source in the query to only include the needed fields for the form.  After saving the query, I ran trace parser and was expecting the SQL in trace parser to recognize this change in the fields.  But the Select statement in the trace still shows all the fields from each of the three tables - the same as before I made the query change.

I have included a couple screenshots for reference.

Any ideas?  

Thanks,

Scott

4540.AOT-Query.pngForm_2700_s-Trace-Parcer-SQL.png

  • Martin Dráb Profile Picture
    Martin Dráb 230,371 Most Valuable Professional on at
    RE: Removed Query Fields not recognized in TraceParser SQL

    I would start by checking the form query in GUI. Right-click the form, choose Personalize and then switch to the Query tab. You could also add one of the excluded fields to the form, to see whether it gets a value or not.

    I said, form data sources don't have any option to select individual fields, so it's possible that it's not supported with AOT queries either. But I don't really know; it's something to test.

    Nevertheless there is something else that form data sources support - you can enable OnlyFetchActive property (see Form Data Source Properties).

    Also, you could modify the underlying query with code.

  • ScottG17 Profile Picture
    ScottG17 14 on at
    RE: Removed Query Fields not recognized in TraceParser SQL

    Daniele,  Thank you for your guidance on this!  With our AOT version (DAX 2012), I do not have an option to "Delete elements in cache," but we do have a "Refresh Elements" option which I think does the same thing.  I ran that but unfortunately the Select statement still included fields that I had removed.  I will check the fields in the joins, as you suggested, and try removing and re-adding the data sources to the query and see if that works.  Thanks again for your help, very much appreciated.  

    Scott

  • Suggested answer
    DAnny3211 Profile Picture
    DAnny3211 9,274 Super User 2024 Season 1 on at
    RE: Removed Query Fields not recognized in TraceParser SQL

    hi

    It's possible that the changes you made to the data sources in the query have not been fully reflected in the Select statement generated by the query. This can happen if the query's cache needs to be cleared or refreshed.

    To ensure that your changes to the query's data sources are fully reflected in the Select statement, you can try clearing the query cache and running the trace parser again. Here are the steps to do this:

    Open the AOT (Application Object Tree) in Dynamics AX.

    Expand the "Queries" node, and then locate the query you modified.

    Right-click the query and select "Delete elements in cache" to clear the query cache.

    Right-click the query again and select "Generate SQL" to regenerate the Select statement for the query.

    Run the trace parser again to see if the changes you made to the query's data sources are now fully reflected in the Select statement.

    If the Select statement still includes fields that you removed from the data sources in the query, you may need to review the query's join criteria and ensure that you've included all the necessary fields to support the join conditions. You can also try removing and re-adding the data sources to the query to ensure that they're properly linked.

    Daniele

  • ScottG17 Profile Picture
    ScottG17 14 on at
    RE: Removed Query Fields not recognized in TraceParser SQL

    Thanks, Martin for your message.  I should also share that my goal was to improve performance, including only the needed fields in the AOT query.  Modifying this query the same way on the SQL side (SSMS) improved the form load performance from 15 seconds to 2 seconds.  Hoping to find a solution for this in AOT.  

  • Martin Dráb Profile Picture
    Martin Dráb 230,371 Most Valuable Professional on at
    RE: Removed Query Fields not recognized in TraceParser SQL

    I would be surprised if it worked, because it's not something that form data sources allows.

    I believe that if you use a query to define data sources, the system takes tables and joins into account, but it ignore fields.

    Note that you can also see the (primary) form query in GUI.

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.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,642 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,371 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans