Changing Behaviour of Exchange Rate Field

Has there been a recent change in the behaviour of the exchange rate formula? Or maybe I am just going crazy.

When setting up an exchange rate field, Ragic gives us the option of returning an exchange rate referencing the creation date of the record, or an exchange rate referencing a date field. If the date field has today’s date, Ragic returns an empty field.

Or at least this is how it worked before. I created a workaround with two exchange rate fields to solve this particular problem: one referencing the creation date, and one referencing a date field. If the date field had today’s date, then that exchange rate field would be empty and my calculations would use the other exchange rate field referencing the creation date. If the date field didn’t have today’s date, then that exchange rate field would not be empty, and I could use it instead.

But when checking again today, Ragic was no longer returning an empty field when the referenced date field had today’s date. It was returning an exchange rate, but not the same rate as the creation date referenced one. However, after midnight my local time, Ragic went back to the old behaviour of returning an empty field. I have to admit I am at a loss as to what is happening exactly. Does the behaviour change after a certain length of time into the day?

Also, Ragic is returning slightly different exchange rates on two different sheets, even though both are referencing the exact same creation date and time. How is that possible?

Can you clarify exactly how both of these exchange rates are calculated so that I can program a workaround to always return an accurate exchange rate of the day?

Also, I would like to know how to solve another related issue. On one of my sheets I have a date field that loads from a linked field. I have an exchange rate field referencing that loaded date field, but it won’t return an exchange rate when the date field is loaded. Does the exchange rate formula not work with loaded date fields? What is a workaround? Thanks

Is it possible that if I create and close a new record without saving, that Ragic is still saving a draft with the exchange rate referencing the original creation date? Then when I create a new record it auto loads the old exchange rate instead of a new one? There is some really strange behaviour happening.

I would appreciate some sort of reply and explanation here. After days of experimenting, only one thing has become clear: that I really do not understand how Ragic returns results when querying exchange rates. In order to program my workflow accordingly, I have a few questions:

  1. Why is Ragic returning the same exchange rate for the last two days (when referencing creation date), but returning two different exchange rates for the same two last days (when referencing a date field)?

  2. Why are two different sheets showing different exchange rates for the same day (when referencing creation date)… while on their respective sheets still showing the same exchange rates as the day before?

  3. Why does the exchange rate (referencing a date field) not match the exchange rate (referencing creation date) even when they are the same date?

  4. Why is the exchange rate field (referenced by date) empty for the first few hours of a new day, but sometime later that same day starts returning an exchange rate?

  5. Is Ragic auto-saving a draft with the exchange rates when cancelling a new record, and then incorrectly loading those same values into the exchange rate fields the next time a new record is created?

I know these questions are a bit confusing to fully understand. At the very least, please explain or point me to documentation on how the exchange rate field behaviour is supposed to work and I can test and see what is and isn’t working as designed and fix my workflow to compensate. Thanks.

Hi Pax,

Thank you for your message here. We will need some time to have a further investigation on this, will get back to you ASAP when we identify the root-cause of this.

Thanks again for your feedback.

Hi Pax,

We’ve run some tests based on the scenarios you provided in previous messages. We can not seem to recreate the issue that you’ve encountered. Therefore we kindly ask you to send Ragic Support an email with sheet URL that you’re having problems for us to take further investigation with our developers.
Thank you for your understanding.

I have changed the layout on my sheet to take screenshots of the relevant fields on 4 records so that you can see the issue more easily. All 4 records were made within 4 hours of each other on the same day. Only the last screenshot shows both exchange rates the same as each other. Why don’t all the others have the same exchange rate?

Now maybe this is working correctly, but you need to explain how each exchange rate is calculated, because as it stands I can’t figure it out:

  1. Does the exchange rate referencing the creation date change throughout the day? How often are the exchange rates updated? Daily? Hourly? Instantly?

  2. At what time during the day is the exchange rate referencing a date calculated? 12 hours after midnight? 24 hours? Is it based on my time zone?

  3. How is the exchange rate referencing a date calculated? Start of the day value? End of the day value? Daily average?

  4. There is an auto draft save feature when creating and deleting a new record that saves the information already input and automatically loads it later when a new record is created. Are you 100% sure that the exchange rate from the old record is not automatically copied into the new record, because it sure seems like it to me.

  5. When I reference a date field that is loaded from a linked field, the exchange rate function does not return an exchange rate. Is there any way to get this working?

Hi Pax,

Here are a few updates regarding your questions:

1-3. The exchange rate data is sourced from a third-party provider called Open Exchange Rates. By default, the Exchange Rate field (without a reference date) retrieves the current exchange rate from this third-party resource, which is updated hourly from various sources. Therefore, if your exchange field is set to the default configuration, entries created on the same date may have different exchange rates. However, browsers cache may affect this by retaining an updated exchange rate from the current accurate rate. On this matter, we will check with our developers to see if there’s a better solution.

Alternatively, if the exchange rate field is configured to reference a specific date field, then the exchange rate remains fixed based on the first exchange rate record fetched by our server for that date. Entries with the same referenced date should have the same exchange rate, although it may not align with the default exchange rate version.

Regarding the error seen in the second entry from the screenshots you provided, we would need more specific details of the record to investigate further. This is why we kindly asked for the URL of your record sheet and the steps taken to create the entry.

  1. Could you confirm if there’s a referenced date for the exchange rate field in your sheet? If so, it’s normal for the exchange rate to remain the same. If not, it may be affected by server or browser cache, especially if you keep one tab open for an extended period. As mentioned earlier, this is a matter that requires further discussion with our development team.

  2. This issue has been reported to development team. We will keep you updated once the it is fixed.

Thanks again for the feedback! We will get back to you as soon as we can.


Do you know the time of the day that this first record is fetched? I am guessing that it changes depending on your time zone. I am curious at what local time in my time zone the first value will appear… 8 hours after midnight for me maybe? (I’m in GMT+8). I know that for the first few hours after midnight at least, the value is empty.

You can see 2 exchange rates in each of my screenshots in my previous post above. The exchange rate on the left is referenced by the creation date which is shown directly below that rate. The exchange rate on the right is referenced by the date field which is shown directly below it.

It may not be the browser’s cache. Every time a new record is made, Ragic auto-saves the draft (even if auto-save is disabled). It shouldn’t auto-save the exchange rate because it reloads that old exchange rate from the draft even when there is a new creation date.

Screenshot 2024-04-01 at 19.51.42

Even better would be to have the development team ensure that the exchange rate referencing the creation date is always freshly synced to the creation date whenever a new record is created or cloned.

The first time the exchange rate is fetched by the server is when the first user attempts to retrieve the exchange rate for a specific date. This means there’s no fixed time for the server to perform this function.

Once again, this process may be influenced by the mechanics of cache settings. We are currently discussing this issue with our development team to determine the best approach to adjust the behavior and ensure that the exchange rate remains synchronized with the resources.

We will provide you with an update on this matter as soon as possible.