1 2 Previous Next 16 Replies Latest reply on Nov 22, 2016 10:33 PM by sam0723

    Time dependent of currency

    sam0723

      Hello everyone, i am new to filemaker. i am working on the currency of the invoice database.

       

      My client would like to have a currency depends on the time. For example, they will use that currency rate for calculation for one week. For next week, they would like to use another currency rate.

       

      My question is : is it possible in filemaker, setting the time dependent field or they need to input the rate manually everytime?

        • 1. Re: Time dependent of currency
          Mike_Mitchell

          The real question is, where do you get the "currency rate"? Are you talking about an exchange rate? Or is this something that is actually cyclical - you can predict ahead of time what it will be?

          • 2. Re: Time dependent of currency
            David Moyer

            Hi,

            one part of your solution might include a "public table" - this is a table with a single record and non-global fields for "public" data ... like the current rate of the week.  This table is connected to your other table(s) via a cross-join relationship.

            All this does for you is allow you to populate a single field (e.g. WeeklyCurrencyRate) which can be accessed by all users, all week.  How you go about populating this field is still a question.

            • 3. Re: Time dependent of currency
              sam0723

              yes. The exchange rate. The user will get the data from the web once a week.

              • 4. Re: Time dependent of currency
                sam0723

                Thank you, i think it is close to the solution to my question. how could i set up the field, get the information from the net. but the exchange rate will change everyday

                • 5. Re: Time dependent of currency
                  keywords

                  Unless you can come up with some way to automate grabbing data from a web source, then I think you have already provided your own solution: "The user will get the data from the web once a week" (unless, of course you mean every day, as your latest post implies). In other words, there is no simple way around user input.

                  As for making the data available to use in your invoices, rather than a global field, I would suggest you create an exchange rate table with each record storing both the rate and the date on which it applies. That way you maintain a history of the rates used, in case you ever need to roll back for some reason, and it would not be hard to ensure that the current rate was always used in any dependent calculation.

                  • 6. Re: Time dependent of currency
                    philmodjunk

                    I suspect that insert from URL could be used to get the exchange rate.

                    • 7. Re: Time dependent of currency
                      sam0723

                      Thank you. so i need to create a table for exchange rate. If i need to use the value in calculation. i should have a calculation using the most updated value? Is that right?

                       

                      for the exchange rate, i should insert from URL from the web and create a button to allow user take the data every week.

                       

                      Do i misunderstand your advice?

                      • 8. Re: Time dependent of currency
                        keywords

                        That sounds right. You might have to play around a bit to get the auto insertion working, but it is certainly possible. Alternatively a user could just look it up and enter the relevant rate manually.

                        • 9. Re: Time dependent of currency
                          AndyE

                          Google has a nice little currency converter tool:

                           

                          Currency Converter - Google Finance

                           

                          Being a basic, unobtrusive web page it can be implemented into a FileMaker solution with a Web Viewer quite well. Attached is a demo of possible uses.

                           

                          Demo 1 allows for a fully interactive use of the Google Currency Converter. Handy if you need to pick and choose different currencies.

                           

                          Demo 2 shows how it could be implemented if you prefer it hidden with less steps to get the current exchange rate. To use this method you would need to know which two currencies you are using and pre-set that in the URL of the Web Viewer. For this demo I've set a conversion of 1 Australian Dollar (AUD) to US Dollars (USD). Being pre-set currencies this could also potentially be added as a scheduled script to run weekly.

                           

                          The scripts extract the exchange rate from the content of the loaded Web Viewer by locating the returned value's position in the html and converting it to a number.

                           

                          As to the legality of using it in a commercial solution you'd need to read Google's terms and conditions I guess. Also relies on Google keeping it as an active tool. Occasionally they deprecate features like this so if that happened in future you'd need to find another method.

                          1 of 1 people found this helpful
                          • 10. Re: Time dependent of currency
                            keywords

                            That's excellent Andy. Will certainly take care of the web scraping aspect of what the OP is looking for.

                            • 11. Re: Time dependent of currency
                              sam0723

                              Thank you. i think it is close to the answer. One more question, Could i use other website to take the data other than google convertor?

                              • 12. Re: Time dependent of currency
                                David Moyer

                                for me, I look for the easy solution of a simple http address/query string URL in conjunction with the Insert from URL script step.  That may be what's used with the Google converter - I haven't looked.  You can probably make your alternative site work in a similar fashion.

                                • 13. Re: Time dependent of currency
                                  AndyE

                                  You're right yes. Certainly for the hidden version in the demo I uploaded the Insert From URL script step is probably a better method to achieve the same result.

                                   

                                  Attached is a modified demo file using this method and showing two examples of how it can be implemented for different sites (Google and xe.com). Pretty much any currency conversion site should work. Just have to find the returned result in the html.

                                  • 14. Re: Time dependent of currency
                                    David Moyer

                                    This can be useful parsing results:

                                    Another custom function for text parsing

                                    1 2 Previous Next