web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to Filter on Grid ...
Microsoft Dynamics AX (Archived)

How to Filter on Grid only without refreshing data from Database

(0) ShareShare
ReportReport
Posted on by JauB 352

Hi everybody

How can I filter on a Grid (for a given Column) without executing the query from  the database after the first display ?

Typically : I lunch a specific form containing a Grid and my data is displayed. Good

I want, when the user try to filter on a column, that AX display the concerned Rows without refreshing data from the Database. So even, there is a refreshed data after executing my first query, i don't want to get when executing a filter.

If there is  a property to change or an idea ....

I hope my question is clear :)

Thanks in advance.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 239,452 Most Valuable Professional on at

    It would work if the form obtained all data (not just what it needs to display at the moment) and make a copy, which isn't the case.

    You can implement it by yourself, if you want, but be careful about performance implications.

  • JauB Profile Picture
    JauB 352 on at

    Thanks Martin.

    The first execution obtain all data.

    How can i do the copy please ?

  • Martin Dráb Profile Picture
    Martin Dráb 239,452 Most Valuable Professional on at

    It doesn't. It creates a cursor and fetches just a few records need for current display. It may fetch something like 100 records even if the table has billion ones. Fetching all of them from database and caching them on client would be a huge waste of resources for no reason.

    You could use a TempDB table for the copy. But why? What's business problem you're trying to solve by this architecture?

  • JauB Profile Picture
    JauB 352 on at

    So the situation is like that : My Form is linked to a SQL Query (created directly in SQLServer, not using AOT,). This SQL Query make arround 3 minutes to get the result (it make a lot of calculation that i don't want to do using X++ ...). Once my Grid is filled by this result, the final user can make filters on some columns (like Item Code, Site ...), but the problem is that when the user filter on a givgen column and click on enter, AX make the same time (3 minutes) to execute this filter, so sure it try to execute the whole Query from scratch and return the resukt filtred depending on what the user make as filter. So my need is : Once the Query is executed for the first time and the Grid is filled, so when teh user try to filter on a specific column, AX must fetch only from the actual data filled on the Grid without refreshing the data twice from AX :)

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 239,452 Most Valuable Professional on at

    Then the solution is simple - run the expensive query once, put the result into a TempDB table and use this temporary table as the datasource of your form.

    By the way, be very careful when using direct SQL queries - don't forget to take into account partitions, companies, table and field permissions, record-level security and so on.

  • JauB Profile Picture
    JauB 352 on at

    It works.

    Thanks Martin :)

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the April Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 39

#2
Michel ROY Profile Picture

Michel ROY 14

#3
imran ul haq Profile Picture

imran ul haq 8

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans