Knowledgebase
[QODBC-Desktop] Foreign Currency and Exchange Rates
Posted by brad waddell on 12 March 2009 05:21 PM

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 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 it would apply to the Canadian $ Bank Account (boxed in red below)!

     Foreign Currency really 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 also 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

(164 vote(s))
Helpful
Not helpful

Comments (5)
Alex Cope-Norris
06 January 2013 10:52 AM
Can you give an example about foreign currency journal postings, and how to extract the currency information out afterwards (as in the columns "foreign amount" etc that you can select in Transaction Detail / Customise window)
Masar Hoxha
19 August 2014 09:37 PM
Hi, how do i update Exchange rate for all the trasnaction i have in Qb
for a whole year (all trasnaction for eyar 2009)
Jack
03 September 2014 10:40 AM
Hi Masar,

I would like to inform you that you can update ExchangeRate table wise. For Example If you want update ExchangeRate for Invoice, then you can update it from InvoiceLine table.

I kindly request you to please refer below sample query & please verify the records before running update query. First, you have to verify all records which you want to update by select query. Please refer below sample query. Please make changes in query according to your requirement.

If you want to update a small number of records, then please use below query:

SELECT * FROM InvoiceLine where where ExchangeRate =1.2

Update InvoiceLine set ExchangeRate =1.5 where ExchangeRate =1.2

If your table contains large number of records need to update then please use the date filter in your query. Every transaction you request must be translated and communicated to QuickBooks via large complicated XML transactions. QuickBooks needs to process those XML requests. More the number of Records, lengthier the XML request. QuickBooks may take time/memory to process all those requests.

QODBC send request to QuickBooks and QuickBooks process it in XML, So if records are more than QuickBooks may give "Out Of Memory" error.

SELECT * FROM InvoiceLine where TxnDate >= {d '2014-10-01'} AND TxnDate <= {d '2014-11-01'}

Update InvoiceLine set ExchangeRate =1.5 where ExchangeRate =1.2 and TxnDate >= {d '2014-10-01'} AND TxnDate <= {d '2014-11-01'}
Masar Hoxha
10 December 2014 06:26 PM
I have multiple line query, i need to update every day of the year starting 2009?!

does qODBC support these?!

Update InvoiceLine set ExchangeRate =20.161 where TxnDate >= {d '2009-01-01'} AND CurrencyRefFullName = 'Fine Gold'
Update InvoiceLine set ExchangeRate =20.161 where TxnDate >= {d '2009-01-02'} AND CurrencyRefFullName = 'Fine Gold'
Update InvoiceLine set ExchangeRate =20.161 where TxnDate >= {d '2009-01-03'} AND CurrencyRefFullName = 'Fine Gold'
Update InvoiceLine set ExchangeRate =20.161 where TxnDate >= {d '2009-01-04'} AND CurrencyRefFullName = 'Fine Gold'
Update InvoiceLine set ExchangeRate =20.142 where TxnDate >= {d '2009-01-05'} AND CurrencyRefFullName = 'Fine Gold'
Update InvoiceLine set ExchangeRate =20.333 where TxnDate >= {d '2009-01-06'} AND CurrencyRefFullName = 'Fine Gold'
Update InvoiceLine set ExchangeRate =19.991 where TxnDate >= {d '2009-01-07'} AND CurrencyRefFullName = 'Fine Gold'
Update InvoiceLine set ExchangeRate =20.038 where TxnDate >= {d '2009-01-08'} AND CurrencyRefFullName = 'Fine Gold'
Update InvoiceLine set ExchangeRate =20.141 where TxnDate >= {d '2009-01-09'} AND CurrencyRefFullName = 'Fine Gold'
Update InvoiceLine set ExchangeRate =20.141 where TxnDate >= {d '2009-01-10'} AND CurrencyRefFullName = 'Fine Gold'
Jack
11 December 2014 10:03 AM
Hi Masar,

If you want to set the different value day wise, then you need to run update query day wise otherwise you can update data in a single query.

For Example:
Update InvoiceLine set ExchangeRate =1.5 where ExchangeRate =1.2 and TxnDate >= {d '2009-10-01'} AND TxnDate <= {d '2014-11-01'} AND CurrencyRefFullName = 'Fine Gold'

Or

Update InvoiceLine set ExchangeRate =20.161 where TxnDate >= {d '2009-01-01'} AND TxnDate <= {d '2009-01-02'} AND CurrencyRefFullName = 'Fine Gold'

Update InvoiceLine set ExchangeRate =20.161 where TxnDate >= {d '2009-01-02'} AND TxnDate <= {d '2009-01-03'} AND CurrencyRefFullName = 'Fine Gold'
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).