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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Display results of com...
Finance | Project Operations, Human Resources, ...
Answered

Display results of complex query in form?

(2) ShareShare
ReportReport
Posted on by CD-28072126-0 117
My question is similar to the one at https://community.dynamics.com/forums/thread/details/?threadid=96a4bf38-e0e2-42c2-943d-93ccb30c655b , which was unanswered.  I, also, want to know how to display results of a complex query in a form.   In my case, I know that for a report I would just use the same technique used for reports bound to temporary tables (Data Provider class, etc.) but what if I need a form and not a report? Can I bind my  form to a temporary table and populate the table via X++ in a form event?
I have the same question (0)
  • Suggested answer
    Waed Ayyad Profile Picture
    Waed Ayyad 9,039 Super User 2025 Season 2 on at
    Hi,
     
    I'm not able to open the previous question, but no worries.
     
    1- If you able to do your requirements using AOT query you can use the Query on the form.
    2- If you aren't and you wrote it by code, you can add your tables to the form and add ranges using code on the form.
    3- If you are able to use the AOT query and your form will only show data (read only) , you can connect the query to view and add the view as form data source.
     
     
    Thanks,
    Waed Ayyad
    If this helped, please mark it as "Verified" for others facing the same issue
  • Raj Borad Profile Picture
    Raj Borad 1,428 on at
    Hi,
     
    As per my knowledge, the 3rd answer provided by Waed is proper and meets your requirements.
     
    Thank you, 
    Raj
  • CD-28072126-0 Profile Picture
    CD-28072126-0 117 on at
    In what way is an AOT Query different from an AOT view? Does it allow me to enter a complex query? Under Query I see, for example:
     
    Data Sources
      DataSourceName
        Fields
        Ranges
        Data Sources
        Group By
        Having
        Order By
     
    The Query's data source has to be a Table. Maybe that means Table or View or another Query. You don't have the option of a Data Provider class, which is available for a Report.
     
    This is the same as an AOT View, except that you can also Order By. My query is too complex to be represented in this point-and-click way. 

    By the way, the original question (search for "Display results of complex query in form") was:

    Display results of complex query in form?

     
     
     
    SubscribeLike(0)Share
     
     
    ReportReport
     
    Posted on 21 Oct 2020 08:39:12 by Community Member UG Leader

    Hi, I need to create a query that seems impossible (or difficult) to express using reports or the advanced find tool, but would be trivially expressed in SQL or C#.

    SQL is unavailable for the hosted solution, but is there any way to define a query or somehow to create an EntityCollection and display it in a form? 

    My best idea is to create a connection that would resemble the datamodel of the query result, and populate it using a Plugin that fires on record retrieval (executing the query and populating that field with the results data before the record is displayed in the form). Would this work, and if so, is this really the easiest way to achieve this? 

  • CD-28072126-0 Profile Picture
    CD-28072126-0 117 on at
    I found this example How to Use Temporary Table in Form    for Microsoft Dynamics Ax posted by Nagaraj Jadhav | India.
    I have reproduced his article below:
    // How to Use Temporary Table in Form
//
// Temporary table(Table whose property temporary set to "yes") is not persistent media , so at run-time you have to populate the temporary table and attach the same to the form/report.
// For illustration purpose ,I am using inventTable data and attaching the items whose group is Parts.
// Steps
//
// 1. Create temporary table as TmpTestTable with 3 fields(ItemId,Itemname,ItemGroup).
// 2. Create a form as TempTestTable and attach the table as datasource
// 3. Create a new method on the form and copy the following code
//
TmpTestTable populateRecords(ItemGroupId _itemGroupId)
{
  TmpTestTable tmpTable;
  InventTable inventTable;
  ;

  while select inventTable
  where inventTable.ItemGroupId == _itemGroupId
  {
    tmpTable.Itemid = inventTable.ItemId;
    tmpTable.ItemName = inventTable.ItemName;
    tmpTable.Itemgroup = inventTable.ItemGroupId;
    tmpTable.insert();
  }

  return tmpTable;
}

//4. Call the above method in init() after super as below
public void init()
{
  super();

  // Call the setTmpData method to attach records to datasource
  TmpTestTable.setTmpData(element.populateRecords("Parts"));
}
    Now I would like to translate this to D365 F&O. This is what I have done so far:
     
    1) I created a temporary table  TestTableTblTmp with 2 fields FirstName and LastName.
    2) I created a form TestTableFrmTmp with TestTableTblTmp as the datasource.
    3) I created a form method like this:
    [Form]
