Hi Jonathan,
I’m not sure how you were able to link the ReceivePayment.TxnDate field to the Invoice table, but this relationship doesn’t really make sense. The relationship between invoices and receive payment applied lines is one to many, so there’s no way to specify which of these lines the value in the invoice table would be linked to.
There are couple of ways in which you can approach this. The first would be to run an action set that would retrieve the transaction date from the related ReceivePaymentAppliedToTxn record and store the value in a custom field on the Invoice table. If you’re always receiving payments in full, retrieving the first value returned should be sufficient. This action would loop through your ReceivePaymentAppliedToTxn or your Invoice table and would take some time to complete.
The second would be to write an SQL override statement to populate this field. You are essentially doing the same thing as the action set, but the field will be dynamic and will populate immediately. One caveat is that you will be unable to use the search function in this field.
Keep in mind that both of these options will simply retrieve the first matching value. If your invoice has more that 1 payment applied to it, the payment date returned will just be from the first payment.
Consider my suggestions and let me know if you have any follow up questions. I hope this helps.
-Audisho