I'm trying to get the currency exchange rate for each day into the sales table.
The currency exchange table has the fields "date_effective", "from currency id", "to currency id" and "exchange rate". It has two inactive relationships with the sales table via "dates" and "from currency id" and I always want the conversion to USD (currency id = 1).
I was using the formula below to get the rate and it worked well except that the currency exchange table does not have one day listed (system issues). For example 9/1/2020 does not exist on that table and when I use the formula below the result for that day comes out as blank. I wanted to include a condition on my formula below which will use the currency exchange rate from the day before in that case. Can anyone please help?