I support an organization fundraising in a variety of currencies (USD, GBP, EUR, CAD, ILS). To simplify the process for the person maintaining the list, I have a single number field for the donation amount and a single linked field to select the appropriate currency (this is opposed to having 5-6 different currency fields on the intake form and needing to choose the correct currency field each time). We found that it was easier and faster to use a linked filed as opposed to a single select for currency.
I would like to keep a running total of all the donations' values in both USD and EUR so I can use rollup fields to display overall amounts on a dashboard (i.e. my 100 donations in a variety of currencies are worth a total of USD 10,000 or EUR 9,500).
Exchange rates are "static" in that if on the day of the donation, the ILS:USD rate is 4:1, it should stay calculated at that rate forever, even if in a year from now the rate is different. Exchange rates will be updated once a year based on an average rate--no need for daily updates.
I cannot figure out how to create an automation (or other system) that looks for a new donation entry and makes the correct currency conversions. I'm open to using formulas, scripting (though I don't know it well yet), automations, changing to a single select field, Make, etc., but do not want to purchase another add-on.
Maybe I'm overthinking and the best solution is simply to have separate currency fields instead of trying to do this complicated conversion on the backend.
Thanks for any suggestions?
