Display value from unlinked table
I have a global Value (Exchange Rates) which I want to use in various other tables.
So far I have a table called "Exchange Rates).
There is a subtable for USD-THB exchange rate which will be updated every now and then.
Now I have other tables, e.g. "Backup Licenses".
There is a value in for the price of a license type in USD. But to calculate our profit, I need to convert to THB (Thai Baht). I can however not use the Exchange Value if the tables are not linked together.
If I link them, I need to add it as a Combo, Popup etc. box which means for every new record, we have to click on this field first to become active or else it won't show the Exchange Rate. I would much more prefer to just having a formula field which calculates e.g. the USD40 from table 'Backup Licenses' * 'Exchange Rate.Rates.Rate' and show the result. Then again, without actually linking the table it doesn't work.
2 Options would work for us.
a) linking to Exchange Rates but have it automatically show the USD Value. Currently we have to click on USD as per screenshot above. Only then it shows the exchange rate value. (We don't need EUR and THB, just USD).
b) not linking and in Profit field use a formula similar to the one below.
'Client pays THB /y' * Qty - 'Backup License Fees'.'License Fee USD /y' * Qty * last(Currency.RATES.RATE)
whereby last(Currency.RATES.RATE) would have to point to the table and field I want to use for the calculation.
Sorry for the lengthy post but I don't know how to explain shorter LOL.