I'm trying to create a report that shows inventory counts by item, location and lot code as of a specific date, but can't get it to work. What do I need to do to create this report? It's ok if it's just a long list of data, because I can export and clean it up in Excel, but so far, I haven't been able to create a report I can export.
Here are a couple of things I've tried and the problems I've encountered.
Physical Inventory Worksheet: The On Hand quantity changes with the Location filter, but the Serial/Lot Numbers(Qty) shows total quantities for all locations.
I got close with a custom quantity on hand detail report, but the Transaction Serial/Lot Number quantities don't use consistent units of measure - sometimes the qty is eaches, other times it's cases. In the screenshot below, Quantity on Hand is Eaches, the first line with multiple Transaction Serial/Lot Numbers is also eaches (342 + 18 = 360 eaches). The second is cases (9 + 361 + 230 = 600 cases * 6 = 3600 eaches), the third is back to eaches again (2160 + 1080 = 3,240 eaches)
Thank you