Exchange Rate solution needed - Month specific

[buckbuck]

Hello

I am trying to fashion an exchange rate solution to produce an exchange rate given a number of variables.

 

An accounting record has an amount to convert to GBP £. The amount could be USD CAD or EUR. The exchange rates are monthly.

 

I have created an EXCHANGE RATES table with the fields; RtYr, RtMn (rate month), CAD>GBP, USD>GBP, EUR>GBP

I have an ACCOUNTS table with the fields Date, Currency and Amount.

I want the field cGBP to calculate the required Exchange rate based on the Currency field and the Year/Month in the Date.

So for example; when the ACCOUNTS Date is June 2012 and the ACCOUNTS Currency is USD ACCOUNTS cGBP is the EXCHANGE RATE USD>GBP rate for RtYr 2012 and RtMn June

Can anyone help?

Thanks

Matthew

[eos]

I have created an EXCHANGE RATES table with the fields; RtYr, RtMn (rate month), CAD>GBP, USD>GBP, EUR>GBP

 

If you have three currency fields, you need to create (and check on) three relationships; better use only one field for the target currency and one relationship (and more records instead).

 

See here:

ExchangeRateMonthly_eos.fp7.zip

[buckbuck]

Hello Tegernsee Bavaria

I appreciate a lot your time helping me with a solution. That said, could you explain the creativity of your design? Why did you create a separate table for Currency? I do not understand your use of the term Rates_cartesian - you are specifying a point on a graph? Also, could you have used a Get Year (date) and Get Month (date) to specify the rate? Why use ≤ or ≥. As mentioned I appreciate the solution I just want to understand your logic better - I am a novice.

Thanks

Matthew

[eos]

Matthew,

 

Hello Tegernsee Bavaria

 

it's eos, actually; “Tegernsee, Bavaria” is my location … 

 

Why did you create a separate table for Currency?

 

In your solution, Currencies are a part of your business logic and entities in their own right, so you should treat them like that. Imagine you'd simply define a value list. Next thing you want to define and use the long form of the name and/or a symbol for a currency, or other associated data –  where would you put that?

 

I do not understand your use of the term Rates_cartesian - you are specifying a point on a graph?

 

A cartesian relationship is one that does not filter, e.g. shows all records from the other table. Read all about it: http://fmhelp.filemaker.com/fmphelp_12/en/html/relational.11.5.html#1027758

 

Used here merely as an interface tool to define the monthly rates. You would probably rather use a list, or a cartesian relationship and a portal from some TO of Rates to Rates_cartesian, but for this demo I wanted to keep it all to one layout.

 

Also, could you have used a Get Year (date) and Get Month (date) to specify the rate? Why use ≤ or ≥.

 

There are several different methods to match an arbitrary date from a month to a lookup record for that month. Normally the table which does the lookup has a lot more records than the lookup table (in your case there are n * 12 lookup records per year, i.e. 36 with three currencies, but probably many more account records). So it makes more sense to create the additional data fields in the lookup table instead of the “transaction” table. You could make a copy of the file and try to use other fields and relationships for the lookup; a nice exercise …

[buckbuck]

eos

Yes of course eos. I was just sending a shout out to you in your town from Vancouver, my town. Thanks for the insight.

Best regards

[buckbuck]

When I set this up on my database it does not work. When I remove the relationship ACCOUNT_fk_CurrencyID and Rates_fk_CurrencyID the relationship shows the Year and Month for the rate but it is not dynamic - I cannot change the Currency. I cannot understand where my mistake is copying your solution. Any suggestions?

I find the relationship CURRENCY_Currency and ACCOUNT_Amount odd. Can you explain its role or effect in the solution?

Thanks

[eos]

When I remove the relationship ACCOUNT_fk_CurrencyID and Rates_fk_CurrencyID the relationship shows the Year and Month for the rate but it is not dynamic - I cannot change the Currency. 

 

