Creating a Querie having multiple Data sources

In this post we will be creating a Query having multiple Data sources which be be further used in some other places such as forms, parts, Reports etc. In this scenario we will be having 3 different tables i.e. Students, StudentsCourses and Courses table. We will be creating a query containing these 3 tables as data source in a specific way such that their relation is maintained.

Below are the following three tables:


Now let's create our desired query:


  1. Go to Queries Node in the AOT.
  2. Right Click on Query and Select New Query.
  3. Rename the Query to "DemoQuery". 
We are all done with creating our Query.


 Now we will be adding Data Sources to our Query.
Open a new AOT (CTRL+D) and navigate to the Data Dictionary --> Tables and select the required table. in this case we will select Students Table and drag it on the Data Source Node of our Query.


Expand the Students node and Set the Fields Dynamic Property to Yes.
This will add all the Table's fields to the data source.

Now in the Students Data Source Drag and Drop the Students Courses table to the Data Source node of Students:

Similarly Set the fields Dynamics property to Yes.

Now in the StudentsCourses Data Source Drag and Drop the Courses table to the Data Source node of StudentsCourses :

Now we will set the Relations among the data sources :

First we will set relation of StudentsCourses_1 data source with Students_1 Data Source.
Navigate to the Relations node in the StudentsCourses_1 Data Source and add New Relation:


The relation of StudentsCourses_1.StudentId should be with Students_1.StudentId:


Also make sure that right fields are set in the Properties window:


Similarly, make the relation of Courses_1 data source with the StudentsCourses_1 data source i.e. Courses_1.CourseId and StudentsCourses_1.CourseId:

Also make sure that the right fields are set in the properties window:


Now, this is all done with this Query. The behavior of this query is that it will map the particular student with its particular courses which are assigned to that student, as seen in the relations we have created.

Comments

  1. Anonymous7 July 2015 at 00:53

    Iam really satisfy by your information. It's well-written, to the point, and relative to what I do. I like it very much for giving information on. I hope you can continue and post more.
    Microsoft Dynamics CRM Online Training | Dynamics AX Online Training

    ReplyDelete

Post a Comment

Popular posts from this blog

D365: SSRS Report Development/ Customization

Image
Hi guys, Today in this blog I will be demonstrating the procedure for the SSRS Reports development and customization in D365FO. Before proceeding with the Development or customization make sure that you have created a new model for your customization since we will be using extensions approach in our customization because An extension enables you to add functionality to existing model elements and source code. Coming back to the main objective of this blog which is to Develop SSRS reports in D365. One point to keep in mind is that there is no change on How you develop query based report and RDP based report in D365FO . The only change is you have to use extensions for standard report. If you are developing brand new report based on custom tables, there is no change. So in this Blog I will discuss How to customize an existing SSRS Report in D365 using Extensions approach step by step: Step#1:  The first step is to create a new model, Below are the steps to create a new model: Navigate to
Read more

D365: X++ code to add custom lookup on worker to show specific workers team workers only

/// <summary>             ///             /// </summary>             /// <param name = "_formReferenceControl"></param>             /// <returns></returns>             public Common lookupReference( FormReferenceControl _formReferenceControl)             {                 SysReferenceTableLookup      sysTableLookup = SysReferenceTableLookup ::newParameters( tablenum ( HcmWorker ), _formReferenceControl, true );                 Query                        query = new Query ();                 QueryBuildDataSource         queryBuildDataSource;                  QueryBuildRange              queryBuildRange;                 HcmPosition                  hcmPositionPareant;                 HcmPositionHierarchy         hcmPositionHierarchy;                 utcdatetime                  _validFrom = DateTimeUtil ::utcNow();                 utcdatetime                  _validTo    = _validFrom;                 H
Read more

Error message when you log on to a Microsoft Dynamics AX 4.0 client: "You are not a recognized user of Microsoft Dynamics AX"

When you log on to a Microsoft Dynamics AX 4.0 client after you restore a database, you receive the following error message: You are not a recognized user of Microsoft Dynamics AX. Contact your system administrator for help. This problem occurs if you restore a Microsoft Dynamics AX 4.0 environment in a different Active Directory directory service environment. This problem occurs when one or more of the following conditions are true: The security identifier (SID) is incorrect. The network alias is incorrect. The network domain is incorrect Resolution To resolve this problem, run the following script in SQL Query Analyzer after you restore the database. Note  You must obtain a SID in the current Microsoft Dynamics AX 4.0 environment. --Extract the user's SID from the current installation to update the restored database. select id, SID, Networkdomain, networkalias from userinfo where Id = 'admin'-- networkalias = '<UserAlias>' To extract t
Read more