Getting the correct value [Scripting]

[#1]

Hello all,

I would like to describe my tables below:

HEADER TABLE:

id	currency code	date
1	USD	2/23/2013
2	AU	2/20/2013

CURRENCY TABLE: (everyday it updates)

currency code	rate	starting date
USD	50	2/23/2013
AU	40	2/23/2013
USD	55	2/24/2013

LINES TABLE:

id	amount
1	1000
2	270

Now my problem, the chart that I create is the sum of the amount in the LINES TABLE.

I need to output the computed amount not the raw amount.

Any ideas?

Regards,

Donna

[#2]

Very helpful answer you gave me Rob! I tried using the first one, works like a charm, but my problem now is connecting it to the HEADER table.

since I need to consider both the CURRENCY CODE and the POSTING DATE [targeted to Starting Date] If you could give me some points. Thank you!

[rwunderlich]

Can you post some sample data?

-Rob

[#4]

Hi please see attached file. I have already filled up all the dates for the rates in the rate table. Now I need to connect it to the HEADERS table. But if I link it using the currency code, I won't be able to get the appropriate date connected to it.

Hope you get it. Thanks! PSA.

[rwunderlich]

Can you rename the "Posting Date" field to "Date"? That will create a link on Date & Currency Code whilch will link the Header rows to the correct Rate.

-Rob

[#6]

Alright. I will just concatenate the 2 key fields.

[rwunderlich]

Please mark someting as an answer if it works out for you.

-Rob