I have an AOT query in Dynamics AX 2012 which consists of the HcmWorker and HcmEmployment tables (among others). My problem is that when I create a report (not SSRS), or otherwise use this query, I only get the employees with a valid time state in HcmEmployment.
I would like to get all employees that has a record in this table, even if they doesn't have a current employment - and only once. The optimal solution is to get the record from HcmEmployment for the current employment, if it exists, and otherwise the last record with a start date before "now". However, I can accept a solution where it joins with the last start date.
I would prefer a solution where I do not need to add code every time I use it (i.e. solve it in the AOT query).
I have tried setting the validTimeStateDateTimeRange property on the query from code, which resulted in duplicate lines if the employee has several employments.
If I set the HcmEmployment datasource to ExistsJoin and firstOnly, then it works in a view, but not on reports (still duplicate records) :( Using this view on reports is not an option, because the users needs to be able to filter on DefaultDimension from HcmEmployment.
How can I solve this problem?
I "solved" my own problem...
For some reason the report did not correctly inherit the properties (ExistsJoin) from the AOT query. After dropping the query on the report again, some compilations and the following code in the query init method, it works :) No more duplicate records!
this.query().validTimeStateDateTimeRange(DateTimeUtil::minValue(),
DateTimeUtil::maxValue());