I've combed through the forums and haven't quite found the solution Im looking for. I have a DB which receives updated daily exchange rates and stores them, and customers who transact in multiple currencies. I'm trying to figure out how I can use this information in my QV app to allow the selection of the base currency + the date for the exchange rate. I see something similar in the Salesforce app, but my data is different and I was hoping to use it as-is without transformation to fit the salesforce example.
- USER_CONF table contains 'CURRENCY' (ISO code) of the customer and other personal information
- DW_DAILY table contains the customers transactional history I want to convert to the selected currency
- CURRENCY table contains the ISO code, full name of the currency and if its the system wide default
* the above tables all have clear associations
The following two tables have associations to each other but not the rest
- CURRENCY_CONV table contains a CURRENCY_CONV_ID, BASE_CURRENCY and CUR_RATE_DATE
- CURRENCY_CONV_RATE contains the conversion_id, CURRENCY_CONV_ID, TO_CURRENCY (ISO code), currency_rate_date
I would like to build into my app the ability to select the Base Currency 'CURRENCY', and the date of the rate to use 'CURRENCY_RATE_DATE' similar to the way the Salesforce app does, except of course selecting the date of the exchange rate.
Not sure I understood you task well enough, moreover I'm not familiar with Salesforce, but at first sight it's necessary:
1. To link CURRENCY_CONV table with CURRENCY table via BASE_CURRENCY and CURRENCY fields, correspondingly, by renaming one of them
2. To create expression or variable with calculation of total rate to the end of selected period - this is done by multiplication of exchange rate coefficients in the corresponding period, say:
Exp(Sum({<FILTER PERIOD/DAYS AS YOU NEED>} Log(1+RATE)))-1
