Formula for exchange rate

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a line items for my purchase order and I want each item to have another total amount automatically converted to BND (Brunei Dollars), based on the original total amount of whatever the currency is.
I would like to set the conversation automatically base on the latest exchange rate, would it be possible to do so?

Please see below example for easier understanding:

PO Date: 15/04/2016
Item: Bracket (Manual Input: Text)
Qty: 12 pcs (Manual Input: Numeric)
Unit Price: 1 (Manual Input: Numeric)
Currency: USD (Manual Input: Multiple Choices)
Total Amount: 12 USD (Summary: [Qty x Unit Price])
Total Amount in Brunei Dollars (Base on 1.36 today's exchange rate for USD-BND): 16.32 BND (Summary: [ExRate=USDtoBND of the date in the PO]*[Total Amount])

I hope that my explanations are clear and will wait for your prompt response.

Photo of Bernard


  • 116 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
No problem.

Make a table of Exchange Rates, with fields for the exchange rate code and rate.
Make a relationship to the PO Lines table where 1 Rate has Many PO lines.
Lookup the rate into the PO lines Table. Call that lookup field Current Exchange Rate.

Create a new numeric field called Exchange Rate and at the bottom of the field properties set it to be a snapshot of the Current Exchage Rate. Use the Exchange Rate field in your formula.

Here is the help text on snapshots b

From time to time sat weekly or monthly you will need to update you exchange rates. But with this setup you will not affect old PO line calculations as the rate changes.