Knowledgebase
[QODBC-Desktop] How to Create an Inventory Adjustment using QODBC
Posted by Wilson (QODBC Support) on 07 February 2012 05:58 AM

Manually modify your Item Inventory quantity

How to Create an Inventory Adjustment using QODBC

Instructions

Although QuickBooks automatically adjusts your inventory quantities after every purchase and sale, sometimes you still need to adjust them yourself from time to time. QuickBooks allows QODBC to access item inventory adjustment records by modifying table InventoryAdjustmentLine. This article will show you how to create an Inventory Adjustment record using QODBC.

There are two fields InventoryAdjustmentLineQuantityAdjustmentQuantityDifference and InventoryAdjustmentLineQuantityAdjustmentNewQuantity in the table "InventoryAdjustmentLine". Both of them can be used to insert records.

Use Field InventoryAdjustmentLineQuantityAdjustmentQuantityDifference

Here is the sample statement:

INSERT INTO InventoryAdjustmentLine (AccountRefListID, InventorySiteRefListID,InventoryAdjustmentLineItemRefListID,InventoryAdjustmentLineQuantityAdjustmentQuantityDifference)
VALUES ('1D0000-934380913', '80000001-1481767266','280000-1071524260',-100)

The value in this field stands for the adjust quantity. For example, if you need to reduce 100 units of this inventory, value this field -100.

Here is the result in VBDemo.

 

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

 

And this is the result in QuickBooks:

Use Field InventoryAdjustmentLineQuantityAdjustmentNewQuantity

We can also use field InventoryAdjustmentLineQuantityAdjustmentNewQuantity to do the same job. Here is the sample code:

INSERT INTO InventoryAdjustmentLine (AccountRefListID, InventorySiteRefListID,InventoryAdjustmentLineItemRefListID, InventoryAdjustmentLineQuantityAdjustmentNewQuantity )
VALUES ('1D0000-934380913', '80000001-1481767266','280000-1071524260',6600)

Value "6600" in the field stands for the quantity after adjustment, that's the only difference.

Here is the result in VBDemo:

Here is the result in QuickBooks:

 

You can ignore the field "InventorySiteRefListID" in case you have not turned ON multiple inventory sites feature in QuickBooks.

(8 vote(s))
Helpful
Not helpful

Comments (5)
Akash
20 September 2012 04:53 PM
I'm new to Quickbooks. I don't even know which key words to use to search for anresws to my questions about the program. If you can point me to the appropriate sites, that would be great. Here's my problem.I work for a law firm. A typical retainer agreement has the following figures:$10,000 total balance$1,000 down payment due today$250 per month for the next 36 months.Other info:Send a reminder letter (ranging from mild to harsh) if payment is missed in 1-3 months.Send a letter of final notice if payment is missed in 4-6 months.Send a letter informing the client that we'll take them to small claims court if no payment is made after 6 months.As you can see, I need to keep track of which clients have been missing their payments.Can you help? Thanks.
Noah
18 June 2014 03:23 PM
I am using your query as an example but I am getting the following error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Here is the query i am trying to run (adapted to use openquery):

insert openquery (qodbc, 'select AccountRefListID,InventoryAdjustmentLineItemRefFullName,
InventorySiteRefListID,InventoryAdjustmentLineQuantityAdjustmentQuantityDifference
from InventoryAdjustmentLine where 1=0 ')
VALUES ('8000002B-1230317992','abc123','1,'80000001-1334837985')

Other openquery sql statements work without issues (select, update and insert).

Any help you could give me would be appreciated.
Jack
07 July 2014 12:06 PM
Hi Noah,

I kindly request you to please raise support ticket to 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 the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Marcellous Rowe
29 January 2018 04:18 PM
how to (delete) Inventory Adjustment Line in qodbc
Jack
30 January 2018 08:59 AM
Hi,

Please refer below sample query for deleting InventoryAdjustment using QODBC.

For deleting single line:
Delete from InventoryAdjustmentLine where InventoryAdjustmentLineTxnLineID='5E4B-1197715818' and TxnID='5E49-1197715818'

For deleting whole records:
Delete from InventoryAdjustment where TxnID='5E49-1197715818'

If you are still the facing issue, 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

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).