| Update Class for Journal Entries |
| Author |
Message |
| Rob |
| |
| Group | : Members |
| Posts | : 7 |
| Joined | : 2008-08-15 |
|
| Profile |
|
| Posted : 2008-10-28 03:08:13 |
Hi Tom,
I need to be able to find journal line items associated with a particular job and give it a particular class.
I'm using:
MS Access
Quickbooks Enterprise 8
QODBC 8.00.00.242
I've tried:
1) Creating a join with JournalEntryLineItems and another table that maps jobs to classes to update all line items that have a particular job to a particular class. I've used a similar method for bill line items, but for journal entries it does not work.
2) Creating join using just JournalEntryCreditLineItems or JournalEntryDebitLineItems and that gives me an error stating that the General Journal Entry is not in balance.
3) I did another similar to number 2 above but I linked the two journal entry line item tables via the TXNID.
For the latter two methods, I get an error that says the General Journal Entry is not in balance.
Any suggestions?
Robert |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-10-28 11:40:16 |
What you actually need to do is execute update statements like this:
UPDATE JournalEntryCreditLine SET JournalCreditLineClassRefListID = '10000-1144330695' WHERE TxnID = '389D-1071511025'
UPDATE JournalEntryDebitLine SET JournalDebitLineClassRefListID = '10000-1144330695' WHERE TxnID = '389D-1071511025' |
|
|
|
| Rob |
| |
| Group | : Members |
| Posts | : 7 |
| Joined | : 2008-08-15 |
|
| Profile |
|
| Posted : 2008-10-28 23:30:05 |
Hi Tom,
Wouldn't the method you suggest update all the line items of a journal entry to the same class?
What I need to do is update line items of a journal entry having a particular job to a particular class.
This is a problem because most of my journal entries have multiple jobs and hence there must be multiple classes.
So, I guess my question is how to update line items of a journal entry to different classes?
All the methods I tried before resulted in an error saying the general journal entry is not on balance.
Thank you,
Robert
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-10-29 07:46:13 |
Correct, but you can update by a journal line by line basis by adding the TxnLineID:-
UPDATE JournalEntryCreditLine SET JournalCreditLineClassRefListID = '10000-1144330695'
WHERE TxnID = '389D-1071511025' and JournalCreditLineTxnLineID = '389E-1071511025'
UPDATE JournalEntryDebitLine SET JournalDebitLineClassRefListID = '10000-933272658'
WHERE TxnID = '389D-1071511025' and JournalDebitLineTxnLineID = '389F-1071511025' |
|
|
|
| Rob |
| |
| Group | : Members |
| Posts | : 7 |
| Joined | : 2008-08-15 |
|
| Profile |
|
| Posted : 2008-10-30 01:51:31 |
Hi Tom,
When I try to execute the following:
UPDATE qryFindTxnIdJournalCreditLineforUpdating INNER JOIN JournalEntryCreditLine ON (qryFindTxnIdJournalCreditLineforUpdating.JournalCreditLineTxnLineID = JournalEntryCreditLine.JournalCreditLineTxnLineID) AND (qryFindTxnIdJournalCreditLineforUpdating.TxnID = JournalEntryCreditLine.TxnID) SET JournalEntryCreditLine.JournalCreditLineClassRefFullName = "NY - Psyop";
I get this error:
QODBC error: 3170, error when modifying a general journal transaction, 10053.
This kind of join has successfully worked for Bill Line Items.
I also tried what you suggested in your last post testing just an individual transaction and line item and I get the same error as I do for this join. The Sql I used for that was created by ms access and looks like this:
UPDATE JournalEntryCreditLine SET JournalEntryCreditLine.JournalCreditLineClassRefFullName = "NY - Mass Market"
WHERE (((JournalEntryCreditLine.TxnID)="A195F-1221341887") AND ((JournalEntryCreditLine.JournalCreditLineTxnLineID)="A198A-1221341887"));
The log for the join shows this:
QL Statement: UPDATE "JournalEntryCreditLine" SET "JournalCreditLineClassRefFullName"=? WHERE "FQPrimaryKey" = ?
3170 - There was an error when modifying a General Journal transaction. QuickBooks error message: Transaction not in balance.
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="7.0"?>
<QBXML>
<QBXMLMsgsRq onError = "continueOnError" responseData = "includeNone">
<JournalEntryModRq requestID = "1">
<JournalEntryMod>
<TxnID>A195F-1221341887</TxnID>
<EditSequence>1223490681</EditSequence>
<JournalLineMod>
<TxnLineID>A1961-1221341887</TxnLineID>
</JournalLineMod>
<JournalLineMod>
<TxnLineID>A1962-1221341887</TxnLineID>
.
.
.
</JournalEntryMod>
</JournalEntryModRq>
</QBXMLMsgsRq>
</QBXML>
Output XML:
<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<JournalEntryModRs requestID="1" statusCode="3170" statusSeverity="Error" statusMessage="There was an error when modifying a General Journal transaction. QuickBooks error message: Transactio
n not in balance." />
</QBXMLMsgsRs>
</QBXML>
The SDK log looks like this:
20081029.104659 I 2536 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 1000).
20081029.104700 I 2536 SpecVersion Current version of qbXML in use: 7.0
20081029.104700 I 2536 QBSDKMsgSetHandler MODIFY: General Journal
20081029.104703 E 2536 JournalEntryStorage::DoModify Failed to save General Journal transaction QuickBooks error message: Transaction not in balance. HRESULT=0x80041001
20081029.104703 I 2536 QBSDKMsgSetHandler Request 1 failed.
20081029.104703 I 2536 MsgSetHandler Finished.
20081029.104703 I 2536 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 1000), ret = 0.
Again, I get the same error whether I try to modify a single transaction or multiple transactions.
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-10-30 10:05:32 |
The problem occurs because QuickBooks wants both sides of the journal entry. So I thought I could try:
UPDATE JournalEntryCreditLine SET JournalCreditLineClassRefListID = '10000-933272658',
FQSaveToCache = 1 WHERE TxnID = '389D-1071511025'
and JournalCreditLineTxnLineID = '389E-1071511025'
UPDATE JournalEntryDebitLine SET JournalDebitLineClassRefListID = '10000-933272658',
FQSaveToCache = 0 WHERE TxnID = '389D-1071511025'
and JournalDebitLineTxnLineID = '389F-1071511025'
but there's a bug in QODBC that results in a "Error parsing complete XML return string (8)" error. I've logged this problem with the QODBC Software Engineer under QSupport Ticket No: BR00000091. I do not have a timeframe for a workaround or a fix. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-11-11 07:43:40 |
This bug will be fixed in a upcoming release of QODBC, in the meantime, the workaround is to include the TxnLineID in the Set portion like this:
UPDATE JournalEntryCreditLine SET JournalCreditLineClassRefListID = '10000-933272658',
JournalCreditLineTxnLineID = '389E-1071511025',
FQSaveToCache = 1 WHERE TxnID = '389D-1071511025'
and JournalCreditLineTxnLineID = '389E-1071511025'
UPDATE JournalEntryDebitLine SET JournalDebitLineClassRefListID = '10000-933272658',
JournalDebitLineTxnLineID = '389F-1071511025',
FQSaveToCache = 0 WHERE TxnID = '389D-1071511025'
and JournalDebitLineTxnLineID = '389F-1071511025'
Adding the TxnLineID gets around the "Error parsing complete XML return string (8)" error. |
|
|
|
