0

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
Paula
  • 1
  • 1

2 Answers2

0

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

HannahMunro
  • 1
  • 1
0

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

DotNetHitMan
  • 931
  • 8
  • 20