Skip to main content
Solved

Report for Inventory Unit Sales

  • February 3, 2026
  • 4 replies
  • 41 views
J
Freshman I

Hi all!

I’m trying to create a report for my company that will show inventory unit sales by INVENTORYID.

So far, every combination I’ve tried is “tripling” the units sold in the final query.

I have the below Joins:

ARTran Full → InventoryItem

ARTran Left → ARInvoice

InventoryItem Left → POVendorInventory

InventoryItem Left→ InItemCost

Grouped by: ARTran.InventoryID.

Results Grid:

  • VendorID
  • VendorInventoryID
  • InventoryCD
  • InventoryItem.Descr
  • (Calculated Field) → =sum(iif([ARTran.TranType] = ‘CRM’, -1 * [ARTran.Qty], [ARTran.Qty])

All fields are showing correct except for the unit sales which seem to be tripled in quantity. Hoping someone can point out where this may be coming from :) 

Thank you!

Best answer by nhatnghetinh

Hi ​@JHuang 

Please try deactivating these Join (InventoryItem Left → POVendorInventory & InventoryItem Left → InItemCost) to see if it's still "tripling" ? => If there is no more error then the cause is 1-to-many joins (POVendorInventory, INItemCost)

The solutions are as follows:

Option 1: Create multiple GIs (as you suggested)

Option 2: Force 1–to–1 joins

If joining is required, add conditions like the following example:

Filter POVendorInventory.IsDefault = True or Filter POVendorInventory to a single vendorID or CuryID..

Filter INItemCost to a single CuryID..

=> Ensure only one row per InventoryID is joined.

 

Best Regards,

NNT

    4 replies

    nhatnghetinh
    Captain II
    Forum|alt.badge.img+13
    • nhatnghetinh
    • Captain II
    • February 4, 2026

    Hi ​@JHuang 

    The “tripled” quantity is caused by 1-to-many joins (POVendorInventory, INItemCost) duplicating ARTran rows, so SUM(Qty) is overcounted.

     

    Best Regards,

    NNT

    I have devoted a great deal of my time and passion to Acumatica ! 🌐https://www.linkedin.com/in/phambaotruong
      J
      Freshman I
      • JHuang
      • Author
      • Freshman I
      • February 4, 2026

      Thanks ​@nhatnghetinh . Any idea how I can get around this one-to-many relationship? I know in SQL we’d have bridge entities but not sure how to create that? Or is it better to create separate GIs and then link them all together in one final GI?

        nhatnghetinh
        Captain II
        Forum|alt.badge.img+13
        • nhatnghetinh
        • Captain II
        • Answer
        • February 4, 2026

        Hi ​@JHuang 

        Please try deactivating these Join (InventoryItem Left → POVendorInventory & InventoryItem Left → InItemCost) to see if it's still "tripling" ? => If there is no more error then the cause is 1-to-many joins (POVendorInventory, INItemCost)

        The solutions are as follows:

        Option 1: Create multiple GIs (as you suggested)

        Option 2: Force 1–to–1 joins

        If joining is required, add conditions like the following example:

        Filter POVendorInventory.IsDefault = True or Filter POVendorInventory to a single vendorID or CuryID..

        Filter INItemCost to a single CuryID..

        => Ensure only one row per InventoryID is joined.

         

        Best Regards,

        NNT

        I have devoted a great deal of my time and passion to Acumatica ! 🌐https://www.linkedin.com/in/phambaotruong
          J
          Freshman I
          • JHuang
          • Author
          • Freshman I
          • February 4, 2026

          Thank you ​@nhatnghetinh 

            Terms & ConditionsCookie settingsAccessibility statement
            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