We’ve had a number of requests for a report that will easily show users their Quantity on Hand by Lot. On the surface this doesn’t sound like a very difficult request, in fact one might think this report is available inside QuickBooks. There is in fact a report in QuickBooks but you have to double-click on each individual item on the report to see the actual Lot Number quantities. This isn’t a very user-friendly solution.
We’ve set out to tackle this report a couple of times now and this time we believe we have a solution. We have in fact created a report using the QODBC driver that is included with your QuickBooks Enterprise purchase however, there is one stipulation that needs to be considered for the report to be accurate. In the remainder of this article we’ll cover everything that must be done in order to set your data up so you can produce an accurate Quantity on Hand by Lot Report outside of QuickBooks.
The reason we’ve had such a hard time creating this report is due to an issue with Intuit’s Software Developers Kit (SDK). The SDK is the primary means of accessing QuickBooks Desktop data from outside of QuickBooks. In this situation when Intuit added Lot and Serial tracking to QB they left a rather important piece out of the SDK and that missing piece is the quantity value on Lot Number Adjustments. Let me explain.
In the first image below, you can see the Lot Numbers in Stock Report available in QB. Sure, the report shows the Lot Numbers you have in stock but you don’t get to see the Quantities on Hand for those Lots. That is unless you double-click on the Lot Numbers. In the second image below, we see the results of drilling down into the report. Now we can see we have 75 of Lot 205 and 73 of Lot 207. Again, not a very user-friendly solution and this is why customers have asked for a custom solution.
Now that we understand the issue let’s look at how QuickBooks works with Lot Numbers.
Throughout this article we’ll be working with an inventory item called Red Paint. In order to create an accurate Quantity on Hand by Lot Report we need to make sure Lot Numbers are showing on all forms. This way you’re able to enter the Lot Numbers as they come into the system and when they leave. The image below shows the Advanced Inventory screen under the Items & Inventory Preferences in QuickBooks.
Now that we have QuickBooks setup to work with Lot Numbers let’s take a look at how we use them. One way to bring Lot Numbers into QuickBooks is to enter a Bill. Below we see that we have a Bill for our Red Paint. The Item shows up twice because we’ve received two different Lot Numbers, 205 and 207.
Now when we run a QuickReport on the Item in QB we see the Lot and Quantity info from that Bill.
Now let’s look at how we can make Adjustments to these Lots. If all we need to do is adjust the Lot Quantities because maybe they were entered incorrectly on the Bill you could do an Adjustment transaction with a Type of Lot Number. (Yes you could simply change the Bill but play along with us) However, if you do this then any external tools you use outside QuickBooks that work with Lot Number quantities will be incorrect. This is because the SDK does not provide access to the quantities in the Lot Number Adjustment Type. Below is an image of what one of these Adjustments looks like.
A way to demonstrate the issue is by rerunning the QuickReport for our Red Paint. Now on the report we can see that we have an Inventory Adjustment but the Quantities show Zeros, even though we increased one Lot by 25 and reduced the other by 25. However, the report is still reporting the Total Quantity correctly, which we would expect since this isn’t labeled as a “Lot Number Report”.
Now if we want to know exactly how many we have of each Lot we need to go back and take a look at our Lot Number in Stock Report and drill-down on the item. In the below image we can see we have 75 of each Lot. Again, not an ideal way of knowing how many of each Lot we have in stock, especially when we may have hundreds of Lot controlled Items.
While we’re reviewing Lot Number Adjustment Types let’s take a look at what happens if we don’t have offsetting quantities in our Adjustment Transaction. We’ll do this by going back into our Adjustment Transaction and only increase Lot 207 by 5 rather than 25.
After we save the updated Adjustment and we re-run our Inventory Item QuickReport we see that we still have 150 in stock but when we look at the Lot Number in Stock Report, we only have 130 of those with Lot Numbers. See the two screenshots below for reference.
Rather than using an Adjustment Type of Lot Number we suggest you use a Quantity, or Quantity and Value, Adjustment. However, when working with Lot Numbers the Quantity Adjustments can get a bit tricky.
One thing to keep in mind on Quantity Adjustments is you can only list an Item once per transaction, which means we can’t adjust both of our Lots on a single transaction. Another thing to keep in mind is the Qty on Hand is the total of All Lots. When you want to adjust a single Lot, you will want to enter the Qty Difference and the Lot Number and ignore the New Quantity column. The issue here is you must know what QuickBooks has for the current quantity for the Lot, which our report can help you with. You can also use the drop-down in the Lot Number column but that doesn’t populate until you’ve specified a Quantity.
After entering the first Quantity Adjustment we enter the second which we’ll use to increase Lot 207. Again, we only focus on the Qty Difference and not the Qty on Hand or New Quantity fields. On this second entry QuickBooks did not populate the Lot Number drop-down, we had to manually enter the Lot Number.
Now notice when we look at the Inventory Item QuickReport we see a Qty value for all of our transactions and the Lot Number in Stock drill down shows we have 75 of each Lot.
Now let’s sell some of our Red Paint. When the Invoice is entered be sure to specify your Qty and Lot Number.
After we save our Invoice, we see the Inventory Item QuickReport shows we have 130 and the Lot Number in Stock Report also show we have a total of 130 on hand.
A quick note about Quantity and Total Value Adjustments when using Lot Numbers. Like the Quantity Adjustment, be sure to enter the Qty Difference and don’t worry about the New Quantity. However, the New Value column is the Total Value of ALL Lot quantities.
Our Custom Quantity on Hand by Lot Report
As we mentioned in the beginning of this article, we’ve been asked many times to create a Lot Quantity on Hand Report and up till now we’ve said no because there is no way for us to ensure the report is 100% accurate. Well we finally gave in and created a Crystal Report using the QODBC driver by FLEXquarters, the one that is included free with QuickBooks Enterprise. We still can’t guarentee the accuracy of the report but that is only when a user enters a Lot Number Adjustment transaction. As long as you stick to Quantity and Value Adjustments the report will be accurate.
Below is a screenshot of the report we created. As you can see you’re able to see the Lot Quantity without drilling down into the report, like you have to do in QB.
If the user would like to see the Transactions that make up the Lot Quantity, they are able to drill down in the report and see the Quantity, Transaction Type, Ref Number and Date of each Transaction.
Finally, if the user does in fact enter a Lot Number Quantity Adjustment the Report will display an asterisk next to the Lot Number and will display the Quantity in Red, indicating that we can’t guarantee the accuracy of that Lot Quantity.
When the user drills down into the details they will be able to identify the offending transaction by the Inventory Adjustment with a Zero Quantity.