Sometimes you will need to query a previous record that used to be valid. To do this you can do the below.
Note: Remember that this feature allows the unique index of a table to exist for various time spans and not allowing any overlap. This was a new AX 2012 feature.
The below code will ONLY return back the current active record based on the Valid Time State/Date for the current date/time.
select firstOnly omHierarchyRelationship
where omHierarchyRelationship.HierarchyType == omHierarchyType.RecId
&& omHierarchyRelationship.ParentOrganization == omOperatingUnit.RecId;This select below will NOT WORK to pull a record for a previous From/To date/time (historical non-active record).
select firstOnly omHierarchyRelationship
where omHierarchyRelationship.HierarchyType == omHierarchyType.RecId
&& omHierarchyRelationship.ParentOrganization == omOperatingUnit.RecId;
&& omHierarchyRelationship.ValidFrom >= utcDatePassed;
&& omHierarchyRelationship.validTo <= utcDatePassed;This select will WORK to pull a record from a previous From/To date/time (historical non-active record).
select firstOnly validTimeState(utcDatePassed) omHierarchyRelationship
where omHierarchyRelationship.HierarchyType == omHierarchyType.RecId
&& omHierarchyRelationship.ParentOrganization == omOperatingUnit.RecId;Special thanks to Joris de Gruyter for his post below. He nailed it in both select statements and a query. Please scope it out for more information.
Dynamics Ax Musings - Valid Time State/Date Effective Framework - Part2
Hi there,
ReplyDeleteHow is it possible to get the active as well as inactive records in a date range.... ?
I checked Joris's blog and found this:
while select validTimeState(fromDate, toDate) rateTable
where rateTable.RateID == 'DAXMusings'
{
info(strFmt("%1: %2 - %3",
rateTable.RateID,
rateTable.ValidFrom,
rateTable.ValidTo));
}
It makes sense If I want to grab the active records during this date range, but what If I want active as well as inactive records?
Jewl, To state the functionality, in order to make sure I'm answering your question correctly, the new Valid Time State table index setting allows the From/To date functionality to exist for a table without the need to manually create a 'from' and 'to' date field and all logic to keep records from overlapping. AX's UI will automatically look at these records and display only the records which are valid for the current date (or one specified in a field by the user). There are a number of other records which may exist for the viewed table, but they will not be visible because the requested date/date range does not overlap with the record's date range. There can only be one record for that unique index for a given time frame.
ReplyDeleteTo state what you are asking, how can we query records for many date ranges for a given unique index combination?
The 'validTimeState' function in select or while select statements,
// Select all records (even the inactive records for a table which uses validTimeState functionality).
date dateFrom, dateTo;
dateFrom = 01\01\1900;
dateTo = 31\12\2154;
While select validTimeState(dateFrom, dateTo) * from [AX_TABLE]
{
...
}
// Use the below to grab the 'active' records for a specific date
date dateTarget;
dateTarge = 12/11/2010;
While select validTimeState(dateTarget) * from [AX_TABLE]
{
...
}
Is the above what you were asking for clarity for?
Thanks. It's worked
Delete