converting different currencies

[peterRed]

I have an existing report which is providing the number of hotel rooms multiplied by the duration and then by the nightly room rate which is sub totalled by company department then totalled. However the nightly currency rates are different due to different countries. I would like to convert the foreign currency to a common type

Currently the cells identify the Hotel_Type_Currency and formula for the @NightlyRate

Numbervar X;
stringvar TheField := {cv_accomodation_segment.HOTEL_NIGHTLY_RATE};
Stringvar TempString := "0";
for x := 1 to len(TheField) do(
if isnumeric(mid(TheField,X,1))
or mid(TheField,X,1) = "." then
TempString := TempString+mid(TheField,X,1)
);
If isnumeric(TempString) then
val(TempString)
else
0

However due to hotel guests staying in different countries the room rate changes due to currency i.e. Hong Kong Dollars HKD 1200 for one line and USD 144 for the next line. The result in the sub totals and totals being inaccurate.

whilePrintingrecords;
booleanVar dataflag :=false;// to identify bad data
NumberVar result:={cv_accomodation_segment.DURATION} * {cv_accomodation_segment.NUMBER_OF_UNITS};
NumberVar ResultTotal;
NumberVar DeptTotal;
NumberVar GrandTotal;
if not isnull({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) and
isnumeric({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) then result:= result
* ToNumber ({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})
else (result:=result)* (0);
dataFlag:=true;
ResultTotal := ResultTotal + Result;
DeptTotal := DeptTotal + Result;
GrandTotal := GrandTotal + Result;
result;

there are typically only approximately 10 different currencies. How can I convert the nightly rate to a common currency?

Thank you for your help

Peter

 

[synapsevampire]

I would attempt to approach this on the databse side.

Perhaps creating a Currency table which has the 10 currencies of interest, and then the equivalence in a standard type, such as Euros or US dollars. Now join your current table to this new table and you'll always have the standard amount, ot the equivalence in any other currency amount.

Since currency rates change constantly, I would find a method to automatically download them and populate your table daily, if you want to be exact.

Rather than using descriptives to define a technical question, try posting a mini spec:

Crystal version
Database used
Example data
Expected output

-k

 

[peterRed]

I am using CR 9 and unfortunately I am unable to gain any access to the input side of the database. My only opportunity is to convert the supplied information.
the selected data is displayed in CR as follows
rooms duration total currency rate value
1 2 2 aud 150 300
1 1 1 usd 200 200
totals 3 500

thanks for your help
peter

 

[Madawc]

Since you can't update the data, you should create a formula field that checks the type of currency and then converts it accordingly.

If you need to do this in more than one place, I'd recoment making each conversion-rate into a formula field, so that you only need to convert it once.

Formula fields and running totals are a lot more convenient than numeric variables for most purposes.

Madawc Williams
East Anglia, Great Britain

 

[peterRed]

thanks for your reply.
creating the formula is the help i am looking for. are you able to help?
thanks
peter

 

[Madawc]

Something like

if isnull ({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})
then 0
else
if {country} = "USA"
then
({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) * @dollars
else 
if {country} in ["France", "Germany", "Spain"]
then
({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) * @euros

You haven't said how you recognise countries.

Madawc Williams
East Anglia, Great Britain

 

[synapsevampire]

Since currencies change daily, and Crystal is NOT a database itself, this is fairly complex.

CR 9 allows for creating and passing real SQL directly to the database, or you might be able to use it to fire a stored procedure which does some updating, but this would require some database programming.

Another common approach is to set up an Access database and link (not import) your current database tables to it, and then create a currency table within Access, and then you can create Access Queries to supply the required data.

The requirements are a bit unclear to me, perhaps you can define the process in simple terms:

-Update nightly currencies in an external table from <some unknown database>
-Base the report on a common currency, or allow for parameters to state the currency of interest to display.
-produce report

Converting currencies is fairly simple, and you can also write a UFL to assist with this, but I won't make further suggestions as I don't understand the requirements very well as you haven't supplied the requested technical information.

-k

 

[peterRed]

Thanks for your help thus far.
There is a field on the report which identifies Hotel_Rate_Currency i.e. USD, EUR etc. then the amount.
The report is typically run each month therefore the exchange rate at the time of the month is relevant. I understand the report will never be 100% correct with exchange rates varying through out the month. I am trying to improve on the information that is currently being provided as a sub total and total which has less value.
I think a formula with exchange rates which may be adjusted if required each month would be the simplest way.
thanks again