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