1 Reply Latest reply on Sep 21, 2016 1:36 AM by Johan Hedman

    Calculation: exchange rate

    LeszekŁuczkanin

      I have 3 tables:

       

      EXCHANGE RATES

        Rate date

        EUR rate

        USD rate

        CAD rate

       

      OPERATIONS

         Original price

         Price in PLN when added to stock (calc)

       

      STOCK

         Original price

         Price in PLN when added to stock (calc)

         Price in PLN today (calc)

       

      In OPERATIONS I add all incomes and outcomes in the specified currency (USD, EUR, etc.).

       

      In EXCHANGES RATES I keep all rates from different days during the year. I have there also a value, that I set at the beginning of the year. So there will be at least 13 records for every year - one at the beginning (01/01/YYYY) and one at the end of each month.

       

      In STOCK I have all my items sorted.

       

      What I need is calculations as above.

         Price in PLN today (calc) - this takes original price and the latest (newest) exchange rate

         Price in PLN when added to stock (calc) - this takes exchange rate from day when added to stock.

       

      Do I need a lookup (?) or EXCHANGE RATE_ID_FK will be enough? Value list to pick the value will be quite long after few years...

        • 1. Re: Calculation: exchange rate
          Johan Hedman

          Create a field called Year_C (meaning calculation of year) that does Year(date-field)

           

          Then you create a relationship between same table occurrence and from Year_C to Year_C and you will have all 12 records for each year related. First record in that relationship will be start value of the year and if you do another relationship over same field but with sort function last first, you will get last dates value.