I'm developing an interface between QuickBooks 2006 and SunSystems 5.2.1. The purpose is to transfer transactions from QuickBooks to SunSystems (only for P&L expenses accounts). The source tables from which I will pick up transactions to be transferred are:

- JournalEntryLine

- CheckExpenseLine

- BillExpenseLine

- DepositLine

All amounts have to be transferred in USD (which is the home currency of the company). For the first three tables I've got no problem to calculate the USD amount as I'm using the ExchangeRate field which is available in these three tables. But for the DepositLine table there isn't any ExchangeRate field... I know I could join the Account table and then the Currency table to find the ExchangeRate of the Currency code linked to the account... but when QuickBooks is configured with multi-currency, in the Make Deposit screen there is a field on the bottom which allow user to overwrite the default rate...

Where is that exchange rate value stored ??? How can I retrieve that value ???

Thanks a lot.

UPDATE Currency SET ExchangeRate=1.62575 where Name='United States of America Dollar'

See: How can I see the Exchange Rate used in Foreign Currency Transactions? for more.

Tom,

I may be completely missunderstanding you but what JCR seems to want (and I need to do as well) is to read the values that have been assigned to each existing deposit.

I'm not trying to create new deposits with a given exchange rate, I'm trying to read deposits and find the value of each one in my native currency.

- Jon

The only way to see what happen to the Foreign Exchange Deposits is to follow the TxnNumber. For example, for this Deposit:

I can look at the DepositLine table and find that the TxnNumber for this Deposit was 474:

SELECT TxnNumber, DepositToAccountRefFullName, Memo, DepositTotal,
DepositLineEntityRefFullName, DepositLineMemo FROM DepositLine where TxnNumber=474

I can then use this TxnNumber to find what exchange rate was used by reading the USD$100 deposit from above and comparing it the AUD$120.00 now in my Australian Bank Account by using the Deposit Detail report:

SELECT TxnNumber, DepositToAccountRefFullName, Memo, DepositTotal,
DepositLineEntityRefFullName, DepositLineMemo FROM DepositLine where TxnNumber=474