public class TestTableFrmTmp extends FormRun
{ 
    TestTableTblTmp populateRecords()
    {
        TestTableTblTmp tmpTable;

        ttsbegin;
        tmpTable.FirstName = "john";
        tmpTable.LastName = "brown";
        tmpTable.insert();
        ttscommit;
       
        return tmpTable;
    }

}
    My problem is with step (4)
    //4. Call the above method in init() after super as below
public void init()
{
  super();

  // Call the setTmpData method to attach records to datasource
  TmpTestTable.setTmpData(element.populateRecords("Parts"));
}
    I put init() in the form class, so that the entire class is:
    [Form]
public class TestTableFrmTmp extends FormRun
{ 
    public void init()
    {
        super();
        Box::info("TestTableFrmTmp::init called");
        TestTableTblTmp.setTmpData(element.populateRecords());
    }
    TestTableTblTmp populateRecords()
    {
        Box::info("element.populateRecords called");
        TestTableTblTmp tmpTable;

        ttsbegin;
        tmpTable.FirstName = "john";
        tmpTable.LastName = "brown";
        tmpTable.insert();
        ttscommit;
       
        return tmpTable;
    }

}
    I see  the "init" message but not the "element.populateRecords" message. What am I doing wrong?
  • Waed Ayyad Profile Picture
    Waed Ayyad 9,039 Super User 2025 Season 2 on at
    Hi,
     
    As I told you :
    1- If you able to do your requirements using AOT query you can use the Query on the form.
    2- If you aren't and you wrote it by code, you can add your tables to the form and add ranges using code on the form.
    3- If you are able to use the AOT query and your form will only show data (read only) , you can connect the query to view and add the view as form data source.
     
    But it seems you used the Temp table now? So, what is your issue? the data isn't filled on your table? Did you try to debug the code?
    Also did you set the Table to Temp DB?
     
     
    Thanks,
    Waed Ayyad
     
  • Verified answer
    Waed Ayyad Profile Picture
    Waed Ayyad 9,039 Super User 2025 Season 2 on at
    Hi 
     
    Take a look on the following blogs, it may be helpful for you:
     
    Thanks,
    Waed Ayyad
     
    If this helped, please mark it as "Verified" for others facing the same issue
     
     

     
  • CD-28072126-0 Profile Picture
    CD-28072126-0 117 on at
    Hello Waed,
     
    You said:
    1- If you able to do your requirements using AOT query you can use the Query on the form.
    2- If you aren't and you wrote it by code, you can add your tables to the form and add ranges using code on the form.
    3- If you are able to use the AOT query and your form will only show data (read only) , you can connect the query to view and add the view as form data source.
    and as I told you, my query is too complex to be represented using an AOT query.  The last sentence of my original post was:

    Can I bind my  form to a temporary table and populate the table via X++ in a form event?
    That was always my idea from the start, but I had not seen that technique described anywhere, and I wasn't sure that it was even possible, so I asked.
     
    I then decided to try that approach and I eventually found an example, which I posted in its entirety. When the form runs, I see that the init method is called. This method calls the method that populates the temp table (element.populateRecords) and also calls the method that is supposed to bind the temp table's data to my form (TestTableTblTmp.setTmpData):
    public void init()
    {
        super();
        Box::info("TestTableFrmTmp::init called");
        TestTableTblTmp.setTmpData(element.populateRecords());
    }
    However, the form is blank. element.populateRecords has a Box::info call in it, but no message box appears, so it seems that the method is not being called.

    I remembered to set the table type to TempDB. Other than adding the calls to Box::info, I have not tried to debug it yet, but I will be doing so shortly.
  • CD-28072126-0 Profile Picture
    CD-28072126-0 117 on at
    Hello Waed,
     
    Your post with links to two articles solved the problem. For some reason, when I check the checkbox to accept the answer, it spins forever. I will try to update it later. For the benefit of of others, the temp table is bound to the form in the form's init) method like this:
     
    [Form]
public class TestTableFrmTmp extends FormRun
{ 
    public void init()
    {
        super();
        Box::info("TestTableFrmTmp::init called");
        // linkPysicalTableInstance is for TempDB temp tables
        // setTmpData is for in-memory temp tables
        TestTableTblTmp.linkPhysicalTableInstance(element.populateRecords());
    }
    TestTableTblTmp populateRecords()
    {
        Box::info("element.populateRecords called");
        TestTableTblTmp tmpTable;

        ttsbegin;
        tmpTable.FirstName = "john";
        tmpTable.LastName = "brown";
        tmpTable.insert();
        ttscommit;
       
        return tmpTable;
    }

}




     

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 544 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 450 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 250 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans