Friday, March 26, 2010

Tutorial: refresh, reread, research, executeQuery - which one to use?

X++ developers seem to be having a lot of trouble with these 4 datasource methods, no matter how senior they are in AX.
So I decided to make a small hands-on tutorial, demonstrating the common usage scenario for each of the methods. I have ordered the methods based on the impact on the rows being displayed in the grid.
You can download the xpo with the tutorial on my SkyDrive.

1. Common mistakes

Often, developers call 2 of the mentioned methods in the following order:
formDataSource.refresh()
formDataSource.research()

or
formDataSource.reread()
formDataSource.research()

or
formDataSource.research()
formDataSource.executeQuery()

or
formDataSource.research()
formDataSource.refresh() / formDataSource.reread()

All of these are wrong, or at least partially redundant.
Hopefully, after reading the full post, there will be no questions as to why they are wrong. Leave a comment to this post if one of them is still unclear, and I will try to explain in more detail.

2. Refresh

This method basically refreshes the data displayed in the form controls with whatever is stored in the form cache for that particular datasource record. Calling refresh() method will NOT reread the record from the database. So if changes happened to the record in another process, these will not be shown after executing refresh().
refreshEx
Does a redraw of the grid rows, depending on the optional argment for specifying the number of the record to refresh (and this means the actual row number in the grid, which is less useful for AX devs). Special argument values include -1, which means that all records will be redrawn, and -2, which redraws all marked records and records with displayOptions. Default argument value is -2.
This method should be used sparingly, in cases where multiple rows from the grid are updated, resulting in changes in their displayOptions, as an example. So you should avoid using it as a replacement for refresh(), since they actually have completely different implementations in the kernel.
Also, note, that refreshEx() only redraws the grid, so the controls not in the grid might still contain outdated values. Refresh() updates everything, since this is its intention.

3. Reread

Calling reread() will query the database and re-read the current record contents into the datasource form cache. This will not display the changes on the form until a redraw of the grid contents happens (for example, when you navigate away from the row or re-open the form).
You should not use it to refresh the form data if you have through code added or removed records. For this, you would use a different method described below.
How are these 2 methods commonly used?
Usually, when you change some values in the current record through some code (for example, when the user clicks on a button), and update the database by calling update method on the table buffer, you would want to show the user the changes that happened.
In this case, you would call reread() method to update the datasource form cache with the values from the database (this will not update the screen), and then call refresh() to actually redraw the grid and show the changes to the user.
Clicking buttons with SaveRecord == Yes
Each button has a property SaveRecord, which is by default set to Yes. Whenever you click a button, the changes you have done in the current record are saved to the database. So calling reread will not restore the original record values, as some expect. If that is the user expectation, you as a developer should set the property to No.

4. Research

Calling research() will rerun the existing form query against the database, therefore updating the list with new/removed records as well as updating all existing rows. This will honor any existing filters and sorting on the form, that were set by the user.
Research(true)
The research method starting with AX 2009 accepts an optional boolean argument _retainPosition. If you call research(true), the cursor position in the grid will be preserved after the data has been refreshed. This is an extremely useful addition, which solves most of the problems with cursor positioning (findRecord method is the alternative, but this method is very slow).

5. ExecuteQuery

Calling executeQuery() will also rerun the query and update/add/delete the rows in the grid. The difference in behavior from research is described below.
ExecuteQuery should be used if you have modified the query in your code and need to refresh the form to display the data based on the updated query.
formDataSource.queryRun().query() vs formDataSource.query()
An important thing to mention here is that the form has 2 instances of the query object - one is the original datasource query (stored in formDataSource.query()), and the other is the currently used query with any user filters applied (stored in formDataSource.queryRun().query()).
When the research method is called, a new instance of the queryRun is created, using the formDataSource.queryRun().query() as the basis. Therefore, if the user has set up some filters on the displayed data, those will be preserved.
This is useful, for example, when multiple users work with a certain form, each user has his own filters set up for displaying only relevant data, and rows get inserted into the underlying table externally (for example, through AIF).
Calling executeQuery, on the other hand, will use the original query as the basis, therefore removing any user filters.
This is a distinction that everyone should understand when using research/executeQuery methods in order to prevent possible collisions with the user filters when updating the query.

