Hi there JonSWR and thank you for getting back to us on this thread. Understanding the data that is populated by the reports in QuickBooks Online is essential in order to appropriately extrapolate it and see where your business stands. I want to expand on the difference you're seeing between the Trial Balance report and the Balance Sheet.
The Trial Balance as you've stated, is showing you the un-revalued amount in CAD as a trial is a projection, and QuickBooks isn't able to project exchange rates. When the report is pulled, QuickBooks can only know the current and historical rates as per when the transactions were entered.
When it comes to the Balance Sheet, in general terms, it's meant to show the actual value currently held, which as you know, requires revaluing foreign currencies. This is why the Balance Sheet report that you're running is showing you the revalued amount in CAD that you expect to see.
I hope this information is helpful and clears things up for you. Have a great evening!