Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
Multi-Currency Reporting - GL Transaction Detail by Account |
Author |
Message |
|
Posted : 2006-08-02 19:30:33 |
We are using multi-currency database in QuickBooks Enterprise 2004 - Canadian Edition. We are printing GL Transaction Detail by Account report in QuickBooks, where we can select Foreign Amount field and display it along with other fields.
Now, we are trying to get this information programmatically using stored procedure named as "TxnDetailByAccount". In this stored procedure we are not able to find the field "Foreign Amount". We would like to know whether there is any way to get this field displayed as QuickBooks provides it.
If not then, is it possible to get the Exchange Rate for the transaction?
Does this problem occur in UK and Australian QuickBooks editions as well?
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-08-03 13:09:15 |
Sorry, the "Foreign Account" and "Foreign Balance" columns are currently not avaliable for selection in the TxnDetailByAccount stored procedure report:
sp_report TxnDetailByAccount show Text, Blank, TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisMonthToDate'
However, the Amounts and Balance shown in the report are in your local currency values. To see the Foreign Currency value itself, look at the actual transaction in the transaction table for the corresponding transaction "Type".
The Exchange Rate represents further problems as the ExchangeRate column is often blank in the transaction tables. See: Error parsing complete XML return string, when creating multi-line invoice for more. |
|
|
|
|
Posted : 2006-08-03 17:52:59 |
Our current requirement is only for reading purpose. We are using QODBC to only read data from QuickBooks. You have mentioned that Exchange Rate field will create problem incase of record insertion.
Is this problem related to QuickBooks SDK?
As far as Foreign Amount is concerned, we will need to retreive the Exchange Rate from transaction tables for every distict Transaction Type and then calculate Foregin Amount accordingly. Could you please confirm whether we are correct in our understanding?
Also, can you please provide us a list of tables, which provides/does not provide the Exchange Rate field? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-08-04 00:58:49 |
The Foreign Amount is already in the actual transactions concerned and I've already pointed you to the currency table. For ExchangeRate columns see: Where are the schema rules for using QODBC? How do I use sp_columns?
Foreign currency is not used within USA, so there hasn't been a strong requirement to work on opening QODBC to these features and seeing that everyone outside USA is still on Intuit's qbXML SDK Release 3.0 while USA is about to go to the qbXML SDK Release 6 for QuickBooks 2007, there's not much point right now. |
|
|
|
|
Posted : 2006-08-04 01:24:03 |
There are few trasaction tables listed below which do not provide Exchange Rate
BillPaymentCheck, BillPaymentCredit, Deposit
Please confirm.
Also, ItemReceipt table has this field but it always contains value as Blank. Please advise.
I need Exchange Rate value for Historical Transactions. Hence i can not use current Exchange Rate from the Currency table. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-08-04 11:28:32 |
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:
ACCOUNTS RECEIVEABLE 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'
ItemReceipts are used to receive stock without a Bill, so Foreign currency doesn't apply. |
|
|
|
|