Skip to main content
Solved

Help with GI Creation

B
Freshman II
Forum|alt.badge.img

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. 

Best answer by Naveen Boga

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'

 

 

View original
    Did this topic help you find an answer to your question?

    5 replies

    Gabriel Michaud
    Captain II
    Forum|alt.badge.img+11

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

    Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
    B
    1 person likes this
    • B
      byates
    Gabriel Michaud
    Captain II
    Forum|alt.badge.img+11

    @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:

     

    1 Attachments
    Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett
    B
    Freshman II
    Forum|alt.badge.img
    • byates
    • Author
    • 74 replies
    • April 20, 2021

    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

    Naveen Boga
    Captain II
    Forum|alt.badge.img+19
    • Naveen Boga
    • Captain II
    • 3404 replies
    • Answer
    • April 21, 2021

    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'

     

     

    1 Attachments
    Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
    Gabriel Michaud
    B
    2 people like this
    • Gabriel Michaud
      Gabriel Michaud
    • B
      byates
    B
    Freshman II
    Forum|alt.badge.img
    • byates
    • Author
    • 74 replies
    • April 23, 2021

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

     

    Regards,

    Gabriel Michaud
    Naveen Boga
    2 people like this
    • Gabriel Michaud
      Gabriel Michaud
    • Naveen Boga
      Naveen Boga

    Reply


    Most helpful members this week

    Terms & ConditionsCookie settings

    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
    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