Skip to main content

Working with Multiple Datasource on a single Form

Today we will discuss how we can display records from multiple datasources in a single form. Data sources on a form allow the form to display the data from the table specified on the data source. A table in a data source can be sorted and filtered by the user, which is an important feature of Microsoft Dynamics AX.
Forms can have multiple data sources, and each data source can be joined to another data source on the form using the defined relations between the tables. Relations or Dynalinks can also be added in the form code.
A form data source produces a query that can be manipulated in the same way as any other query in the system.
If you wish to display records from two tables on one form, you can limit the records in one of the tables to the related records in the other table. You can do this by specifying a join between the two tables. The join is specified on the JoinSource property of the second or joined table. Here you should enter the data source name of the main data source. You can also specify the type of join.


The following table describes the types of join available:
  • Passive: The query on the joined data source is only executed when the form is opened. A later change in the controlling data source does not change the view.
  • Delayed: The query on the joined data source is executed every time that the controlling data source is changed. The query execution is delayed to avoid the fetch of data, if the controlling data source is changed multiple times in a short time. This is the case when the user is scrolling through data on a grid.
  • Active: This option is similar to Delayed, except there is no delay before the query is executed.
  • InnerJoin: Selects records from the main table that have matching records in the joined table and vice versa. If the joined table does not have any records related to the main table record, the main table record is not displayed. Each match returns a result set of the main table record and joined table record joined together as one record. This is useful when wanting to display records from both tables in a grid.
  • OuterJoin: Selects records from the main table whether they have matching records in the joined table. Each match returns a result set of the main table record and joined table record joined together as one record. If there is no match, the fields from the joined table will be empty.
  • ExistsJoin: Selects a record from the main table only if there is a matching record in the joined table. As soon as a matching record is found, the main table record is returned. The record in the joined table is never retrieved.
  • NotExistsJoin: Select records from the main table that do not have a match in the joined table.

Labels:

Popular posts from this blog

What does this mean: "The form datasource query object does not support changing its AllowCrossCompany property after the form has executed the query."?

I have made a form with datasources vendtable and vendtrans. Inside vendtable_ds.executequery() looks like this: QueryBuildDataSource queryBuildDatasource ,queryBDS_VendTrans_Invoice; ; queryBuildDatasource = this.query().dataSourceTable(tablenum(vendtable)); queryBDS_VendTrans_Invoice = this.query().dataSourceTable(tablenum(vendtrans)); if (curext() == "MASTERCOMP") { this.query().allowCrossCompany(true); } else { this.query().allowCrossCompany(false); } //FilterVendorName = stringedit control on form if (FilterVendorName.text()) { queryBuildDatasource.addRange(fieldNum(VendTable,Name)).value(strfmt("*%1*", FilterVendorName.text())); } else { queryBuildDatasource.clearRange(fieldNum(VendTable,Name)); } //FilterInvoiceNumber = stringedit control on form if (FilterInvoiceNumber.valueStr() == "") { queryBDS_VendTrans_Invoice.enabled(false); } else { queryBDS_VendTrans_Invoice.enabled(true); queryBDS_VendTrans_In...
Read more

Credit Note [Dynamics AX] using X++

This post will help to create credit note for a sales order based on the invent lot id. All the invoices raised for a particular sales line – Lot Id will be raised back as a credit note. Information on Credit Note: A credit note or credit memorandum (memo) is a commercial document issued by a seller to a buyer. The seller usually issues a Credit Memo for the same or lower amount than the invoice, and then repays the money to the buyer or sets it off against a balance due from other transactions Below Code will help to create credit note for all the invoices raised against the sales line -lot id. Please note: This code can be customized as per your requirements. This is just a template to help creating credit note using X++ code. Please test the code before use. static void SR_CreateCreditNote_Sales(Args _args) { // Coded by Sreenath Reddy CustInvoiceTrans custInvoiceTrans; Dialog dialog = new Dialog(“Create credit note – for sales.”); DialogField dfInv...
Read more