Solved

Trying to sort by grouping in a GI

  • 22 April 2021
  • 5 replies
  • 310 views

Userlevel 5
Badge +2

Hi, 

I am trying to do a GI get Month-To-Date, Year-To-Dae & Prior-Year Sales totals for an inventory item. 
There are multiple warhouses setup. 
I am using the INTran table, with a condition of the item & the TranType of Invoice. 

If I sort the results in Site Order (SIteID), the results do show in Site order. 
However, if I set the grouping to be by SiteID, results are totaled correctly by do not appear in Site Order.
Is there a way to sort by the grouping conditions?

No grouping:

With Grouping:

 




 

 

icon

Best answer by Naveen Boga 22 April 2021, 19:42

View original

5 replies

Userlevel 7
Badge +17

Hi @wfilipiak67 If possible, can you please share the above Generic Inquiry?

 

Userlevel 5
Badge +2

Here is the XML of the Inquiry. 
It has the Grouping checked

Thank you for any assistance.

Userlevel 7
Badge +17

Hi @wfilipiak67  I have done slight modifications to your Generic Inquiry and grouping is working as expected. Attached GI here, please verify.

Userlevel 7
Badge +7

Hi @wfilipiak67 ,

Although the INTran data access class is configured to display the SiteCD when you add the SiteID to the result grid, your GI is actually sorting based on the SiteID field.  You can see this by looking at the SQL trace after viewing the GI.  I would suggest that you add the INSite DAC and sort on the SiteCD, which it looks like is what @Naveen B has already done in his revised XML :)

...
WHERE ( [INTran].[CompanyID] = 2) AND  ( [INTran].[BranchID] IS NULL  OR [INTran].[BranchID] IN ( 22, 16, 20, 17, 21)) AND ( [INTran].[InventoryID] = @P0 AND [INTran].[TranType] = 'INV')
GROUP BY [INTran].[SiteID]
ORDER BY [INTran].[SiteID] OPTION(OPTIMIZE FOR UNKNOWN)

Userlevel 5
Badge +2

Thank you both! 
That worked great! 

 

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