Solved

Filter Generic Inquiry by Count Of Concatenated Fields

K
Rank
Userlevel 4
Badge

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? 

icon

Best answer by lauraj46 6 June 2023, 23:37

View original
Kaavya

10 replies

lauraj46
Rank
Userlevel 7
Badge +7

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

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

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! :)

Kaavya
lauraj46
Rank
Userlevel 7
Badge +7

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 

 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

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

1 Attachment
Kaavya
lauraj46
Rank
Userlevel 7
Badge +7

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

1 Attachment
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

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?

Kaavya
lauraj46
Rank
Userlevel 7
Badge +7

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

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

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

 

Kaavya
K
Rank
Userlevel 4
Badge

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

Kaavya
lauraj46
Rank
Userlevel 7
Badge +7

My pleasure @kanupindi !

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings
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