ODBC Link to produce YTD Trial Balance by Account |
Author |
Message |
|
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? |
|
|
|
Tom |
|
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
|
|
|
|
|
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. |
|
|
|
|
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 |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-03-14 09:22:26 |
|
|
|
|
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 |
|
|
|
Tom |
|
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. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-03-20 11:26:06 |
|
|
|
|
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. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-05-08 09:56:09 |
|
|
|
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 |
|
|
|
Tom |
|
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? |
|
|
|
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 |
|
|
|
Tom |
|
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' |
|
|
|
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
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-11-25 23:44:09 |
|
|
|