Of course, removing the currency predicate from the relationship negates the point of this whole exercise. 

 

I find the relationship CURRENCY_Currency and ACCOUNT_Amount odd. Can you explain its role or effect in the solution?

 

Those TOs should be matched by currency_pk to currency_fk; it didn't matter/notice in the barebones UI of this demo because the account currency name in the popup is resolved via the value list, not a relationship. (Note that the currency names in the lookup table are also displayed without a Currency table being (properly …) connected.) 

 

I cannot understand where my mistake is copying your solution. Any suggestions?

 

Not without seeing the file (hint …).

[buckbuck]

Hi eos

Here is the file. I expect you will wonder about my approach - it comes from a lack of understanding.

Thanks

2012 LedgerUK FxTest.fp7.zip

[eos]

Oh, you mean the GJ table with 463 fields …? I've seen worse, but if you know that it's not a good structure, that's a start. And your solution would definitively be easier to understand if would forego all those cryptic field and table names.

 

So, where in those tables are your accounts? The only thing that's looking into the exchange rates table is GJ, and since it only has one record, I assumed that's a global dashboard / configure&create table – but don't see any Add/Create button. Is GJ your actual data table, and you just didn't want to hand out real data?

[buckbuck]

Yes. Sorry but I could not send all my data since it is somewhat confidential (I do not want to share it with the FMF community), and besides given my amateurism stupidly embarassing. I am doing my best.

 

Being a novice I was critiqued for my nomenclature before - too long, so I have tried to abbreviate things a bit. I have no idea what the protocols are other than capitalizing table names.

 

Yes GJ (General Journal) is the data form for all my Journal entries. My objective is to have a shorthand method for exchanging my USD and CAD amounts into GBP.

 

Thanks again

[eos]

Your relationship was per se OK, but you invented some fancy primary key numbering scheme and didn't use the same data type in the primary and the secondary key field (so one was cur 001, the other one only 001 …)

 

Anyway, I recommend that for starters, you use as a primary key a simple serial increment with data type number. If you want to record any additional attributes for currencies (like your internal sorting, a symbol, the name of the Treasury Secretary whose signature is on the notes, whatever), define a new field in the Currency table and put those data in there.

 

As for your names: have a look here http://filemakerstandards.org/display/cs/Overview; also, any decent introductory database or programming textbook will have a chapter devoted to naming schemes and conventions. Usage of said will also help others who are working on your projects with you or after you, or who you ask for assistance … 

 

Here's a working version incl. annotations on the layout and in the Relationship Graph. And you should really think about what to do with those 463 fields … (before you start renaming them, that is!)

 

 

2012 LedgerUK FxTest_eos.fp7.zip

[buckbuck]

eos

Thanks again for your excellent help.

Matthew

[buckbuck]

eos

The 'Converted' field amount does not change when the Currency field value list changes. Am I missing something

[eos]

The 'Converted' field amount does not change when the Currency field value list changes. Am I missing something

 

Yes. The conversion rate to use in your calculation must not come via the Cartesian relationship, but via CurrAndMonth – that's the one (1) lookup record the current GJ record matches. 

[buckbuck]

eos
I replicated everything as you designed it and I think I understand your logic but my effort does not work. Can you explain, "the account currency name in the popup is resolved via the value list, not a relationship." Maybe this is my problem. I have a value list based on the currency table as you suggest.
Matthew

[eos]

What exactly isn't working?

[comment]

Perhaps you should start out with something simpler:

ExchRates.fp7.zip


Actually, I don't think it needs to be any more complicated in the end, either. You don't need a Currencies table, because you are not a central bank and don't have anything to say about a currency as such. And you don't need an x relationship, because ... well, because it's not needed for anything useful.

The only thing I would consider changing is looking up the exchange rate into a local field in Transactions, so that the GBP amount calculation can be stored. Otherwise it could become slow when viewing a large list of transactions.