Lookup a value in a second table using two relationship fields
I have two simple tables (client and currency).
In the client table I have the fields client ID, amount, currency, date, rate and amount$.
In the currency table I have the fields currency, date and rate
I have tried to use the auto-enter's Looked-up value by specifying rate for the datafield to be returned.
I've set the relationship to client:currency = currency:currency and client:date = currency:date, expecting that if I enter a valid currency code and date I'd get the rate to calculate the amount in $.
Thanks for any help!