Payment line items - purchase record association

[neil.interactit]

Hey guys,

I have a documentation system such that invoices/authorisations are attached to line items of a creditor purchase batch ...

The client now wants the associated invoices/authorisations to be available against line items on the payments batch page for review prior to approving the payment ...

I'm stumped trying to correctly associate payment line items back to the purchase line item they relate to.

On the purchase page, I can work with DispersalKey (CR_BatchTranLines.ReceiptLineID) or TranKey (_form.CRBatchTransObject.TransLines.RecID), but on the payment page I can't find any unique key per line item (either in the _form.CRChequePayObject or in the _form.grdLines columns) to get me back to the associated purchase.

What am I missing?

If you prefer, to ask the same question from a database perspective ...

I can see the data for the purchase:

Code: Select all

SELECT * FROM  CR_BatchTranLines WHERE ReceiptID = (SELECT ReceiptID FROM CR_BatchTrans WHERE BatchNum = '024366')
ORDER BY LineNum


And for the payment:

Code: Select all

SELECT * FROM CR_BatchPayLines WHERE CR_BatchPayID = (SELECT CR_BatchPayID FROM CR_BatchPayment WHERE BatchNo = '006780')
ORDER BY LineNumber


But I can't work out how to traverse the database from a payment result line back to the associated purchase line.

Thanks in advance!
Cheers,
Neil.

[Mike.Sheen]

Hi Neil,

The CR_TransID column of CR_BatchPayLines will reveal what row of CR_Trans the payment is linked to. From there you link CR_Trans.SourceID to CR_BatchTranLines.ReceiptLineID - but only if the CR_Trans.Source is 'Creditor Sourced Purchase' - if it's something else, then you need to join in a different set of tables - depending on the CR_Trans.Source.

Mike

[neil.interactit]

Hi Mike,

Thanks. That make sense. Once I have the CR_Trans record, how do I determine the CR_BatchTranLines item that it relates to?

Cheers,
Neil.

[neil.interactit]

Duh! Found it.

CR_Trans.SourceID relates to CR_BatchTranLines.ReceiptID.

Couldn't see for looking!

Cheers,
Neil.