‘Using sub queries in Dynamics Ax views

I had a chance to implement yearToDate functionality in Dynamics Ax using views and I thought it would be helpful to share the code for that. This article required reading article mentioned in post at the bottom. The sub queries in View requires fetching of aliases that Ax creates during conversion of x++ to sql, before writing ‘transact sql ‘ using strfmt.

Ax code:

DictView dv = new DictView(tableNum(PayrollPaySTMTLinesViewTotalsYTD_TYL));

str sReturn;

str PayrollPayStatementPaymentDateAlias = SysComputedColumn::returnField(

tableStr(PayrollPaySTMTLinesViewTotalsYTD_TYL),

identifierStr(PayrollPayStatement),

fieldStr(PayrollPayStatement, PaymentDate));

str PayrollAggregateViewAlias = SysComputedColumn::returnField(

tableStr(PayrollPaySTMTLinesViewTotalsYTD_TYL),

identifierStr(PayrollPayStatementLinesViewTotals_TYL_1),

fieldStr(PayrollPayStatementLinesViewTotals_TYL, Code));

str joinPaystatementTableAlias = substr(PayrollPayStatementPaymentDateAlias, 1, strscan(PayrollPayStatementPaymentDateAlias, ‘.’, 1, 100) -1);

str joinAgrregateViewAlias = substr(PayrollAggregateViewAlias, 1, strscan(PayrollAggregateViewAlias, ‘.’, 1, 100) -1);

sReturn = strFmt(‘(select SUM([SUMOFACCOUNTINGCURRENCYAMOUNT]) from’ +

‘[dbo].[PAYROLLPAYSTATEMENTLINESVIEWTOTALS_TYL] child ‘ +

‘join PAYROLLPAYSTATEMENT childPayStatement ‘ +

‘ON childPayStatement.RecId = child.PAYSTATEMENT ‘ +

//’where childPayStatement.PAYMENTDATE>=DATEFROMPARTS (YEAR(ParentPayStatement.PAYMENTDATE), 01, 01)

‘where childPayStatement.PAYMENTDATE>=DATEFROMPARTS (YEAR(%1), 01, 01) ‘ +

//’AND childPayStatement.PAYMENTDATE <= ParentPayStatement.PAYMENTDATE’

‘AND childPayStatement.PAYMENTDATE <= %1 ‘ +

//’AND childPayStatement.WORKER = ParentPayStatement.Worker

‘AND childPayStatement.WORKER = %2 ‘ +

‘AND child.DATAAREAID = %3′ +

‘AND child.PARTITION = %4′ +

‘AND child.CODE = %5′ +

‘AND child.IsEmployer = %6′ +

‘)’,

joinPaystatementTableAlias + ‘.PAYMENTDATE’,

joinPaystatementTableAlias + ‘.Worker’,

joinAgrregateViewAlias + ‘.DATAAREAID ‘,

joinAgrregateViewAlias + ‘.PARTITION ‘,

joinAgrregateViewAlias + ‘.Code ‘,

joinAgrregateViewAlias + ‘.IsEmployer’ );

return sReturn;

Sql Generated

Cast (((SELECT Sum([sumofaccountingcurrencyamount])

FROM [dbo].[payrollpaystatementlinesviewtotals_tyl] child

JOIN payrollpaystatement childPayStatement

ON childPayStatement.recid = child.paystatement

WHERE childPayStatement.paymentdate >=

Datefromparts (Year(T2.paymentdate),

01, 01)

AND childPayStatement.paymentdate <= T2.paymentdate

AND childPayStatement.worker = T2.worker

AND child.dataareaid = T1.dataareaid

AND child.partition = T1.partition

AND child.code = T1.code

AND child.isemployer = T1.isemployer)) AS

NUMERIC(32, 16)) ) AS

YTDVALUE

FROM payrollpaystatementlinesviewtotals_tyl T1

CROSS JOIN payrollpaystatement T2

WHERE ( T1.paystatement = T2.recid

AND ( T1.dataareaid = T2.dataareaid )

AND ( T1.partition = T2.partition ) )

For going through background using sub queries in Views in dynamics, read the article below

http://community.dynamics.com/ax/b/goshoom/archive/2012/10/11/ax2012-subqueries-in-views.aspx

‘Select all’ ctrl-A is selecting only 20 records in the Form grid

There is an issue with the Grids in Ax 2012 where select all or ctrl A or clicking top check box in the grid, selects only 20 records and if you want to iterate or check the number of records through code, it will always show 20 even though the records are more than 20 in the Grid.

The reason behind is, the Grid control in Ax does not load all the records on the initialization of the Form, if the records are more than 20 and user has to scroll the whole grid to make Ax select all the records and to get the correct count in the code.

The more technical explanation of this could be, there are only 20 records marked when the user clicks the top check box in the Grid and to get all the records, code has to mark all the records by iterating through all the rows in the Grid. Something like below

recordsCount = student_ds.recordsMarked().lastIndex(); // Total no of marked records.

studentLocal = student_ds.getFirst(1);

while (studentLocal)

{

info(studentLocal.Name +" " +studentLocal.ID);

studentLocal = student_ds.getNext();

}

User can manually press Ctrl + Shift+ End to select all the records. I also tried passing this key strokes through the code but the key strokes didn’t work through code. The key stroke for Ctrl + Shift+ End is 2824.

More helpful Links can be found below
http://soft-maya.blogspot.com/2012/11/retrieve-multiple-selected-records-from.html

http://community.dynamics.com/ax/f/33/t/107379.aspx

How to check user has applied filter on the records or not

TestTable_ds.queryRun().query().queryFilterCount();

A very helpful link with examples can be found below
http://dynamicsuser.net/forums/t/63208.aspx

TestTable_ds.defaultMark()

How to check user has selected all the record using the top check box in the grid

How to check user has selected all the record using the top check box in the grid

TestTable_ds.defaultMark() method can tell you if the user has selected all the records in the grid on the Form or not

How to get company fiscal calendar

 

Ledger::FiscalCalendar()

How to make field readonly in web service

Question:

I have a bug in web service where we need to make a parameter read. There is a code in validateWrite(..) method of the table that adds an AIFFault::checkFailedLogFault if the field is being changed.However, when testing this using the web service in the visual studio solution no error is being raised and the web service is being allowed to change parameter even though when I step into debug the AIFFault::checkFailedLogFault is being called.

Answer:

There are four ways to achieve this through x++ code. There is nothing that web service can do to make visual studio or any other client stopped editing, because web services are asynchronous in nature and web services are not called on the assignment of the value in any client ( I believe user is associating project to the lines from the client and not the code behind web service is doing that). It will raise error when the client submit the code and will fail the creation of the record in case of any error.

1) Write the code on the validate write of the table and return false from there with the checkFailed error message.

2) Delete the field from the query so that user can’t see that field.

3) Manipulate through code in the prepareForSaveEntended method of the Axd class and check if there is a project, blank the field or ignore the user value and use the table value.

4) Use the removeFieldAsTouched() method

 

How to add reference group control through code

#Define.controlName(TestControl)

FormReferenceGroupControl testReferenceControl = Grid.addControl(FormControlType::ReferenceGroup, #controlName);

testReferenceControl.dataSource(tableStr(testDataSourceTable));

testReferenceControl.referenceField(fieldNum(testDataSourceTable, testField));

testReferenceControl.visible(false);

How to set focus on the reference control

Referencegroup.controlnum(1).setFocus();

Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: