Select statement on valid time state tables

(0) Share

Report

Posted on by D365 beginner

572

Hi Experts,

Below is a sample table where the valid time state field type is selected as Date in the table properties. I need to select the first record in my code (the first record created as per the Rec id in the table), the record with salary 1000 in the below case but the select statement brings the current record only which is the last one since the table is date effective. what should be the statement to get the first record ? Thanks in advance.

Employee	Valid From	Valid To	Salary
123	01/01/2020	31/12/2020	1000
123	01/01/2021	31/12/2021	2000
123	01/01/2022	31/12/2022	3000

All responses (4)

Answers (0)

GirishS 27,832 Super User 2024 Season 1 on at

Like (1)

Report
RE: Select statement on valid time state tables

Hi,

For this scenario you need to mention the from and to date using validtimestate.

Validfrom fromDate = 01\01\2020; ValidTo toDate = 12\31\2020; Select validtimestate(fromDate,toDate) * from table;

Thanks,

Girish S.
Jeson 120 on at

Like (0)

Report

RE: Select statement on valid time state tables

honestly for now i don't really know whats the mystery behind that but i found solution. You just create view for that particular table and set the order by to ascending order and use the this code to get the first RecID ( select firstOnly view order by RecId asc).
D365 beginner 572 on at

Like (0)

Report

RE: Select statement on valid time state tables

Thanks Jeson for your reply. I had tried the same already but it doesn’t work. I need to know that if the table is date effective, the normal select statement would work or not.
Jeson 120 on at

Like (0)

Report

RE: Select statement on valid time state tables

Try this code:

Select firstonly table order by RecId asc;