Knowledgebase
[QODBC-Desktop] How to create Journal Entries using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

Two Examples of Creating Journal Entries

Create a Journal Entry with 2 Credit Lines and 2 Debit Lines

Note: This example creates one Journal Entry with 2 credit lines and 2 debit lines. Note the FQSaveToCache field, set to True except on the last line. Also, when saving Journal Entries, QuickBooks will reject transaction which does not balance the Credit and Debit sides.

First Credit Line

INSERT INTO "JournalEntryCreditLine" ("RefNumber", "JournalCreditLineAccountRefListID", "JournalCreditLineAmount", "JournalCreditLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270542', 1.11, 'Test Memo 1', 1)

Second Credit Line

INSERT INTO "JournalEntryCreditLine" ("RefNumber", "JournalCreditLineAccountRefListID", "JournalCreditLineAmount", "JournalCreditLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270543', 2.22, 'Test Memo 2', 1)

First Debit Line

INSERT INTO "JournalEntryDebitLine" ("RefNumber", "JournalDebitLineAccountRefListID", "JournalDebitLineAmount", "JournalDebitLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270544', 1.11, 'Test Memo 1', 1)

Second Debit Line

INSERT INTO "JournalEntryDebitLine" ("RefNumber", "JournalDebitLineAccountRefListID", "JournalDebitLineAmount", "JournalDebitLineMemo", "FQSaveToCache") VALUES ('1', '120000-933270545', 2.22, 'Test Memo 2', 0)

Create a Journal Entry with 1 Credit Line and 1 Debit Line

Note: The QuickBooks sample files set the QuickBooks system date at Dec 15, 2007 here, so I have used these sample SQL insert statements to demonstrate using future dates in a journal entry:

Create a Credit Line

     INSERT INTO "JournalEntryCreditLine" ("TxnDate", "RefNumber",
     "JournalCreditLineAccountRefListID", "JournalCreditLineAmount",
     "JournalCreditLineMemo", "FQSaveToCache") VALUES
     ({d '2008-01-01'}, '1', '120000-933270541', 1.11,
     'Test Future Dated Credit', 1)


Note: 
 VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

 

Create a Debit Line

     INSERT INTO "JournalEntryDebitLine" ("TxnDate", "RefNumber",
     "JournalDebitLineAccountRefListID", "JournalDebitLineAmount",
     "JournalDebitLineMemo", "FQSaveToCache") VALUES
     ({d '2008-01-01'}, '1', '120000-933270541', 1.11,
     'Test Future Dated Credit', 0)

TxnLineIDs Locate

     SELECT JournalCreditLineTxnLineID, JournalDebitLineTxnLineID
     FROM JournalEntryCreditLine, JournalEntryDebitLine
     WHERE JournalEntryCreditLine.RefNumber=JournalEntryDebitLine.RefNumber
     and ((((JournalEntryDebitLine.TxnDate)>={d'2008-01-01'}))
     or (((JournalEntryCreditLine.TxnDate)>={d'2008-01-01'})))

(170 vote(s))
Helpful
Not helpful

Comments (2)
Jason
10 February 2015 04:07 PM
When I insert a journal entry like this, it works great but then it looks like Quickbooks is editing (or duplicating?) the accounts that I'm using on the Account table.

For example, if I insert to the "Professional Fees" account using a list ID of "XYZ", the insert works correctly but then a query against the Accounts table will show two "Professional Fees" accounts -- the original one and new one with all the same values (including ListId = "XYZ") but a different TimeModified.

Any thoughts on why this might be happening or what I could do?
Jack
23 February 2015 01:26 PM
Hi Jason,

I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.

We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share QuickBooks Version details: Press f2 on QuickBooks UI & share screenshot.
4) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
5) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
6) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).