Solved

How to count multi-select attribute values individually?

  • 11 January 2022
  • 2 replies
  • 153 views

Userlevel 2
Badge

Does anyone know how to accomplish this?

A pivot table counts the values as stored in the database (multiple values concatenated, separated by commas), I want to count them individually.

In this example, the counts should be as follows:

Value 1 = 3

Value 2 = 2

Value 3 = 3

 

icon

Best answer by lauraj46 8 February 2022, 04:20

View original

2 replies

Userlevel 7
Badge +7

Hi @JohnBeiler61 ,

I think you could make this work by doing a cross join between CSAnswers and CSAttributeDetail.  

 

Replace ‘TEST’ with the id of your attribute. 

Because of the cross join, each of the possible choices (from CSAttributeDetail) will be compared with each of the answers (from CSAnswers).  The InStr function returns the position where the text is found, or 0 if not found.

 

 The hidden parameter makes it easier to build the formula into the Condition tab.

 

This will filter to return just the records that match.

This is what the results of the Generic Inquiry look like:

Then you can build the pivot something like this:

 

End result:

 

Hope this helps!

Laura

Userlevel 2
Badge

I discovered I incorrectly stated the expected count for Value 3, it should be 2 not 3.

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