In ODBC, does anyone know how to join from the revenue_elements table to the transactions table? I can see revenue_elements has the reference_id field which returns CustCred_1474994...but the only way I can get back to the transactions table is to use

CONCAT('CustCred_', transaction_id)

(table 1)

CONCAT('CustInvc_', transaction_id)

(table 2) which seems incredibly bad. Surely there's a simpler way?

Try to do a search from Revenue Plans. It contains a join to the Applied to Transactions table which will give you not just the tranid but also transaction lineid the Revenue Element is from.

I will give it a try, thanks!