A blog offering various X++ code snippets and tips for Microsoft Dynamics AX

Wednesday, October 29, 2008

Displaying and Saving a Query

Sometimes it might be useful to display a query you have created in the AOT to the user. After this the user makes changes to the query (adds ranges and sort fields) and you would like to save the query (as changed by the user) to a table in the database, therefore saving the state of the query as modified by the user. Then you can re-use the query later by retreiving it from the table.

To do this you need to:

1. Create a field in your table of type container;
2. Create the query, you want to initially display to the user, as usual from the AOT;
3. Use the following code:

void createAndSaveQueryInTable()
  QueryRun queryRun;
  YourTable yourTable;
  // Attach new queryRun object to AOT Query
  queryRun = new QueryRun(queryStr('YourQuery'));

  // Display the query to the user for him/her to make the necessary changes
  if (queryRun.prompt())
    // Save the packed query to the database
    // Pack the queryRun so that it can be saved in the table
    yourTable.QueryContainerField = queryRun.pack();

Now, to retrieve the saved query from the table, for some other use, is equally simple:

void retrieveQueryFromTable()
  Container packedQuery;
  YourTable yourTable;
  // Get the necessary record from the table
  yourTable = YourTable::find(......);

  // Get the query stored in the table in packed form
  packedQuery = yourTable.QueryContainerField;

  // check if you already have a query packed in your field
  if (packedQuery)
    // Unpack it into a queryRun
    queryRun = new QueryRun(packedQuery);
    // Call method to create a new query - method shown in code snippet above
    queryRun = this.createAndSaveQueryInTable();

  // Do any cool work with your QueryRun here
  // .....

If you any questions or comments e-mail me on: mirko@mirkobonello.com