Instructions for Foreign Currency

     Foreign currency is supported in versions of QuickBooks that support it. The stored procedure reports in QODBC "sp_report" shows the foreign currency type in the "Account" column. For example, in this report :

     sp_report CustomerBalanceDetail show Text, Blank, TxnType, Date, RefNumber, Account , Amount, RunningBalance parameters DateMacro = 'All'

     The "Account" column will show, for example, "Accounts Receivable" except for a customer that uses a foreign currency, in which case it will say for foreign currency account, for example, "Yen Account" (or whatever you have defined them in the account type in QuickBooks).

An Example of Seeing Exchange Rate in Foreign Currency Transaction

Instruction

     This is not as simple as it sounds. For example, I can pay the Foreign Amount into my USD$ Bank Account or my Canadian $ Bank Account, and the Exchange Rate would not apply to the USD$ Bank Account but would apply to the Canadian $ Bank Account (boxed in red below)!

     Foreign currency comes into play when a customer pays you a Foreign Amount, or you pay a Foreign Amount. For example (here take accounts receivable and accounts payable as an example):

ACCOUNTS RECEIVABLE

    SELECT TxnID, ARAccountRefFullName, DepositToAccountRefFullName,
    AppliedToTxnRefNumber, AppliedToTxnAmount, ExchangeRate
    FROM ReceivePaymentLine
    where CustomerRefFullName='FLEXquarters.com LLC'

ACCOUNTS PAYABLE

     SELECT BillLinkedTxn.TxnID, BillLinkedTxn.APAccountRefFullName,
     BillPaymentCheck.BankAccountRefFullName, BillLinkedTxn.RefNumber,
     BillPaymentCheck.Amount, BillLinkedTxn.ExchangeRate
     FROM BillLinkedTxn, BillPaymentCheck
     where BillLinkedTxn.LinkedTxnTxnID = BillPaymentCheck.TxnID
     and BillLinkedTxn.VendorRefFullName='Intuit'

Note: A number of QODBC tables contain an ExchangeRate column when used with non-USA editions of QuickBooks.

See an example of how to create an invoice using exchange rates below.

An example of Creating an Invoice Using Exchange Rates

Note: When using Exchange Rates, you always need to use the same table for all inserts. In other words, the final insert should be InvoiceLine without the FQSaveToCache or with FQSaveToCache set to false. You cannot use both InvoiceLine and Invoice.

Query in QODBC

     For example, this insert created an Invoice using the exchange rate of 1.4222 specified:

     INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc,
     InvoiceLineQuantity, InvoiceLineRate, InvoiceLineClassRefListID, InvoiceLineAmount,
     InvoiceLineTaxCodeRefListID, ExchangeRate, FQSaveToCache)
     VALUES ('2E0000-1197674120','2B0000-1197674351', 'QODBC v7 Driver for QuickBooks 2007',
     1, 199, '30000-1045538607', 199, '70000-1045536338', 1.4222, 0)

Result in QuickBooks