SBX - Search With Button

SBX - Forum Post Title

How to Fetch 1st inserted data from purchlineAllversion

Microsoft Dynamics AX Forum

DarkE asked a question on 14 Jun 2018 1:19 PM

Question Status

Unanswered

I am going to create AX2012 R3 RDP SSRS Report to show Vendors Total purchamount and invoiceamount and Variance between purchamount and invoiceamount

purchamount should be 1st entered record in purchline  

when purchline amount revised it will affected in  table purchlineAllversion has more than one version of purchamount how to fetch the 1st entered record

with purchline Itemid I can  join purchlineallversion to get top one record sort by desc 

but with respect to GL account, there is know itemid then I don't know how to fetch the purchamount.

I have an idea Linenumber is same for versions so with use of line number we can get 1st enetered record

I am in the confusion on how to get those record using linenumber

purchlineallversion 

purchid linenumber lineamount VersionDatetime
P001 1 50 6/14/2018 13:00:00
P001 1 45 6/14/2018 14:00:00
P001 2 60 6/14/2018 16:00:00
P001 2 75 6/14/2018 18:00:00

I need to fetch those 1st entered data 

purchid : P001 and Lineamount : 50 linenumber :1

purchid : P001 and Lineamount : 60 linenumber :2

Thanks!!!

Reply
Sohaib Cheema responded on 14 Jun 2018 1:47 PM
My Badges

you may trust an algorithm as following

IF(PO never confimed)

{

//take price of line from PurchLine

}

Else

{

//find 1st confirmation of PO in which this PO line exists.

//take price from confirmation journal line

}

This logic can be seen in createData(method) of PurchPurchaseOrderDP(Class)

Reply
DarkE responded on 14 Jun 2018 3:51 PM

Hi Sohaib,

Thanks for your quick response I have another doubt.

I am can achieve 1st entered record in sql server I need to get this in X++

CREATE TABLE [dbo].[PurchLineAllVersion](

[PurchID] [varchar](50) NOT NULL,

[LineNumber] [int] NOT NULL,

[LineAmount] [int] NOT NULL,

[VersionDateTime] [datetime] NOT NULL

) ON [PRIMARY]

insert into Purchlineallversion

(Purchid,[LineNumber],[LineAmount],[VersionDateTime]) values

('P001',1,50,'6/14/2018 13:00:00'),

('P001',1,45,'6/14/2018 14:00:00'),

('P001',2,60,'6/14/2018 16:00:00'),

('P001',2,75,'6/14/2018 18:00:00')

select * from Purchlineallversion where [VersionDateTime] in ( select Min([VersionDateTime]) from Purchlineallversion group by linenumber)

Thanks!!!

Reply

SBX - Two Col Forum

SBX - Migrated JS