Solved

Generic Inquiry-How to ignore grouping on column


Userlevel 1
Badge

Hello all,

I’m working on a generic inquiry. I need a total inventory amount for each item. The result as below. 

The item existed in different warehouse. 

 

When I add a grouping for InventoryID. The result become to : 

 

My question is how do I ignore grouping on MSRP column?

 

My GI setting is below.

 

 

icon

Best answer by wfilipiak67 27 May 2021, 22:32

@larryly , You can set the Aggregate Function column to AVG. This will ‘seem” to ignore the grouping by doing some math.

Bill

View original

8 replies

Userlevel 6
Badge +4

Hi  @larryly,

If possible, can you please share the XML format of the generic inquiry?

Userlevel 1
Badge

@Naveen B    I attached the GI file.  My other thinking is join the msrp to main table after grouping. But I don’t know to do it on GI. 

Userlevel 2
Badge

@larryly , You can set the Aggregate Function column to AVG. This will ‘seem” to ignore the grouping by doing some math.

Bill

Userlevel 6
Badge +4

@larryly  Since all are same, you can use MAX/AVG  with this grouping will ignore for RecPrice..

Userlevel 2
Badge +1

If you are using grouping on your GI look out for numeric fields since the aggregate function will default to SUM if left blank. As @wfilipiak67 suggested,AVG will be your go-to if all values are identical and you want it to remain unchanged.

Userlevel 1
Badge

Thank you so much. It’s working.  I learned a lot from you all. 

@larryly what I do is set the results grid data field in the GI to =[Item.RecPrice]. 

This is effectively a very short equation that says just print that value and the best part is even when grouped the aggregate function will not apply to it. 

Userlevel 1
Badge

@abrunner78 You are right. I tried it. It’s working. It’s a good point that use some expressions on column to ignore grouping. Thank you!!

Reply


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 — 2020  Acumatica, Inc. All rights reserved