Auto create a table relationship

Hi Quickbase users,

I have a table with a Date field and a Numeric field which can be either dollars or euros.
One more field specifies the currency (i.e. "USD" or "EUR").

Now I am trying to create a new calculated Numeric field which would have everything in EUR.
To achieve this I created a second table with daily USD-EUR FX rates (it has date and rate fields).

What I am trying to achieve is to automatically retrieve the exchange rate for a relevant date from the FX table and multiply it by the Numeric field in the first table.

I tried to create a table to table relationship but this way you need to link these tables manually.
I am looking to automatically link them using the date field as reference.

Hope it makes sense.

Thank you,
Dmitry