web

You’re offline. This is a read only version of the page.

Dynamics 365 CommunityForums Finance | Project Operations, Human Resources, ... Item Last receipt date...

Finance | Project Operations, Human Resources, ...

Answered

Item Last receipt date with vendor and cost

(0) Share

Report

Report

Posted on by Tami Farrelly

5,080

I am looking to get the last receipt date of an Item with the cost and vendor.

I queried the IV30300 table with

select distinct itemnmbr, max(docdate) from IV30300 group by ITEMNMBR

Which gives me last transaction date and Item

I did run a select where DOCTYPE = '4' ...I cannot seem to get the syntax right with the max date??

I just need the receipts ...and I need the Vendor (CUSTNMBR) and cost

Can someone help with the script...I am not that proficient with SQL

Tami

Categories:

Microsoft Dynamics GP

I have the same question (0)

All responses Img

All responses (2)

Answers Img

Answers (1)

Verified answer

Dean Marty on at

Like (1)

Report

RE: Item Last receipt date with vendor and cost

Hi Tami

I do not claim to be a SQL expert either

That said, this seems to work in a quick review of my data here, so you can give this a try and see how it works for you

SELECT t.ITEMNMBR, t.DOCNUMBR, r.DOCDATE
FROM (
SELECT ITEMNMBR, MAX(DOCDATE) as DOCDATE
FROM iv30300
WHERE DOCTYPE = 4
GROUP BY ITEMNMBR
) r
INNER JOIN IV30300 t
ON t.ITEMNMBR = r.ITEMNMBR AND t.DOCDATE = r.DOCDATE
WHERE DOCTYPE = 4
ORDER BY ITEMNMBR

Thanks!

Dean

Was this reply helpful? Yes No
Tami Farrelly 5,080 on at

Like (0)

Report

RE: Item Last receipt date with vendor and cost

Thank you so much for your help with this...I will try and tweak to get the fields needed.

Tami

SELECT t.ITEMNMBR, t.CUSTNMBR,t.unitcost, r.DOCDATE
FROM (
SELECT ITEMNMBR, MAX(DOCDATE) as DOCDATE
FROM iv30300
WHERE DOCTYPE = 4
GROUP BY ITEMNMBR
) r
INNER JOIN IV30300 t
ON t.ITEMNMBR = r.ITEMNMBR AND t.DOCDATE = r.DOCDATE
WHERE DOCTYPE = 4
ORDER BY ITEMNMBR

Was this reply helpful? Yes No

Join the conversation

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1

CA Neeraj Kumar 2,047

#2

André Arnaud de Cal... 885 Super User 2025 Season 2

#3

Sohaib Cheema 592 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans