2 Replies Latest reply on Jun 28, 2012 3:46 PM by MikeBradshaw

    Filemaker 11 - Determining Lookup Field from a value in another Field



      Filemaker 11 - Determining Lookup Field from a value in another Field



      I am not sure whether I am overcomplicating this, but after a few evening trying to figure out (i) whether it feasible, and (ii) the correct syntax, I am at loss and thought I would ask you guys if you know how to do this :-)

      I wil try to explain this as clear as possible. I have an Invoice table which contains records where the values could be in currencies other than my local currency. This is not difficult, however I buy product in a different currency too. So, I am based in the UK (currency GBP), I buy products from the US (USD) and sell them in Europe (EUR). I also need to work out how much I paid for the item at the time I make the payment and how much I got paid, at the time I received the money.

      I need to calculate profit etc in my local currency for tax reasons.

      Here is how I thought I could approach this:

      I have a table which downloads currency rates from the Central European Bank and stores them in a table that has fields:

      Date, EUR, GBP,USD, SEK, DKK etc. 

      This way each record contains the exchange rate values for that particular day.

      In the Invoices Line Items table I have a field that is the currency of the Unit Cost Price and a Currency field for the Selling Price, each of which need converting to GBP at the relevant rate (Cost when I pay and Sell when I get paid).

      I use the Date in the Exchange Rate table to link to the Date Paid and Date I Pay the supplier.

      I need to use the Currency Field related to the Cost Price to determine which field in the Exchange Rate table to lookup the exchange rate value. (Hope your still with me).

      I have tried using the likes of GetFieldName and GetAsText to concatenate the Lookup SourceField, but I must be getting soemthing wrong.

      If I use the following calculation Lookup ( ExchangeRateTable::USD ) I get the desired USD rate, however if the Cost currency is EUR, I need to automatically relace USD with EUR. 

      The Syntax i was trying to use was along the lines of Lookup ("ExchangeRateTable::"&GetField("cost_currency")) where field cost_currency is the field that contains the currency abbreviation such as GBP, USD etc.

      I hope this provide enough of an insight for someone to tell me I'm either doing it completely ar5e about face or I'm on the right track but just using the wrong syntax.

      Thanks in advance



        • 1. Re: Filemaker 11 - Determining Lookup Field from a value in another Field

          How is the data structured in your exchange rate table?

          Do you have one record for each day or one record for each day for each type of currency?

          With one record for each type of currency for each day, you could use this relationship to lookup your exchange rate:

          LineItems::date = ExchangeRates::date AND
          LineItems::CurrType = ExchangeRates::CurrType

          If you have one record for each day with separate fields for each currency type, you could use this relationship:\

          LineItems::date = ExchangeRates::date (this appears to be the relationship you have defined now)

          Then you can set up an auto-enter calculation to enter the correct exchange rate that looks like this:

          Case ( CurrType = "USD" ; ExchangeRates::USD ;
                    CurrType = "EUR" ; ExchangeRates::EUR ;
                    CurrType = "GPB" ; ExchangeRates::GPB )

          • 2. Re: Filemaker 11 - Determining Lookup Field from a value in another Field

            I had a feeling I was overcomplicating it :-)

            At the moment I have a single table for the Exchange Rates - the European Central Bank provides the rates with the Euro as the base rate and I use GBP (although I am creating the database so that the user can define the base currency in a preferences table). Currently, I have a second set of fields, in the same table which holds the converted rates (e.g. cur2USD, cur2SEK etc, these are unstored calculations.

            I did wonder whether I should create a table that held the converted rates along the lines of your first suggestion - two relationships required: Date and Currency, so that each record held values for date, currency, rate.

            I am not sure whether there is any benefit to this for other use or reporting at some other stage in the future.

            Thanks again Phil !