Select statement on valid time state tables

(0) Share

Report

Posted on by D365 beginner

636

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

I have the same question (0)

All responses (4)

Answers (0)

Jeson 120 on at

Like (0)

Report
Copy link

Link copied!

Try this code:

Select firstonly table order by RecId asc;

Was this reply helpful? Yes No
D365 beginner 636 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Jeson 120 on at

Like (0)

Report
Copy link

Link copied!

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).

Was this reply helpful? Yes No
GirishS 27,843 Moderator on at

Like (1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No