8 Replies Latest reply on Mar 4, 2013 11:40 AM by philmodjunk

    Currency conversion calculation

    PeterMontague

      Title

      Currency conversion calculation

      Post

           I have a script which downloads the currency rate into a global field. I thought that global fields would be the same repeated through all records but I can see that I was wrong.

           I wanted to reference this global field in a calculation that would update prices for me in different currencies.

           What is the best way to approach such a calculation?

        • 1. Re: Currency conversion calculation
          JimMac

               Globals are a single named storage spot independent of a Record, so it is NOT repeated.  You set a Global by the Storage Tab.

               So...

               If you Currency Rate is just one value and not changing, then a Global is a good choice.

               If you have various Currencies, varing times, or your calculations using that Rate need to be fixed in time...

               Then...

               I would create a table of Currencies, the rate and when you captured that rate.  Link your calculations to that Rate Table by a relationship.

               You still can have many calculation using a single Currenct Rate, kinda like a global.

               Jim...

          • 2. Re: Currency conversion calculation
            philmodjunk

                 And look out for differences in how global fields behave if you have multiple users accessing the database via a network. Changes made to a global field will not persist after a user closes the file unless they opened the file on the host computer. And changes made to a global field by one user will not be visible to other users who have the database open at the same time. It's like each user has their own personal copy of any global fields.

                 This can be very useful, but if you want to pull a conversion rate off the web and have it be accessible to all users, you may need to put that value in a nonglobal field in it's own table and then link it using the X operator instead of = to each table occurrence where you need access to the exchange rate.

            • 3. Re: Currency conversion calculation
              PeterMontague

                   Wow. I never thought of using a separate table. So I could make a new record every day. Would date work as a key field? How would I relate it to my inventory which has no dates in it?

                   The x operator: is that a Cartesian join?

              • 4. Re: Currency conversion calculation
                philmodjunk

                     Why would you need more than a single record?

                     How exactly do you intend to use the currency conversion factor?

                     I was working from the assumption that this table would have a single record and thus you could use the cartesian join operator (x) so that it will match to all records in any table where you choose to link in this table.

                • 5. Re: Currency conversion calculation
                  PeterMontague

                       You're right. A single record would be fine. 

                  • 6. Re: Currency conversion calculation
                    JimMac

                         It is hard to say from what you describe so far, how many and what type Currency Records need.

                         Example...

                         Record for Yen to Dollars

                         Record for Pesos to Dollars.

                         Maybe.. you need to fix a Bid... then you would have to save the conversion with the bid.

                         Maybe.. you need a historical change... then you could chart the records of Yen....

                         All calculations would update unless you do a Lookup on the factor or other fixed methods.

                         Jim...

                    • 7. Re: Currency conversion calculation
                      PeterMontague

                           So I'm hoping Ill be able to link the sterling to euro field in the sterling to euro table to inventory::sterling to euro field Then inventory::Euro price will be a calculation: sterling price x sterling to euro::sterling to euro. 

                      • 8. Re: Currency conversion calculation
                        philmodjunk

                             Do you need this value at time of sale on an invoice or are you attempting to compute the total value of your current inventory in both lbs sterling and in euros?

                             The value of the needed conversion factor will change continuously for your total inventory value, but needs to be locked to the current value at the moment a sales transaction results in funds changing hands. The second option typically requires copying the needed conversion factor into a number fields so that future fluctuations do not change the value for this specific sales transaction.