Need:

Single table in CRM Contact view that summarizes Invoice Line Items from Zoho Books and equivalent historical data from previous bookkeeping systems.

Use Case:
We are a direct-to-consumer ecommerce company. When a customer contacts us, we have a customized Canvas view of that individual that shows the customer's information, tickets in Desk, phone calls, etc.

I would like a table in that view that shows the individual items that the customer has ordered in the past so that representatives can best assist, as knowing product details is important for support.

We're currently using Zoho Finance, but that's only been since 2020 (we have order history back to 2006 from previous systems). Therefore, we need the information to be combined and displayed.

The data from the previous system is static (not changing or being updated). That's easy enough to store as a table.

The new data (from Finance) is not static. Theoretically, invoices can be tweaked, etc., and I would prefer that be reflected in CRM accurately.

The optimal way to accomplish this (in my mind, at least) would be to have the historical data in its own table, the Invoice Line Item data from Finance in its native table, and then CRM displaying a table that's essentially an SQL UNION of the data from those individual tables.

Is this (or something that is close to it) possible?

My fallback position is to create a Related List in CRM, import the historical data, and have a workflow that adds new records to the Related List as Zoho Books Invoices are finalized.

The issue with this fallback approach is that it's not dynamic; it creates a second version of the data that ceases to be accurate if things are edited in Zoho Finance. I would prefer to always pull from the Finance data, which is the source of truth.

Is there a way to do what I want, or am I SOL here?