I have a tricky one.
There are two types of TransactionType, claims and payments
In this table there is no existing field that links a claim to a payment so I need to create one. There is however, a field called ServiceID that links them all together
Usually the order of events is
ServiceID 1: Claim 1 --> Payment for 1 --> Claim 2 --> Payment for 2
so the paymentID would just be the next transaction for that ServiceID. easy...
But sometimes it goes a bit off:
ServiceID 2: Claim 3 --> Claim 4 --> Payment for 3 --> Payment for 4
but, the order of the payments always relates to the order of the claims
I can either assign the Claim ID to the Payment record or the Record ID to the Claim record, or both, whichever is easier.