Let me describe the two tables I think I need to work with. I say "think" because I'm new to NAV and Jet Reports.
In Sales Line, we have (among others) Document No_, No_, and Quantity. There is one record per Sales Line.
In Reservation Entry, we have (among others) Entry No_, Item No_, Quantity, Source Type, and Source Subtype. There are two records per reservation that are "linked" by Entry No_.
The fact that there are two entries per reservation is where I am getting hung up.
I came up with the beginnings of a Stored Procedure and it looks like this:
-- First, define some variables
DECLARE @EntryNo int
DECLARE @SourceId VARCHAR(MAX) = 'SO10517'
DECLARE @ItemNo VARCHAR(MAX) = '11501'
-- Find the @EntryNo
SELECT @EntryNo = [Entry No_]
FROM [Company$Reservation Entry]
WHERE [Source ID] = @SourceId
AND [Item No_] = @ItemNo
--This will result in one record (or none). This isn't enough information because we don't know what kind of reservation is defined in the companion record.
--PRINT @EntryNo
--Now find the companion record
SELECT [Entry No_]
, [Positive], [Item No_], [Location Code]
, [Quantity], [Description], [Creation Date]
, [Source Type], [Source Subtype]
FROM [Company$Reservation Entry]
WHERE [Entry No_] = @EntryNo
AND [Item No_] = @ItemNo
AND [Source Type] <> 37 --this is the record we found above so we don't need it again
GO
--This will give us one or more records. It could be a reservation to a PO or to inventory or an assembly order, etc.
With that out of the way, let me restate my need. For every Sales Line that is returned, I want to return Quantity from the companion record of the record pair found in Reservation Entry. I specifically want to
Any help would be very much appreciated!
