1 2 Previous Next 22 Replies Latest reply on Mar 15, 2013 12:55 PM by philmodjunk

    Currency question

    henryhu91

      Title

      Currency question

      Post

            

           Hello FM users, I've a question over currency. 

           I've Currency field from related table to store the currency rate.

           Users will manually change for updated currency rate.

           I’m going to have two fields for US price and China price

           I believe creating calculation field on US price and China price fields will work to multiply and divide with currency rate field from related table. 

           If users choose to input in US price field for an item, it will automatically convert price for China Price field.

           If Chinese currency (Yuan) goes up, it will be updating in China Price.

           If users choose to input in China Price, it will automatically convert the price for US Price. 

           Even though Chinese currency (Yuan) goes up, it will not change because we purchase the item when currency is not up.

           I will be really appreciate any of your advice or help. 

        • 1. Re: Currency question
          philmodjunk

               How much of that can you already get to work?

               You may want to compare the two "look up" methods in this thread: Auto Fill

               One of thes methods updates automatically when data in the "look up" table is modified the other does not update automatically.

          • 2. Re: Currency question
            henryhu91

                 Hi Phil, I've looked into the article about "Look up". I don't see anything that fit my need.

                 

            How much of that can you already get to work?

            I created two tables. Product and Preferences.

            Product table will display US dollar price and China Yuan Price.

                 In Preferences table, I will have currency rate number field which accounting staff will update it manually.

                 I need to find out If users input in US price field for an item, it will automatically convert price for China Price field.

                 If Chinese currency (Yuan) goes up, it will be updating in China Price.

                 If users input in China Price, it will automatically convert the price for US Price. 

                 Even though Chinese currency (Yuan) goes up, it will not change because we purchase the item when currency is not up.

            • 3. Re: Currency question
              philmodjunk

                   I'm trying to understand what you mean by "it" in the last sentence:

                   

                        Even though Chinese currency (Yuan) goes up, it will not change because we purchase the item when currency is not up.

                   Is this what you mean:

                   Changes to Yuan must automatically update US dollar prices but changes to Dollar prices do not update Yuan?

                   Am I correct that both dollar or yuan prices may be entered and the corresponding currency field must then compute the equivalent in the other currency using the current rate in the preferences table?

              • 4. Re: Currency question
                henryhu91

                     Here is an overview of the situation.

                     We have staff in China and US. 

                     China staff will most likely input Yuan price and US staff will input dollar price.

                     We only need one input for each item either it's dollar price or yuan price. 

                     when users input dollar price, yuan price field will automatically compute by currency rate(6.2) in related table (preference table) that accouting staff set it. 

                     when users input yuan price, dollar price field will automatically compute by currency rate(6.2) in related table (preference table). let just say next day, accouting staff set currency rate into higher number than 6.2, yuan price field that users input before will stay the same and not change.

                       

                • 5. Re: Currency question
                  philmodjunk
                       

                            yuan price field that users input before will stay the same and not change.

                       But will the dollar values change when the rate changes?

                       So far, this looks like the "looked up value" method described in that link I shared can be set up to serve this purpose. It's mainly a matter of setting up the correct relationship between the two tables so that a "rate" field in product will look up the current rate from Preferences.

                       This relationship will match all records in Products to any record in Preferences:

                       Products::anyfield X Preferences::anyfield

                       Using the cartesian join operator (x), the specific fields selected as match fields do not matter.

                       The Rate field in Products would simply have a looked up value setting to copy a value from the Rate field in preferences.

                       Then you can define the Dollar field to have this auto-enter calculation:

                       Yuan * Rate

                       The Yuan field would have this auto-enter calculation:

                       Dollar / Rate

                       And in both you'd leave the "do not replace existing value..." check box selected

                       Note that this assumes a Rate value that is in Dollars Per Yuan. If it is Yuan per Dollars, you'd reverse the multiplication and division operators.

                  • 6. Re: Currency question
                    henryhu91

                          

                         

                    But will the dollar values change when the rate changes?

                    it will change the dollar values.

                    So basically, I will need 4 fields, dollar input, yuan input, dollar auto calculation, yuan auto calculation?

                    look up function and mutiply or divide with relationship occurance by the field would be the same?

                          
                    • 7. Re: Currency question
                      philmodjunk

                           You will need three fields: Rate, Dollar input, Dollar calculation, Yuan

                           Since Yuan doesnt change, it can use an auto-entered calcualtion and one field can be used for either data entry or to show a converted value from the dollar input field.

                           In order to get a converted dollar value that updates automatically, you'll need a calculation field such as:

                           IF ( Dollar_Input ; Dollar_input ; Yuan * Preferences::Rate )

                           This will be an unstored calculation field and if the value is specified in Yuan, the value will "float" as the value of Rate in Preferences updates.

                      • 8. Re: Currency question
                        henryhu91

                             what if Users from China need to input in Yuan value?

                        • 9. Re: Currency question
                          philmodjunk

                               They type it directly into Yuan. That's why Yuan should be set up with an auto-entered calculation field, but with "do not replace existing value..." selected.

                          • 10. Re: Currency question
                            henryhu91

                                 Hi Phil. 
                                 I've created test database with calculation you instructed me and calculation fields are not calculating.

                                 can you take a look it at for me please?

                                 https://www.dropbox.com/s/yiyydaxjf469o91/test%20currency%20converter.fmp12?m

                                 thanks

                            • 11. Re: Currency question
                              philmodjunk

                                   C_Dollar should be defined as a field of type Calculation--not an auto-entered calculation.

                                   It's expression should be:

                                   If ( Dollar Input ; Dollar Input ; Yuan * preferences::Currency rate )

                                   Yuan should be defined as a number field with an auto-entered calculation:

                                   Dollar Input / preferences::Currency rate

                              • 12. Re: Currency question
                                henryhu91

                                     when I input Dollar Input, Yuan value isn't auto create by Dollar input value. just stay on 0. 

                                • 13. Re: Currency question
                                  philmodjunk

                                       good catch.

                                       Clear the "do not replace existing values..." check box on the Yuan field.

                                  • 14. Re: Currency question
                                    henryhu91

                                         Thank you so much.

                                         I got what I need.

                                    1 2 Previous Next