51 comments:

  1. Hi, thanks for this.
    Too many developers use executeQuery() all the time :).
    Best Regards.

    ReplyDelete
  2. Thanks for the info! it's very usefull. I will reference your info in my blog.

    ReplyDelete
  3. This is a very useful one. Thanks Kashperuk. I have referenced this in my blog too...

    ReplyDelete
  4. Thanks!

    I translate to spanish and summarize it at my blog.

    ReplyDelete
  5. It clarified this subject to me. Thanks!

    Actually, I did not manage to import this project to my AX4.0; so, I adapted it to version 4.0 http://cid-cbbef1c260084205.skydrive.live.com/self.aspx/.Public/PrivateProject^_Tutorial^_FormDataSourceRefresh^_4.xpo

    Vania, if do not mind, I wish to publish this comment as a reference to this posting in my blog about AX.

    ReplyDelete
  6. Sure. Just add a link to this post.

    ReplyDelete
  7. Wonderful article Vanya.All these years i used them without knowing the significance of each of them. Keep writing.

    regards
    ashish

    ReplyDelete
  8. Very helpful article. It helped me to understand this concepts and use these more effectively.

    Greetz
    Johannes

    ReplyDelete
  9. Are there any requirements for the research method when it is called with value true for parameter _retainPosition? We noticed that it doesn't work for all grids, but we haven't found yet the reason why.

    ReplyDelete
  10. There are potential issues when there is grouping in the grid, that I have stumbled onto myself.
    If you dig to the bottom of your issues, post a reply here - would be interesting.
    Thanks

    ReplyDelete
    Replies
    1. It has been a while but recently I came across this problem again in AX 2012 and after some digging found this article:
      http://devexpp.blogspot.de/2012/02/refresh-datasource-and-retain-position.html
      Basically it says you can run into problems with research and retainPosition when a form uses data sources that are not linked by inner joins or uses views or temporary tables.

      Delete
    2. Yes, thanks for posting this, FH.
      Microsoft, however, discourages the use of FindRecord(), because it is very slow, as I mentioned in the post.
      So you should only use it, if it is critical you still have the cursor on the right record after research(), which shouldn't really be in many cases.

      Delete
  11. Thanks vanya, your article helped me to solve a research/reread/refresh problem ;-) now i understand waht it means exactly

    ReplyDelete
  12. Hi Ivan,

    Thanks for this very helpful post. By the way, I've just got a couple of questions.
    I've got this SQL trace log error that says the query is too nested deeply. Then I checked out the form that sources this error and it's coming from a couple of datasource tables in that form, particularly in the executQuery override method.

    So I looked what's in the executeQuery methods and there are queryBuildRange addRange there that seems to add some ranges for that datasource.

    I placed break points where the executeQueries are called then ran the form. What I've noticed is that it calls the executeQueries on the form datasource everytime the user moves to another record. And this still happens even when other methods on the form calls out the executeQuery on the datasource.

    Is it possible that the addRanges stacks up whenever the executeQueries are run which then results to the deeply nested queries?

    Here's the link to the SQL trace log of the nested query: http://www.sendspace.com/file/te7mt3

    Thanks for your time!



    Regards,

    Matt

    ReplyDelete
  13. Typed your name wrong. Sorry Vanya. Man, this is embarrassing.

    ReplyDelete
  14. Ivan and Vanya are actually the same name, so no worries :)
    Ivan is the more official one, while Vanya is the one I prefer to use.


    About your question above:
    the query obviously looks wrong - one of the ranges is added over and over again. That is incorrect. But we should be looking at the form code, not at the query.
    Probably, two things are happenign:
    active() is overridden on the datasource, and calls executeQuery(), which is wrong
    in executeQuery(), a new range is always added instead of finding an existing one, causing the above query to happen.

    ReplyDelete
  15. Relieved to hear that :)

    Anyhow, that actually makes sense. I've actually made screenshots of the codes which you might want to look into.

    You can find it here: http://www.sendspace.com/file/w2hytc

    I really appreciate you taking the time to help me out :)

    ReplyDelete
  16. yeah, so both places an addRange() method is used instead of a findRange() (with an addRange, if that does not exist).
    There is a findOrCreateRange() method on SysQuery class, or potentially in Global on your installation. You can replace the code with this method call.

    looking at the code you sent, there is no explicit calls of executeQuery method anywhere except for the clicked() methods, more or less.
    So it should not lead to continuous executions of the query.

    Consider sending me the full xpo, maybe I would be able to find something strange there.
    But do try the above fix first. That should help, although I don't think it will fix the issue you described originally

    ReplyDelete
  17. Hi!

    Thanks for the explanation, but something doesn't work correct or I'm doing something wrong. maybe you can help? I use Ax4.0

    I have a form with a grid and datasource. In the DisplayOption() method of the datasource I check for a record status and change the color of the row.
    I have a button. When clicking this button I call a method that changes the record status. The color of the row should change.

    According to your explanation, I should first call reread() to update the datasource form cache with the new status (this works, the field changes), followed by refresh() to update the grid. But when I do so, my colors don't change. The DisplayOption() method isn't called.

    I can solve this by using ReSearch(), buy I didn't add or delete records.

    How can I solve my problem?

    regards,

    Rob

    ReplyDelete
  18. Hi Rob
    In order to update the display options for a specific row that you updated, you can use the clearDisplayOption method on the FormDataSource class.

    So, after calling

    yourDS_ds.reread();
    yourDS_ds.refresh();

    you should call

    yourDS_ds.clearDisplayOption(yourDS);

    This should do the trick and update the color of the row.

    ReplyDelete
    Replies
    1. Hello,
      I have exactly the same situation as Rob. My data source is a temporary table (if it matters). I want to update the row color after changing a particular filed (Field1) that will change other two fields in the same data source (Field2 and Field3). This is done by a modified method in the table. The code for changing row color is based on the values of Field2 and Field3. What I did was to override the modified method of Filed1 (in Design / Grid). I noticed that even thou Field2 and Field3 are changed I don't see those values updated in my data source. Obviously I'm doing something wrong and I would really appreciate any advice pointing me in the right direction.

      public boolean modified()
      {
      boolean ret;

      BankImportTable_ds.reread();
      BankImportTable_ds.refresh();
      BankImportTable_ds.clearDisplayOption(BankImportTable);

      ret = super();
      return ret;
      }

      Thanks,
      Alex

      PS: If I put BankImportTable_ds.removeFilter() before return, the row color changes but the cursor moves to the first line.

      Delete
    2. Well, it's hard to tell from the above description only.
      Try maybe doing reread on BankImportTable before the other 3 lines?

      Another thing would be to do a research(true). That should maintain the position (but depends on which AX version you have - it did not have this argument in earlier versions)

      Delete
    3. Thank you for your answer (and, of course, your blog)
      I do not have access to the development environment right now but I'll try your suggestions asap. I'll also change the display option conditions based only on the column I'll be changing (Field1) just for testing. As for research(true), I can't use it because I'm still running AX4 SP2

      Thanks,
      Alex

      Delete
  19. Thank you! It does the trick :-)

    ReplyDelete
  20. nice job vanya! i'll post about that on my blog (in spanish) if you don't mind.

    ReplyDelete
  21. Be my guest. Just make sure to provide a link to the original post or something.

    ReplyDelete
  22. Good article i use often your blog to waiting for information to solve my problem

    Andrea

    ReplyDelete
  23. Response to the research(true) question. If research(true) does not work for a particular grid/ds, make sure you have a primary key defined for the table your ds references. Research has no way to know how to retain a position if it doesn't know a unique key to the row it is on.

    ReplyDelete
  24. Your article helped me refresh my grid.

    Thanks

    Storm

    ReplyDelete
  25. Excellent. Great help to a newbie

    ReplyDelete
  26. Thanks for your article

    ReplyDelete
  27. This is one of the best AX Articles i ever read :)

    ReplyDelete
  28. hey really nice tutorials blog.. and quite informative too..

    thanks for sharing..


    Anukant
    Max3logic IT Solutions Pvt. Ltd.
    mlm software in india

    ReplyDelete
  29. Just what I needed!! This is very helpful article!

    ReplyDelete
  30. can plz explain in detail about the refresh(),reread(),research(),executeQuery() methods and also lock() and unlock() also.

    ReplyDelete
  31. I would say the above description is pretty detailed.
    As for lock/unlock, I have a separate post about it:
    http://kashperuk.blogspot.com/2011/05/tutorial-lockwindowupdate-vs-lockunlock.html

    ReplyDelete
  32. hi!
    i'm a newbie in ax and this article really helps me understanding those functions. thank you! :')
    but now i have a condition that i can't solve by the combination of those method. i have a table which contain mapping between sales unit id and customer account.

    i made a grid containing the mapping data. then i have a button which should add range to existing grid. i've tried querybuilddatasource.addrange(..).value(..) and put it in datasource, clicked() method, and add the method in form, none of it works. can you help me? i'd really appreciate it

    sorry for my dull english.
    regards,

    naski

    ReplyDelete
  33. Well, the research()/executeQuery() are your 2 choices. It should work with any of the two.

    Send me your form code if you can't figure it out yourself, I'll take a look.

    Thanks

    ReplyDelete
  34. Many Thanks Vanya.
    You demystified this part of AX.
    Very usefull for me

    ReplyDelete
  35. Hi Vanya,
    I'm using ProductBuilder with a class variable (PBATableVariable) which insert an extra line in saleslines when the item is configured.
    I've noticed the cursor / currently selected salesline in the form jumps back and I have to switch salesorders to re-read the screen.
    I don't really want to change the SalesTable form, is there another option?

    Thnx,

    Mike

    ReplyDelete
  36. I don't think there's another option. I mean, if it's a bug on the form, it's a bug on the form...
    Since we don't have interaction classes for details forms, it is written on the form..

    ReplyDelete
  37. Great post. I used this in conjunction with the active method on the data source to update the grid for the users.

    ReplyDelete
  38. Very useful post! Thank you!

    ReplyDelete
  39. Hi Vanya,

    I have one issue:
    in form Grid, when user select Item_1, I am inserting Item_2 automatically, here one info log will come to ask user Item_3 exist want to insert? depend upon users choice Item_3 will inserted into grid.

    here my issue is message box for Item_3 is displaying before Item_2 insertion in grid(means when I click Ok for Item_3 then only Item_2 is showing in grid).

    I want my message should trigger(or before triggering message -> Item_1 and Item_2 should be shown in grid). after refreshing of grid with Item_2.

    Please give some hints.
    Thanks

    ReplyDelete
  40. Hello Vanya
    Thank you for your blogging activity. In general you write good blogs including this one.
    About this issue I notice that in AX 2012R2 in the SYS layer in the form PurchCreateFromSalesOrder in the modified method on the form field specifyVendAccount there is a call to first refresh() and then reread().

    According to your blog I would say it should be the other way around.
    Or have I misunderstood?
    Thanks
    /Johan

    ReplyDelete
  41. I see someone using element.reload(), how does that differ from the methods above?

    ReplyDelete
  42. Hi,

    Do you have any in-depth information about staying on the same record in grid? In my case the grid shows aggregated data (a number of quantity fields summed up grouped by, say, ItemId and InventTransId), so Table_ds.findRecord will not work, and I find that setPosition() method works only within a limited number of records. If I am more than 50 records down the grid, setPosition(50) stays on the first record after calling Table_ds.research(true).

    ReplyDelete

Please don't forget to leave your contact details if you expect a reply from me. Thank you