Hi @kanupindi ,

It should be acheivable.

You should add the InventoryID and BAccountCD to the grouping. 

Also, your count field does not have to be a concatenation - you could just count the AlternateID field.

Hope this helps!

Laura

Hi @lauraj46, Thank you very much for your swift response. I tried what you suggested, unfortunately, it did not work. The alternate ID that I am testing should return three results, however it returns 2 rows and the count says 1 for each item. 

I mimicked this in SQL server, and it seems to work after I do a count(AlternateID) followed by a sum of the count. Is that something that we could do in Acumatica? I tried testing it, but it kept throwing an error - that Acumatica does not allow subqueries
 

Please advise if you have any other ideas, thank you! :)

Hi @kanupindi ,

You cannot have subqueries in a GI, however you could group and count in the GI, and then create a shared filter tab on the results. I'm not sure if that's necessary in this case.  Please export and attach the XML for your GI to this thread and I'll take a look at it.

Laura 

Hi @lauraj46, please see attached XML file of the GI we were testing. Thank you!

Hi @kanupindi ,

In order for the counts to work, you should remove the AlternateID from your result list.  Any columns that are not part of the grouping must be assigned an Aggregate operation.  This is similar to how a SQL query works.

Attached is a revised query.  I also created a shared filter called Duplicate Customer Part IDs.  The shared filter selects only the rows with a count >1.  You can use shared filters on dashboards and business events.

Hope this helps!

Laura

Hi @lauraj46 , thank you very much! I appreciate your help and time! However, in our case - we would also like to display Alternate ID, because that will help us determine which alternate ID is repeating for an item. This will help the team to go and modify. 

Would it work if we include alternate ID and add it to group by clause?

Hi @kanupindi ,

Unfortunately it will not work to add it to the group by clause because then your count will always be 1. 

You could instead accomplish this by using a side panel:

Build a second GI based on the INItemXRef.  Add the Inventory ID and the Customer as parameters for the second GI.  Add the columns that your users need to see in the results grid.

Add this second GI as a side panel in the Navigation tab of your original GI.

The users can click on a row and they will see the details in the side panel on the right.

Hope this helps!

Laura

Hi @lauraj46 , That is a good idea. I will try that and get back to you. Thank you again! 

Hi @lauraj46 , it works! Thank you very much! 

My pleasure @kanupindi !

Laura