Skip to main content

Hello,

We are trying to create a GI that flags if an more than one item has the same customer cross reference/ alternate IDs. 
 

In an ideal situation - the result for the below should be 3. 

Results prior to grouping

We concatenated Alternate ID and Customer ID and did a count on the concatenation

We grouped the generic inquiry by AlternateID. However, the count only shows up as 1.

Results after grouping

Is there a way to display all line items, their alternate id, customer and counts. The expected result should look like below with an extra column towards the end called count with values 3 (in this scenario).

Expected result

Could you please advise if this is achievable? 

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. 

Result after trying the recommended grouping

 

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
 

Result in SQL Query

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


Reply