Description:-
We can let the system manage data relationships that are valid
only during a specified date range. This can be achieved by setting
“ValidTimeStateFieldType” property on the table.
The system then adds the ValidFrom
and ValidTo columns that track a date range in each row. The system guarantees
that the values in these date fields remain valid by preventing overlap among
date ranges, based on the primary key value.
Let me explain with an example. Let us create a new table with a
new string field “Name” which extends Name EDT. A valid time state table lets
you simplify the maintenance of data for which changes must be tracked at
different points in time. You can set the ValidTimeStateFieldType
property to make it a valid time state table. That causes the system to
automatically add the ValidFrom and ValidTo
columns which track a date range in each row.
The system guarantees that the values in these date or date-time
fields remain valid by automatically preventing overlap among date ranges. The ValidFrom and ValidTo columns can
both be of the date data type, or can both be of the utcDateTime data type.
Step 1: Create Table
Name it “A_ValidTimeStampTable”.
- Expand AOT Node.
- Open Data Dictionary Node.
- Select Tables and right Click Select New Table.
- Name it “A_ValidTimeStampTable”.
- Now open table in Insert Some Data in A_ValidTimeStampTable table.
Step 2: Now Fill the
ValidTimeStateFieldType Property to Date in Table Property. Then you can See
Two Field Automatically added in your table.
Step 3: Now Add Field
in Table and name it “Name”.
Step 4: Now go to indexes
of table Node and Create New Index and Name it “NameIDx” and Drag and Drop Name,
ValidFrom and ValidTo in Indexes.
Step 5: Now Select
Created Index and Set Property to Below Image.
In this Property you can See No
Duplicates Value Allow in Index, Alternate Key Should be required for
ValidTimeStateKey. Then After you can Set ValidTimeStateKey and
ValidTimeStateMode Property. Now ValidTimeStateMode Property Set to Gap. It Means
Gap between ValidFrom to ValidTo Date Which one you fill in those two Fields.
Step 6: Now Open your table and fill data so you Can Create Gap Between
to date FromDate and ToDate
In this table you can see Gap Date it means 14-15, 14-16 NoGap 16-18 Gap and 20-22 Gap. So you can Create Gap Date in Date filed When ValidTimeStateMode is Gap.
Step 7: Now Change ValidTimeStateMode Property to NoGap and Compile and
Fill Value in table.
Step 8: Open you table and Fill Data but don’t run.
The NoGap value tells the framework we do not allow gaps in the date ranges. For example, for the same Name, NoGap will not allow one record January 15 to February 15 and a second record of March 15 to April 15, since there would be a gap between February 15 and March 15. We can easily test this once our table is set up.
Let's open
the table browser by right-clicking and selecting "Open". Create a
new record by hitting CTRL+N on your keyboard. Notice how the record defaults
to ValidFrom with today's date, and ValidTo set to "never" (if you
put your cursor on the field, you'll notice how the "never" value is
actually "12/31/2154"). Give it a Name of "Umesh" (yeah!)
and save the record (CTRL+S).
If you enter the Name "Umesh" again and try to save the
record (CTRL+S), you will get the error "Insert not supported with the
values specified for 'Effective' and 'Expiration'. New record overlaps with multiple
existing records".
Step 9: Now Clear All Table Data and Change ValidTimeStateFieldType to
“utcDateTime”. Then Two Field ValidFrom and ValidTo Automatically Added in your
Table.
Step 10: Now Drag and Drop Those Two Field in Created Indexes “NameIDx”.
Step 11: Now Changes Property in NameIDx Indexes Change it and Change
ValidTimeStateMode to Gap.
Gap Gap is allowed in a
date range.
No Gap No Gap is allowed in
a date range.
Now if you will enter Same Date and Name in ValidFrom DateTime then
you will get Error.
Step 11: Now Changes Property
in NameIDx Indexes Change ValidTimeStateMode to NoGap.
Now Enter Same Date in another DateTime Field and Check you will get
Error while inserting Record in Field.
Check
your Self
Example 1:
static void ValidTimeStamp(Args _args)
{
A_ValidtimestampTable
A_ValidtimestampTable;
date FromDate = today();
date ToDate = FromDate +1;
While SELECT
validTimeState(FromDate, ToDate) * from A_ValidtimestampTable
order by
A_ValidtimestampTable.ValidFrom
{
info(A_ValidtimestampTable.Name);
}
}
Example 2:
static void ValidTimeStamp1(Args
_args)
{
A_ValidtimestampTable
A_ValidtimestampTable;
date FromDate = today();
date ToDate = FromDate +10;
while select validTimeState(fromDate,
toDate) A_ValidtimestampTable
//where A_ValidtimestampTable.Name ==
'Jignesh'
{
info(strFmt("%1: %2 -
%3",
A_ValidtimestampTable.Name,
A_ValidtimestampTable.ValidFrom,
A_ValidtimestampTable.ValidTo));
}
}
Example 3:
static void ValidTimeStamp2(Args
_args)
{
A_ValidtimestampTable
A_ValidtimestampTable;
utcDateTime fromDateTime, toDateTime;
;
fromDateTime =
DateTimeUtil::newDateTime(3\1\2016, 0);
//toDateTime = DateTimeUtil::newDateTime(5\1\2016,0);
toDateTime
= DateTimeUtil::maxValue();
select validTimeState(fromDateTime)
A_ValidtimestampTable;
info(A_ValidtimestampTable.Name);
select validTimeState(fromDateTime) * from
A_ValidtimestampTable;
info(A_ValidtimestampTable.Name);
select validTimeState(fromDateTime) Name
from A_ValidtimestampTable;
info(A_ValidtimestampTable.Name);
select validTimeState(fromDateTime,
toDateTime) Name from A_ValidtimestampTable;
info(A_ValidtimestampTable.Name);
ttsBegin;
while select forUpdate
validTimeState(fromDateTime) A_ValidtimestampTable
{
A_ValidtimestampTable.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
A_ValidtimestampTable.Name = 'Umesh';
A_ValidtimestampTable.update();
}
ttsCommit;
}
Example 4:
static void ValidTimeStamp3(Args
_args)
{
A_ValidtimestampTable A_ValidtimestampTable;
utcDateTime fromDateTime, toDateTime;
Query q;
QueryRun qr;
QueryBuildDataSource qbds;
;
fromDateTime =
DateTimeUtil::newDateTime(3\3\2016, 0);
toDateTime
= DateTimeUtil::maxValue();
q = new Query();
qbds =
q.addDataSource(tableNum(A_ValidtimestampTable));
q.validTimeStateAsOfDateTime(fromDateTime);
qr = new QueryRun(q);
while(qr.next())
{
A_ValidtimestampTable =
qr.get(tableNum(A_ValidtimestampTable));
info(A_ValidtimestampTable.Name);
}
}
Thanks for comments.....