Skip to main content
Answer

Generic Inquiry: Grouping tab

  • June 3, 2025
  • 4 replies
  • 138 views
C
Jr Varsity III
Forum|alt.badge.img

Hi all, 

I am trying to build a generic inquiry to show all the inventory items regardless if it has transaction history however I am getting multiple result by inventory ID but when I tried grouping it with inventoryID I am losing inventoryID that do not have transaction history. The ff are my main DAC I used:

PX.Objects.IN.INItemSite

PX.Objects.IN.INItemSiteHistDay

PX.Objects.IN.INSiteStatus

PX.Objects.AR.ARTran

 

I have also attached the extracted GI incase anyone of you have any input how to make the inventory ID unique in the result. Thank you. 

 

 

Best answer by DrewNisley

I tried looking through this and (I think) got what you are looking for. Simply change your ARTran inner join to a left join and it should bring up any inventory items that have not been sold. Then group by InventoryItem.InventoryID. I think it will accomplish what you are looking for.

I’m not sure how you’re wanting to incorporate the FromDate and ToDate if you want to bring up items with no sales, but you seemed to mark their logic inactive so I’m assuming you moved away from that.

Hope this helps!

1 Attachments

4 replies

DrewNisley
Pro I
Forum|alt.badge.img+3
  • DrewNisley
  • Pro I
  • Answer
  • June 4, 2025

I tried looking through this and (I think) got what you are looking for. Simply change your ARTran inner join to a left join and it should bring up any inventory items that have not been sold. Then group by InventoryItem.InventoryID. I think it will accomplish what you are looking for.

I’m not sure how you’re wanting to incorporate the FromDate and ToDate if you want to bring up items with no sales, but you seemed to mark their logic inactive so I’m assuming you moved away from that.

Hope this helps!

Drew Nisley
Chris Hackett
C
C
Jr Varsity III
Forum|alt.badge.img
  • Crisel
  • Author
  • Jr Varsity III
  • June 5, 2025

Thanks for the input ​@DrewNisley I have the ARTran in inner still. 

I made it so that when there is a date range, the result would be those Inventory IDs within the range hence if there is no input in the date range, all inventory IDs should show. 

Chris Hackett
DrewNisley
C
Jr Varsity III
Forum|alt.badge.img
  • Crisel
  • Author
  • Jr Varsity III
  • June 5, 2025

Requesting this topic to be marked as solve or close. Thank you.

Chris Hackett
Community Manager
Forum|alt.badge.img
  • Chris Hackett
  • Acumatica Community Manager
  • June 6, 2025

Hi ​@Crisel - You can close by marking “Best Answer” on the appropriate reply.

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