Skip to main content
Question

How to Include Inventory Items with 0 Qty Produced in Report

  • 25 September 2023
  • 6 replies
  • 126 views

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. 

 

6 replies

Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • September 25, 2023

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


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
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • September 25, 2023

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


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
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2640 replies
  • November 2, 2023

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


Hi Chris,

I was not able to find a solution for this. 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings