Form data source link types - Active, Delay, Passive, Inner join, Outer join, Exist join, Not exist join

Form data source link types

Form data source link type is a property of the form data source. We can add more than one tables as a data source to the form. Those data sources should has the table level relation, So, then the developer no need to work on the coding part to find the relation records. For example, if we create the order form, that order form has orders and order details tables as form datasources. We can add both tables as a data sources to the form.

The parent table and child table should has the table relation. So, once we add these tables in the form as data sources. We can select the child table data source and mention the parent table name in the join source property of the child table form data source property.

Example:

Here, I have created two tables Hari_Order and Hari_OrderDetails. Hari_OrderDetails has the foreign key of Hari_Order table Key is OrderNo.

Hari_Order table

Order No	Customer Name
Ord 2	Ram
Ord 1	Hari
Ord 3	Vithyaa
Ord 4	Uma

Hari_OrderDetails

Order No	Product Name	Product Description
Ord 1	Prod 1	Product One
Ord 1	Prod 2	Product Two
Ord 1	Prod 3	Product Three
Ord 2	Prod 1	Product One
Ord 2	Prod 2	Product Two
Ord 3	Prod 1	Product One

Set the join source and set the link type

Table relation

Use join source and link type

Active

Active link type update the child data sources without any delay when you select the parent table record. When you deal with more records it will be affect application performance.

Delay

Delay form data source link type is also same as active method the different is delay method won't update immediately when you select the parent record. It will update the child data source when you select the parent table, Ax use pause statement before update the child data source. For example, if we are dealing with lot of records so, when the user click or scroll the order, order details will update without any delay if we use active method.

So, We can use delay method because of performance improvement.

Passive

Passive form data source link type won't update the child data source automatically. For example if we select the parent table order then order details child data source won't update. If we need to update the child data source we need to call the child data source execute query method by program (code).

The order details grid is empty. If we need populate the child data source (order details) then we need to call the Hari_OrderDetails_ds.executeQuery() method in the parent table Hari_Order form data source active method. We can add separate button "Populate order details" and call the code Hari_OrderDetails_ds.executeQuery(). So, if the user need to see the order details then the user update by click the "Populate order details" button.

Inner join

Inner join form data source link type displays the rows that match with parent table and child table. For example if the order doesn't has any order details then the order will not be display.

Here, Order 4 does not has the order details records, so it is not coming in the order grid.

Outer join

Outer join form data source link type will return all parent records and matched child records. It will return all rows in the parent table. Here, Order 4 doesn't has the child records (order details) but it is populating in the order grid. It is the difference between inner join and outer join.

Here, Order 4 is coming even order 4 does not has the order details.

Exist join

Exist join form data source link type return matched rows of the parent table. It behaves like inner join but the different is once parent row matched with child records then stop the process and update in the grid, Ax won't consider how many records in child table for the parent row.

Here, Order 4 is not coming because Order 4 does not has the order details.

Not exist join

Not exist join form data source link type is totally opposite method to exist join. It will return the not matched parent records with child records.

Here, Order 4 is coming because order 4 does not has the order details.

Each form data source link type has different behavior. Active, Delay and Passive are one category and Inner join, Outer join, Exist join, Not exist join are another category. So, please understand the behavior and choose the link type based on your requirement.

X++ Implementation of few SQL Keywords

As we already saw in the Previous post, In X++, we can directly use database related statements similar to any other code. Here, we will see and understand how to implement few well-known SQL keywords in X++.

Example(1): To print records in custTable in descending order.
while select custTable order by custTable.AccountNum desc
{
print(strfmt("%1, %2", custTable.accountNum, custTable.Address)); 
}
pause;

Output:

By using order by clause similar to the SQL queries with the keyword ‘desc’, we are able to achieve the expected output.

Example(2): To print Average of Amount from custTrans table.
while select avg(AmountMST) from custTrans
{
print(custTrans.AmountMST); 
}
pause;

Output:

Here, we are using while select statement with the Aggregate keyword ‘avg’ on the required field to get the Average of all the values of AmountMST in the table.
Also note that to print the output, You can get the result from the same field, i.e, custTrans.AmountMST.

The below are Details, Syntax and an AX Example respectively for few well-known SQL keywords:

Keyword	Example
asc	Set the sorting order to ascending. All selects are default fetching data ascending.
	Syntax: select custTable order by accountNum asc;
desc	Set the sorting order to descending. Used in combination with order by or group by.
	Syntax: select custTable order by name desc;
	AX Example: See table method CustTable.lastPayment().
avg	Select uses aggregate keyword (avg) using only one call to the database calculating a result based on multiple records
	Syntax: select avg(amountMST) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
count	Aggregate keyword used to count the number of records fetched.
	Syntax: select count(recId) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
