Currency and Relationships

[Dutchy]

Hi, Need some help with this. Can't seem to find the solution

I have two tables:

Projects

Currency

In the Project table I have the following fields:

Project number (serial number)

Purchase Goods 1 in Currency (number)

Purchase Goods 2 in currency (number)

Purchase Goods 3 in Currency (number)

Currency ID 1- Purchase (text)

Currency ID 2- Purchase (text)

Currency ID 3- Purchase (text)

Currency 1 - Purchase (number)

Currency 2- Purchase (number)

Currency 3- Purchase (number)

Purchase amount in Euro 1(number)

Purchase amount in Euro 2(number)

Purchase amount in Euro 3(number)

Sales amount in Currency (number)

Fields in Currency table:

Currency ID (USD, EUR, etc)

Description (text)

Currency (rates)

In the Currency table I would like to enter the currency rates for the different currency's we work with. In the projects table I would like to be able to fill in the Purchase amount in currency and then be able to type in the currencyID (like USD or EUR). FM should then automatically lookup or fill in the currency (rate) that goes with that Currency ID and then calculated the amount in Euro's.

I can get FM to look up the right currency rate for the fist purchase good I fill in (with a portal) and then calculated the right amount's to Euro's but if I want to fill in a second purchase good it fills in the same currency rate as the first one. And I need it to be able to fill in a different one each time. Can anybody Help?: Probably am doing something wrong with relations, but am not sure what.

Hope my explanation of problem is clear.

Thanks in Advance for any help in the right direction.

Dutchy

[comment]

See if these help:

http://www.fmforums.com/forum/showpost.php?post/233672/

http://www.fmforums.com/forum/showpost.php?post/191094/

[Dutchy]

Hi, these posts where helpful and got me thinking about other possibilities. However I still can't really get it working. Do you have any other suggestions?

Dutchy

[Dutchy]

Hi, Just made a little Filemaker example file which should show what I am trying to do. I can get the first line to work through a portal but that doesn't work anymore for the others. I am totally blank at the moment on figuring this one out. Need some suggestions in the right direction. Thanks.

Dutchy

Currency.fp7.zip

[Fenton]

Whenever you have "field1, field2, field3," etc., it is likely that you have inadequate relational design, especially if you're running into problems with relationships, lookups, etc..

Currency_fej.fp7.zip

[Dutchy]

Great thanks. This is exactly what I am looking for. Can't wait to get started, and get it working. Thanks a million.

Dutchy

[Fenton]

Be aware however that your Project total is now a Sum (lines::amount), in other words an unstored calculation using a relationship. This does not impact when dealing with one project. But it will matter eventually, with hundreds of thousands of projects, when trying to add them all up, etc., as it will be (much) slower than if it was stored. Then you begin to consider "transactional" methods of storing that number. But that's not something you need to worry about now. Of course, by then computers will be faster also :-]

[Dutchy]

Hi Fenton,

I have been working on changing my relation design and things are really shaping up. However still got some problems working it out. Maybe you can shine some light on this?

I have changed the test database a bit to show you what is on my mind. First of all I would like to add a projectline for freight amount. Like I did in the test database. However when I only have one piece of good to purchase I can't skip straight to freight unless the rest of the goods are 0. Is there a better way to do this. I am sure there is. Maybe make a separate projectline for freight? Or auto fill in 0 for all projectlines? (how do I do that??)

Second problem occurs at the Sales side. How do I get the currency ID and currency to correspond to the first projectline of this project number? i have now just made Fm to fill in EUR and 1.00 because that is the most likely to be used. But I would like it to be able to change. Like the way it is done for the purchasing side (project lines) But then I get in conflict with my relationships. Have no clue how to get my relation design in order for what I want to do. Can you help.

Thanks in Advanced,

Dutchy

Currency_du.fp7.zip

[Fenton]

Since you say the sales items can be different currencies, then you cannot assign some overall arbritrary "currency" to the sales items for the whole project later. You don't need to, you've already computed it for Euros. That's what you're totaling for the project sales.

On the other hand, there is only one Freight charge. Hence it belongs to the project; it is not a sales line item. But it also needs a currency conversion, like the line items.

So, taking both the above into account, I stole the existing currency conversion fields from the "sales total" and used them for Freight. You need the whole set of currency conversion fields for any single item in Project, such as Freight. Hopefully the rest are entered in Euros.

The "currency rate" is either "the currency rate at time of entry" or "the currency rate at the current time." For invoices it is almost certainly the former. But you need to know which you want to use. If the former, then the rate is looked at time of entry, and then not changed. Otherwise you're going to mess up existing entries. You do not multiply by the Currency Rate in the Currency table, as it will often change (unless that's what you want, which I doubt, but do not know). I didn't do much to deal with this, but likely validation on a Lock field or something is needed.

Currency_fej.fp7.zip

[Dutchy]

Thanks Fenton, for all your help. This is working like a charm.

About the currency rate: how do I make sure fm uses the former currency rate. (The currency rate at time of entry) and that it doesn't change after a currency change later in time? By filling in "do not replace existing value in field if any"??

Thanks again, I could not have done this without you.

Dutchy

[Fenton]

There is a dilemma in this kind of data entry. The trouble is, people make mistakes. If they choose a currency, then realize it was the wrong one, they need to be able to change it. And when they do, they expect the numbers to change. So you can't check [x] Do not replace existing.

What you can do is have conditions for when they can change things. The simplest is a Lock field, a number field with either 1 or nothing. Then validate the CurrencyID field against it (as it is the trigger for the other fields).

At some point you're going to want to have different Accounts & Privileges, so you can say who can lock/unlock a record, and under what conditions (not shipped, etc.).

Currency_wLock.fp7.zip