CODEWITHSUNDEEP
sqlsage-erp
Paula
Paula

Reputation: 1

Sage 200, Sql joins - POPOrderReturn table and TransactionHistory table

I am trying to join POPOrderReturn table with TransactionHistory table but I cant find an obvsious link between the two.

I am building Purchase Order tracker and wanted to include warehouse GRN number once the PO in booked in to the warehouse. We store that GRN number in TransactionHistory.SecondReference field.

Does anyone know how can I connect the 2 tables?

Thanks in advance Paula

Upvotes: 0

Views: 709

Answers (2)

DotNetHitMan
DotNetHitMan

Reputation: 951

I am pretty sure that you can join the history table to the POPOrderReturn table via the below query. 

SELECT * FROM dbo.TransactionHistory  a
INNER JOIN dbo.TransactionType b on a.TransactionTypeID = b.TransactionTypeID and a.TransactionTypeID in (17,18)
INNER JOIN dbo.POPOrderReturn c on a.Reference = c.DocumentNo

Please note I have added the transaction type in for reference and on my system, the transaction type of 17 and 18 are 'Purchase Order' and 'Purchase Return' respectively.

You may also want to apply a distinct to this to insure a clean return recordset.

HTH

Upvotes: 0

HannahMunro
HannahMunro

Reputation: 1

Normally the GRN is stored within the POPReceiptReturn table. This can be connected to the POPOrderReturn table via the Order Return No. in the POPRcptReturnLineArch

Hannah

Upvotes: 0

Related Questions