sum	Aggregate keyword used to sum values of a field fetched.
	Syntax: select sum(amountMST) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
maxof	Aggregate keyword used to return the highest field value fetched
	Syntax: select maxOf(amountMST) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
minof	Aggregate keyword used to return the lowest field value fetched.
	Syntax: select minOf(amountMST) from custTrans;
	AX Example: See class method KMKnowledgeCollectorStatisticsExecute.runQuery().
delete_from	Will delete multiple records in one call to the database.
	Syntax: delete_from myTable where myTable.amountMST <='1000';
	AX Example: See class method InventCostCleanUp.updateDelSettlement().
exists join	Exists join is used to fetch records where at least one record in the secondary table matches the join expression. No records will be fetched from the secondary table using exists join.
	Syntax: while select custTable exists join custTrans where custTable.accountNum == custTrans.accountNum
	AX Example: See class method InventAdj_Cancel.cancelInventSettlements().
notexists join	Opposite of exists join. Will fetch records from the primary table, where no records in the secondary table match the join expression.
	Syntax: while select custTable notexists join custTrans where custTable.accountNum == custTrans.accountNum
	AX Example: See class method InventConsistencyCheck_Trans.run().
outer join	Outer join will select records from both tables regardless if there are any records in the secondary table matching the join expression.
	Syntax: while select custTable outer join custTrans
	AX Example: See class method SysHelpStatistics.doTeams().
join	Join will fetch Records matching the join expressionfrom both tables. (innerjoin)
	Syntax: while select custTable join custTrans where custTable.accountNum == custTrans.accountNum
	AX Example: See table method SalesTable.LastConfirm().
firstfast	Instruct to select the first record faster. used in situations where only one record is shown, like in a dialog.
	Syntax: select firstfast custTable order by accountNum;
	AX Example: See class method ProjPeriodCreatePeriod.dialog().
firstonly	First record will be selected. Firstonly should always be used when not using while in selects.
	Syntax: select firstonly custTable where custTable.AccountNum == _custAccount (variable)
	AX Example: See Table method CustTable.find().
forupdate	Used If records in a select are to be updated
	Syntax: while select forupdate reqTransBOM where reqTransBOM.ReqPlanId    ==  this.ReqPlanId
	AX Example: See Table method ReqTrans.deleteExplosionCoverage().
from	Default all fields of a table is selected. From is used to select only the fields specified. Use it for optimization only, as it makes the code more complex.
	Syntax: select accountNum, name from custTable;
group by	Sort the fetched data group by the fields specified. Only the fields specified in the group by will be fetched.
	Syntax: while select custTable group by custGroup;
	AX Example: See class method InventStatisticsUS.calcTotals().
index	Used to set the sorting order of the fetched data. The kernel will convert the keyword index to an order by using the fields from the index. Index should only be used if the fetched data must be sorted in a specific way, as the database will choose a proper index.
	Syntax: while select custTable index accountIdx.
index hint	Index hint will force the database to use the specified index.
	Syntax: while select custTable index hint accountIdx.
	AX Example: See Table method ReqTrans.deleteExplosionCoverage().
insert_recordset	Used to insert multiple records in a table. Insert_recordset is useful when copying data from one table to another as it only requires one call to the database
	Syntax: insert_recordset myTable (myNum,mySum) select myNum, sum(myValue) from anotherTable group by myNum where myNum <= 100;
	AX Example: See class method SysLicenseCodeReadFile.handleDomainLicenseChanges().
update_recordset	Used  to update multiple records in one database call. Useful to initialize fields in a fast way. The fields updated are specified after thekeyword setting.
	Syntax: update_recordset myTable setting field1 = myTable.field1 * 1.10;
	AX Example: See class method ProdUpdHistoricalCost.postScrap().

Introduction to Services and Application Integration Framework

Features :  Description  

1) Services : Application Object Server (AOS) is the Windows Communication Foundation (WCF)                          service host for Microsoft Dynamics AX 2012 services that are exposed to users and                            applications on an intranet.

                     To consume services over the Internet, you must host services on Internet Information                          Services (IIS). Services that are hosted on IIS use the WCF message routing service. IIS                     routes all service requests to AOS. All service requests are processed on AOS, regardless                     of whether they originate on the Internet or an intranet. AOS then returns a response to                         the service consumer through IIS. Exchanges that are configured to use web services are                     processed synchronously and are therefore not queued. Microsoft Dynamics AX deploys                     the service that is based on Web Services Description Language (WSDL) to a subfolder                       of the virtual directory that is associated with the website that you provide.

2) Service operations :                                                                                   

Caller Refresh after action performed in Current Form

In many cases you need to refresh the data source of the caller form when you finishing work with the current form like when you using posting form you need to refresh the data source of the caller form to see the effect write the following code in close method of the current form :

