Two-directional currency converter

[kav]

Hi everyone,

I am building up a database which stores products with all corresponding prices in Euros and USD. The data is imported from an external Excel file, from two usd and eur price columns.

The case is, some providers offer their prices in euros, some of them in dollars. Thus, every time there is a filled USD field, the corresponding EUR field for the record is empty, and viceversa. I need to calculate the missing price with a fixed exchange rate.

/* Originally I wanted to make a global dropdown to show "all prices in euros"/"all prices in dollars" but came into much trouble on my way, so I decided to simplify the thing display both prices on the same layout. */

My fields

price_eur (auto-enter calculation)

price_usd (auto-enter calculation)

exchange_rate (auto-enter data)

price_eur has assigned the following EURempty script:

If [isEmpty (products::price_eur)]

Set Field [products::price_eur; products::price_usd * products::exchange_rate]

End If

price_eur has assigned the following USDempty script:

If [isEmpty (products::price_usd)]

Set Field [products::price_usd; products::price_eur / products::exchange_rate]

End If

exchange_rate is auto-enter data with value of 0,8

Problem:

a) The missing value is calculated only when I click its field. How can I do so that it's calculated automatically for all my records without the need to click anything?

: I want to be able to modify the exchange rate easily so that it automatically recalculates the values for all records, with this solution this does not happen, since I'd have to retype the exchange rate for all my 1500 records,

c) I need to truncate the converted values so that there are only two decimals,

d) seeing that there are so many things that go wrong, maybe I'm complicating it too much and there is some other, easier and more reliable way to design this?

I'd appreciate your solutions. I have the freedom to reorganise the excel file, too. I have some experience with access, php, c etc. but I feel I got stuck here.

Thanks in advance!

[Raybaudi]

Hi

it's simpler for me to post an example

Two-directional_currency.zip

[Peter (duksis3)]

Hi,

1)How did you trigger script?

2)Just PriceUSD*Rate isn't enough - Round is necessary, or omit digits starting from 3rd after"," - depend from how is accepted by accounting department.

3)Import USD and EUR into separate fields and show them in new calculation field.

Approx like this:

If (Is Empty (Trim(USD)); Round(EUR/Rate;2); If (Is Empty (Trim(EUR)); Round(USD*Rate;2);""))

And you can include value from field Show all in USD/EUR:

If(USD/EUR=USD;If (Is Empty (Trim(USD)); Round(EUR/Rate;2); If (Is Empty (Trim(EUR)); Round(USD*Rate;2);"")); If (Is Empty (Trim(USD)); Round(EUR/Rate;2); If (Is Empty (Trim(EUR)); Round(USD*Rate;2);""))))

This I wrote quickly (check brackets) and probably here is possible to reduce something. I have done similar years ago and "small details" are forgotten:)

[Lee Smith]

Hi kav, and Welcome to the Forum,

Here are a couple of other files that you might find helpful.

CurrencyConverter by comment

and

Euro_reference_rates by cjaeger

HTH

Lee

[kav]

Thanks guys, thanks to your help finally I got unstuck. Shame on me :

The Euro_reference_rates file unfortunately is not found, it's a pity since it would be a great solution for my database to retrieve the exchange rates from an external source.

[comment]

See if this helps:

http://fmforums.com/forum/showtopic.php?tid/183816/post/236763/#236763

See also:

http://edoshin.skeletonkey.com/2006/06/linked_fields.html

[kav]

Thanks for your help,

I'm investigating and advancing slowly (totally new to xml), XML importing works fine with the examples you've shown me, however, this way I import loads of data for many different currencies, and I'm only interested in the USD exchange rate, so I try to modify the ecbHist.xsl file, to get only the USD

...but I get 0 records imported. Any XML experts? :

Thanks everyone.

Edited July 14, 2010 by Guest

[comment]

Try:

[kav]

Thanks. Worked. :

After all day of fighting, trying to apply the ECB currency historial to my database, I got nowhere.

I tried many things, but none worked.

I stripped my project so that I can show what I am trying to figure out. I left the tables unrelated. Maybe someone creative would have a clue. Take a look:

CurrencyConverterProblem

[comment]

I think you may want something like this (attached).

XchRates.zip

[kav]

Thanks comment, your proposal is exactly what I need, I've copied your scheme into my design (I owe you a beer) and made some modifications due to imported xml date formats etc, tell me just one thing for now: what's the purpose of the "-" global number field in your file?

P.s one more, why when editing your file in layout mode, I can already see the values inserted, not the field names?

---

And the last general thing,

Would import 0 records. Why?

Edited July 15, 2010 by Guest

[comment]

The reason this doesn't work is that the > comparison is done numerically, so:



string A > string B



is actually evaluated as:



number ( string A ) > number ( string B )





If the string contains characters other than digits, white space and an optional minus sign, the result of number ( string ) is NaN (Not a Number).





Since the "dates" are in YYYY-MM-DD format, you can simply remove the dash separators to get a string that will convert to a valid number:

why when editing your file in layout mode, I can already see the values inserted, not the field names?

View > Show > Sample Data

what's the purpose of the "-" global number field in your file?

A visual separator, nothing more.

Edited July 15, 2010 by Guest
Forgot to address the last point

[kav]

I also figured out

[number(substring(@time,1,4))>2008]

...but your solution looks nicer to me.

I have no further questions. Thanks for your time :