Share via


Data Sources for Forms

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

A form data source supplies the data that appears on a form. To display data, you assign a data field from the form data source to a property of a control that appears on the form.

Types of Data Sources

You can use the following AOT elements as a data source for a form.

Source

Description

Query

A query is a business object in the Queries node of the AOT. A query uses tables, maps, or views to retrieve a set of data fields. To use a query as a form data source, you use the Query property of the Data Sources node to specify the query for the form.

You might find that a query has set the Dynamic property of the Fields node to No. You set Dynamic to No when you want to specify the list of fields that appear in the query. When you add that query to the Data Sources of a form, the list of fields in the table node include all the fields for the table. For more information about queries, see Query Elements in the AOT.

Table

A table is a business object in the Data Dictionary node of the AOT. A table is an object that contains data records for the system. In addition, a table can define a table relation that enables you to look up data from a referenced table. For more information about tables, see Tables Overview.

To add a table to a form, you drag the table from Data Dictionary > Tables onto the Data Sources node of the form. You can also right-click Data Sources, click New DataSource, and then use the Table property of the new data source to specify the table.

Do not add a table to the form data source where the Visible property of the table is No. The property specifies whether controls bound to that table will be visible on the form. If you try to open a form that includes a table specified as not visible, you will see an Infolog error message. To try to prevent the error message, you can set the AllowCheck property of the table in the form data source to No. The form will no longer check permissions for the specified table.

View

A view is a business object in the Data Dictionary node of the AOT. A view is a stored query that retrieves a specified collection of data records and fields. The data in a view is retrieved when the view is accessed. For more information about views, see View Basics.

To add a view to a form, you drag the view from Data Dictionary > Views onto the Data Sources node of the form.

You might have X++ code in a form or control that attempts to access field that is not included in the select list of the data source. If the form or a control attempts to access a field that was not retrieved, an X++ exception is thrown. In addition, information about the error is added to the Infolog.

To see whether the field is available, use the isFieldDataRetrieved method. You can also add the specified field to the select list of the data source or remove the code that attempts to access the field.

Using Base and Derived Tables with a Form Data Source

The Microsoft Dynamics AX application object server enables you to specify inheritance between database tables. A table inheritance hierarchy is a collection of tables where each table derives from another table in the hierarchy. The table at the root of the hierarchy is known as the base table. The other tables in the hierarchy extend or derive from the base table. Each derived table adds fields and methods that extend the type described by the base table. For more information about table inheritance, see Table Inheritance Overview.

You can use the base or derived table in a table inheritance hierarchy as a form data source. A form that has a base table for a form data source will include one or more derived data sources. The derived data sources enable you to view, create, or update related types of records from the form. For more information about table inheritance and derived data sources, see Derived Data Sources for Forms.

Using Lookup Forms with a Form Data Source

A form data source can include a table that has a table relation. The table relation links a record in the form data source table to a record in a separate table. The table relation uses a foreign key to link to the related record. A foreign key is a column or combination of columns whose values identify the primary key of a record in a table. The table whose primary key populates the foreign key field is known as the referenced table.

The table relation enables you to add a lookup form to a field. The lookup form enables you to view and select a record from the referenced table. When you select a record in the lookup form, the primary key of that record populates the foreign key field of the table. For information about lookup forms, see Lookup Forms Overview.

The following table describes the types of keys in a table relation that enable you to use a lookup form.

Primary key type

Description

Natural key

You use the values in one or more columns to uniquely identify a record. The column or columns contain meaningful data values. For example, you could use CompanyName and PostalCode as a natural key for the records in a table.

Surrogate key

You use the value in a single column to uniquely identify a record. The value in the column is usually a counter or a very large integer where duplication is very unlikely. In addition, the key value is only used to identify the record and should not appear on a form.

Using Joins with Form Data Source Tables

You can add more than one table to a form data source. If one table includes a foreign key that is the primary key of the second table, you can establish a join between the two tables. You join tables in a form data source to obtain the following advantages:

  • You want to use them like a single data source. For example, you join two tables and display fields from each table on the form using a grid. If you modify or add a record in the grid, neither table is updated until the grid row is exited.

  • You want to reflect a parent and child relation between the tables. Typically, the data from the joined tables is visually separated. For example, order records are shown as the parent data source in one grid and order lines appear in a second grid as the child data source.

  • You want to improve performance. A select, insert, or update operation occurs across all the tables in the join relationship.

    Tip

    You can programmatically display data from other data sources by using methods that have display or edit method modifiers. However, a join performs better than using these types of methods.

  • You want to synchronize navigation between tables. For example, every time that you select a new record, both data sources are updated at the same time.

  • You want to specify how form data source methods run for the tables in the form data source. Form data source methods such as active, next, and executeQuery are directed to the parent table of the join. However, the init method is run for all tables in the form data source.

Gg844014.collapse_all(en-us,AX.60).gifSetting the JoinSource Property

To establish a join between two form data sources, you populate the JoinSource property of one data source. You use the property to identify a data source that has a foreign key table relation with the current data source. For information about how to use the JoinSource property, see How to: Join Data Sources for a Form.

Gg844014.collapse_all(en-us,AX.60).gifSetting the LinkType Property

You use the LinkType property to specify the type of join you want for the form. You use the join to combine data sources so that the fields appear to be from a single data source. You can set the LinkType property to InnerJoin, OuterJoin, ExistJoin, or NotExistJoin. To join tables that have a parent and child relationship, you set the LinkType property to Passive, Delayed, or Active. The following table describes the values that you can use to populate the LinkType property.

LinkType

Join type

How the join is performed

Active

Parent and child

The data source is updated immediately when a new record in the parent data source is selected. Continuous updates consume lots of resources.

Delayed

Parent and child

A pause is inserted before linked child data sources are updated. This enables faster navigation in the parent data source because the records from child data sources are not updated immediately. For example, you can scroll a list of orders where you do not want to review the lines associated with the order until you stop scrolling.

ExistJoin

Combined data source

The data source retrieves a record from the main table for each matching record in the joined table.

The differences between InnerJoin and ExistJoin are as follows:

  • When the join type is ExistJoin, the search ends after the first match has been found.

  • When the join type is InnerJoin, all matching records are searched for.

InnerJoin

Combined data source

Retrieves a record from the main table that matches records in the joined table and vice versa.

There is one record for each match. Records without related records in the other data source are eliminated from the result.

NotExistJoin

Combined data source

Select records from the main table that do not have a match in the joined table.

OuterJoin

Combined data source

The data source retrieves records from the main table. The records are retrieved whether they have matching records in the joined table.

Passive

Parent and child

Linked child data sources are not updated automatically. Updates of the child data source must be programmed on the active method of the master data source.

See also

How to: Join Parent/Child Data Sources for a Form

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.