Is there a way to have a field from a Sales Order flow through to the Revenue Element / Revenue Plan? I've figured out how to map to Revenue Arrangement line but that's not close enough for a Revenue Plan saved search.

if you find a solution, I would be very interested. Only way to do this that we've found is with custom segments

Will let you know if I figure anything out. Did you look into scripting options at all?

yeah that would work. I'm assuming your field is on the transaction header?

Yes, probably for line level you would need scripting

yeah 😢

The link, you mean ODBC?

you need a custom join as there is no direct reference back to the original transaction for some bloody reason

We were actually just talking this morning about moving some of our saved searches and reports to ODBC for performance

Here's what we have at the minute. It's not great performance-wise and there may be a much simpler way to do it. If sales orders are generating your revenue recognition you'd need to add that context to the last join (our rev rec always originates from invoices or credit notes). We can get back to the originating transaction header (table

ret

) but not the line. We can replicate most of this using Analytics Workbooks, but of course you can't export anything with a decent amount of data

FROM 
  "Company".Administrator.transaction_lines d
   INNER JOIN 
  "Company".Administrator.transactions h ON d.transaction_id = h.transaction_id
   INNER JOIN 
  "Company".Administrator.accounting_periods p ON h.accounting_period_id = p.accounting_period_id
   LEFT JOIN
  "Company".Administrator.transaction_links dl ON d.transaction_id = dl.applied_transaction_id AND d.transaction_line_id = dl.applied_transaction_line_id AND dl.link_type='Revenue/Amortizaton Recognition'
   LEFT JOIN
  "Company".Administrator.transaction_lines ld ON dl.original_transaction_id = ld.transaction_id AND dl.original_transaction_line_id = ld.transaction_line_id
   LEFT JOIN
  "Company".Administrator.revenue_elements re ON ld.revenue_element_id = re.revenue_element_id 
   LEFT JOIN
  "Company".Administrator.transactions ret ON TO_NUMBER(SUBSTR(re.reference_ID,10,100)) = ret.transaction_id AND SUBSTR(re.reference_ID,1,8) IN ('CustCred','CustInvc')

Nice, thanks! This should hopefully speed things up for me if we go ODBC route

no we haven't had a look at that. From memory, that last join is the problem. I don't know why they store it as custinvc_123456 instead of just as a bigint transaction ID.

Lol that's exactly what I was typing, it's probably the last join that's causing performance issues

I bet in the real data source it's pure ID There's some abstraction going on to "help" their customers 😡