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.
|DictView dv = new DictView(tableNum(PayrollPaySTMTLinesViewTotalsYTD_TYL));
str PayrollPayStatementPaymentDateAlias = SysComputedColumn::returnField(
str PayrollAggregateViewAlias = SysComputedColumn::returnField(
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’ );
|Cast (((SELECT Sum([sumofaccountingcurrencyamount])
FROM [dbo].[payrollpaystatementlinesviewtotals_tyl] child
JOIN payrollpaystatement childPayStatement
ON childPayStatement.recid = child.paystatement
WHERE childPayStatement.paymentdate >=
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
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
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);
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
A very helpful link with examples can be found below
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
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.
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
FormReferenceGroupControl testReferenceControl = Grid.addControl(FormControlType::ReferenceGroup, #controlName);