Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

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

Forum : ODBC Link to produce YTD Trial Balance by AccountSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v7 Forum

 New Topic 
 
 Post Reply 
[1]  
 ODBC Link to produce YTD Trial Balance by Account 
 Author   Message 
  Dynacinc 
  
 Group: Members 
 Posts: 6 
 Joined: 2007-03-13 
 Profile
 Posted : 2007-03-13 12:12:22

We are looking link our application (DynacTools) through an ODBC link to Account, JournalEntryCreditLine and JournalEntryDebitLine tables.  In essense we are looking to import a YTD Trial Balance by Fiscal Year and Period. We Selected Account.OpenBalance, Account.AccountNumber,Account.ListID joined to JournalEntryDebitLine.JournalCrditLineAccountRefListId, JournalEntryDebitLine.JournalDebitLineAmount (similar for Credit) but we find that some accounts are missing in the JournalEntry and visa versa in the Account table.

Does anyone have any thoughts as how to ensure data integrity?

 

 
Dynacinc 
 
  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-03-13 13:07:08

I'm not sure what you are trying to acheive here, but as far as data integrity is concerned, there are no issues when using the following:-

SELECT JournalEntryLine.TxnDate as Date, JournalEntryLine.JournalLineType as Type,
JournalEntryLine.JournalLineAccountRefFullName as Account,
JournalEntryLine.JournalLineAmount as Amount, Account.Balance, Account.AccountNumber as "Acc No"
FROM JournalEntryLine, Account
where JournalEntryLine.JournalLineAccountRefListID=Account.ListID

 

  Top 
  Dynacinc 
  
 Group: Members 
 Posts: 6 
 Joined: 2007-03-13 
 Profile
 Posted : 2007-03-13 13:23:53

Thanks for your prompt reply Tom.  We are looking to create the following table fields ACCOUNT_ID, AMOUNT (which is YTD Trial Balance Amount) by FISCAL_YEAR and PERIOD in our application using the QODBC link.  We will work with your SQL suggestion tomorrow and let you kno who wit works.  Thanks again.

 

 
Dynacinc 
 
  Top 
  Dynacinc 
  
 Group: Members 
 Posts: 6 
 Joined: 2007-03-13 
 Profile
 Posted : 2007-03-14 04:24:22
The JournalEntryLine querry works well but we only get (General) Journal Entry Transaction Types.  Our issue remains what other tables must be called to construct a full Trial Balance for all other Transaction Types i.e. Deposits, Bills, Credit Memos etc.  Can anyone provide a list?  Or is there a view we can use? We need to be able to define the YTD Trial Balance Amount for each Account by Fiscal Year and Period 

 
Dynacinc 
 
  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-03-14 09:22:26

There's no need to reconstruct trial balances from tables because QODBC also accesses the QuickBooks reporting engine directly. So it's just a matter of deciding what information you "actually" require to interface with DynacTools.

See: How do I use the QuickBooks Reporting Engine with QODBC? I've heard something about sp_report ? 

 

  Top 
  Dynacinc 
  
 Group: Members 
 Posts: 6 
 Joined: 2007-03-13 
 Profile
 Posted : 2007-03-16 12:27:37
We have successfully used sp_TrailBalance using parameters DateMacros of This Month and LastMonthToDate.  Is there a parameter where the user can specify a Fiscal Year and Period?  Secondly is the a source or a  person from whom we can gain an understanding of  the sql behind the sp_TrialBalance 

 
Dynacinc 
 
  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-03-16 19:43:17
That's what this forum is all about. All I need is for you to go into QuickBooks and work out what you need and let me know. Simply upload a screen dump of the report. See: How do I upload images to my forum post? for help on doing this. 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-03-20 11:26:06
Try looking at: How do I run a Trial Balance Report?  

  Top 
  Dynacinc 
  
 Group: Members 
 Posts: 6 
 Joined: 2007-03-13 
 Profile
 Posted : 2007-05-08 09:45:50

We have successfully used QODBC to link our DynacTools application to Quickbooks at a number of clients that have purchased your the QODBC application - thanks for your input.

One new issue is that our clients are expericenceing the following when attempting to close QuickBokks following use with QODBC - "The comnpany file cannot be closed at this time because there is another application (Msg DB Init) which is using it........" Only way out is throught Task Manager.

Any ideas as to how to avoid this issue.

 

 
Dynacinc 
 
  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-05-08 09:56:09

Your application needs to close the QODBC connection, if you are running the application through a Windows Service, if may take 10 minutes for the connection to be closed by the Windows O/S.

But you should be running the application in unattended mode, see: How do I setup QODBC to auto-login into QuickBooks and start it if it's not running? 

 

  Top 
  Sami 
  
 Group: Members 
 Posts: 6 
 Joined: 2008-11-13 
 Profile
 Posted : 2008-11-13 22:59:56

Hi all,

finally I found the forum that might help me. I'm trying to simply retreive the account balance via a query on VBDemo32 (QODBC). I'm using QuickBooks Pro 2008.

I executed exactly the SQL script written above by Dynacinc but the column Amount.Balance contained no values. Then I did a simple select on the Account table and found the Balance field does contain any data for all accounts.

in QuickBooks application, I am able to see that the accounts do really have balances. In the balance sheet and in the main screen (summary of accounts) Balance column displays real data.

How can I retreive the balance?

And why when I sum the column JournalEntryLine.JournalLineAmount I do not match the balance displayed in the balance sheet inside QuickBooks application?

Thanks in advance.

Sami

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-11-13 23:10:31

Try this instead:

SELECT JournalEntryLine.TxnDate as Date, JournalEntryLine.JournalLineType as Type,
JournalEntryLine.JournalLineAccountRefFullName as Account,
JournalEntryLine.JournalLineAmount as Amount, Account.Balance, Account.AccountNumber as "Acc No"
FROM JournalEntryLine calldirect, Account calldirect
where
JournalEntryLine.JournalLineAccountRefListID=Account.ListID

then see: How do I setup the QODBC Optimizer? Where are the Optimizer options? 

 

  Top 
  Sami 
  
 Group: Members 
 Posts: 6 
 Joined: 2008-11-13 
 Profile
 Posted : 2008-11-13 23:33:27

Thanks Tom for your reply.

I tried your query and I've added one more condition to filter on one Account Number (Account.AccountNumber = '2175'). It has been 10 minutes taht the query is running and till now no result is displayed.

What about the optimizer, why should I set it up?

Thanks

Sami

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-11-14 16:44:33

To use that filter you need to run this fist:

Select ListID from Account
where Account.AccountNumber = '2175'

and then use the ListID returned in place of the one in bold below like this:

SELECT JournalEntryLine.TxnDate as Date, JournalEntryLine.JournalLineType as Type,
JournalEntryLine.JournalLineAccountRefFullName as Account,
JournalEntryLine.JournalLineAmount as Amount, Account.Balance, Account.AccountNumber as "Acc No"
FROM JournalEntryLine, Account
where JournalEntryLine.JournalLineAccountRefListID=Account.ListID
and JournalEntryLine.JournalLineAccountRefListID='40000-933270541'

 

  Top 
  Sami 
  
 Group: Members 
 Posts: 6 
 Joined: 2008-11-13 
 Profile
 Posted : 2008-11-25 20:59:09

Hi,

is there a function equivalent to ROW_NUMBER() generating incremetnatl number for each row?

I'm trying to do something like this:

SELECT Account.AccountNumber as "Acc No", Account.Balance, '2008-2009' as "FiscalYear", row_number() as "Account_order"
FROM Account calldirect

Thanks

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-11-25 23:44:09

Sorry, no.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to