Skip to main content

Good Afternoon,

 

I am looking for some assistance with creating a GI. I am using the Inventory “Issues” GI as the basis. I am trying to modify it to include information from the INTrans table. I would like to bring in the following Properties:

INTrans.SiteID

INTrans.InventoryID

INTrans.Qty

INTrans.TranCost

Then add an additional condition to only show item that have INTran.ReasonCode of “Damaged”

 

Essentially I am trying to create a GI that shows me the total cost/qty by site and Inventory ID of all “Damages”. Using this to begin tracking manufacturing shrink/waste.

 

Currently I am getting error when trying to view the GI so I am sure I did something wrong with the tables and relations. 

Attached is the GI as it is now. 

Thank you for the assistance. 

Taking a look -- will update this in a few minutes


@byates there are few issues issues:

  • The INTran table is referenced in the conditions, however the relation between INRegister and INTran is not active right now.
  • “Issue” is the user-facing value, but not what you find in the database in the DocType field. The internal value is simply I. You need to check the “From Schema” checkbox if you want to work with user-facing values.

Here’s how the Conditions and Relations tabs look like after making the changes:

 


Thanks for this Gabriel!

However, it is still not returning the expected results. Currently it is only returning transaction for Issues generated by Sales Orders that were processed. 

I am looking for the transactions  generated by going thru Inventory>Issues. We then use a reason code of “Damaged”. I tried to add that to the conditions but the results come up blank despite knowing there are a good amount of transactions. ( I have it inactive now due to the blank results)

Any thoughts on this? Apologies if I did not explain well and happy to clarify as needed.

 

Thanks


Hi, @byates @Gabriel Michaud 

I have done the slight modifications to the above GI and I can able to fetch the expected results (Getting DAMAGED Issue type of records only which are released from Issues screen but NOT from SO)

Attached modified Generic Inquiry and hope that helps


SQL Query:
SELECT * From INTran IT
left join INSite INS on IT.CompanyID= INS.CompanyID and IT.SiteID= INS.SiteID
Inner JOIN INRegister INR on INR.CompanyID=IT.CompanyID and INR.DocType=IT.DocType and INR.RefNbr = IT.RefNbr 
WHERE IT.CompanyID=2 and INR.OrigModule='IN' and IT.ReasonCode='DAMAGED'

 

 


Thank you both for the help. Appreciate the efforts and the explanations along the way!

 

Regards,


Reply