public void close()

{

 FormRun         callerForm;

;

    callerForm          = element.args().caller();

    callerForm.dataSource().refresh();

    callerForm.dataSource().reread();

    callerForm.dataSource().research();

    super();

}

Table keys: Replacement key in AX 2012

Different table keys in AX 2012 R2

As we know many different tables’ keys exist in AX 2012 such as Surrogate key, 

Alternate key, Primary key, foreign key etc.

Since many of them are briefly explained on MSDN.

But there are certain table keys which are difficult to understand just by the definition,

something like “Replacement key”. So let me then showcase the practical scenario like

how you can use it and the benefit of having that property in your table.

What is replacement key??

A replacement key is an alternate key that the system can display on forms instead of 

a meaningless numeric primary key value. Each table can have a maximum of one 

replacement key. The replacement key is chosen by setting the ReplacementKey 

property on the table. The drop-down list offers every alternate key as an available 

value.

 

The drop-down list contains every index that has its AlternateKey property set to Yes.

You might change the default blank value to an index whose field values within each 

record provide a name or other moniker that is meaningful to people. If a 

ReplacementKey is chosen, its fields can appear on forms to helpfully identify each 

record.The ReplacementKey should be a set of fields that represent the natural key.

 

Now let’s look at it in AX 2012, how we can create this key and how does it look.

Firstly am going to create a simple table called “EmployeeTable” which contains 3 fields 

emplId, Name and DOB (dateofbirth).

And now going to create index on this table where “AllowDuplicate” property should be

set as ‘NO’ and alternate key property as ‘YES’ .

As shown below:

 

Once the creation of index is done then we can easily assign the replacement key to this 

table as shown here in the below image.

 

Now creating another table called “EmployeeTransactions” which contains 2 fields 

TotalAmountCharged and TotalHourWorked.

In order to give relation with EmployeeTable, creating foreign key relations and as soon 

as we do that automatically new field (EmployeeTable) got added in our

“EmployeeTransactions” table.

As shown in below image:

Now, I can say that relation exist between the two tables and relation is RecId field of 

employeetable to that of newly created employeeTable field (Int64 – datatype).

NOTE: You can also achieve this relations using EDT.

 

Now let’s create some line in to these tables and check how and what it stores in to 

employeetable field in ‘employeetransaction’ table.

As we have seen from backend, table stores the INT64 field i.e., recId of related

‘employeetble’ record.

 

Since form is the artifact which is user oriented (Client interface). Now let’s create a form

and check how it appears there. So am just creating a simple form which consist of these

two table as datasource and using the joinsource property to join them. Form structure 

looks something like this:

 

So basically, header and line part in form represents “employeetable”&” 

employeetransaction”. Here in header part details can be filled up & emplid can be 

easily accessed and if you look at the line level there is a field called reference which

I dragged from transaction datasource > emloyeetable field.  For reference, just look 

at the form image above.

Now here comes the magic J as soon as you create & save the record in header then 

line level reference gets filled up with related emplid field. But if you look at the backend

in tables it would be stored as recid.

So I can say that Alternate key allows the system to display significant value on forms 

instead of a meaningless numeric primary key value. Each table can have a maximum 

of one replacement key. 

Thus with this we are done with the Replacement key functionality in AX 2012 R2. 

Please input your email-id, if you are looking for XPO of it.

Sequence Number Generating in AX 2012

Create an EDT : Student_ID

Write a code on lode module() on NumberSeqModuleCustomer

datatype.parmDatatypeId(extendedTypeNum(Student_Id));

    datatype.parmReferenceHelp(literalStr("Student Id"));

    datatype.parmWizardIsManual(NoYes ::No);

    datatype.parmWizardIsChangeDownAllowed(NoYes ::No);

    datatype.parmWizardIsChangeUpAllowed(NoYes ::No);

    datatype.parmWizardHighest(999999);

    datatype.parmSortField(79);

    datatype.addParameterType(NumberSeqParameterType::DataArea,true,false);

    this.create(datatype);

   this.mcrLoadModules(datatype);

Lookup in Form with string field

Under Form Field in lookup override Method

public void lookup()
{

    Query query = new Query();
    QueryBuildDataSource _Querybuilddatasource;
    QueryBuildRange _querybuildrange;

   SysTableLookup systablelookup = SysTableLookup::newParameters(tableNum(PurchTable), this);
    SysTableLookup.addlookupfield(fieldNum(PurchTable, PurchId), true);
    super();
    _querybuilddatasource = Query.adddatasource(tableNum(PurchTable));
    _querybuildrange = _querybuilddatasource.addrange(fieldNum(PurchTable, PurchId), true);

    SysTableLookup.parmQuery(Query);

    SysTableLookup.performformlookup();


}