Question

How to Include Inventory Items with 0 Qty Produced in Report

  • 25 September 2023
  • 5 replies
  • 65 views
E
Rank

I’ve created a report that groups Inventory Items so that I can sum Qty Produced for each item over a given time frame. The goal is to have a column for Inventory ID/Description, a Qty Produced Goal Column, and an Over and Under Column for the sum/difference where an item was over or underproduced where even if the item has not been produced, it would still show on the report. 

For instance, no CHOC400 had been produced, it would still appear on the report, where:

Goal = 700

Qty Produced = 0 

Under: -2060

Instead the report is only showing if the item has been produced. So, for example, an inventory ID, CHOC400 which has not been produced this month, isn’t showing at all. How can I fix this? 

I’ve attached a copy of the .rpx version of the report that’s saved as .doc so I could upload it. 

 

1 Attachment

5 replies

Laura02
Rank
Badge +18

Hello,

In the Relationships tab of Schema Builder, try a Left Join Type when connecting from the InventoryItem table to the production table where you’re pulling quantity produced from.

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
E
Rank

I currently have the AMMTran table joined LEFT to the InventoryItem table on InventoryID=InventoryID. 

Are you saying that I should add another relationship and InventoryItem LEFT AmProdItem where InventoryCD = InventoryID? 

 

I’ve added the 4th relationship which I think is what you meant @Laura02? When I added, I got a an error when trying to RUN REPORT. I also tried with InventoryID=InventoryID and still produced the same error. I had to remove the relationship to get the report to generate again. 

 

 

Laura02
Rank
Badge +18

Hi,

I guessed which table was on the left, incorrectly. Make the connection from AMMTran to Inventory Item a Right-join (on 3rd line), and the system will include all records from InventoryItem, whether there are records in AMTran or not (whether or not any were produced in the time-frame of the report).

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
E
Rank

So this was my corrected attempt when I cleaned up the join and it’s still not showing any items with 0 quantities. 

 

 

Chris Hackett
Rank
Userlevel 7
Badge

Hi @ellidacornavaca were you able to find a solution? Thank you!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved